Aria FAQ

This FAQ explains what to expect of the Aria storage engine (below referred to only as 'Aria'). MariaDB, the MySQL release that includes Aria, is explained on its own page.

What is Aria?

Aria is a new storage engine for MySQL® and MariaDB which is developed with the goal of being the default transactional AND non-transactional storage engine for MariaDB and MySQL.

It has been in development since 2007 and was first announced on Monty's blog.

It's being developed by the same core MySQL engineers who developed the MySQL server and the MyISAM, MERGE, and MEMORY storage engines.

Why is the engine called Aria?

The engine was originally called Maria after Monty's younger daughter. Monty named MySQL after his first child My. His second child, Max, gave his name to MaxDB and the MySQL-Max distributions.

Having both MariaDB the database server and Maria the storage engine proved confusing, so the decision was made to change the name. A Rename Maria contest was held during the first half of 2010 and names were submitted from around the world. Monty picked the name Aria from a short list of finalists. The person who suggested the name, Chris Tooley, received a Linux-powered System 76 Meerkat NetTop from Monty Program. More information on the contest can be found on the Rename Maria page.

What is the current goal of Aria 1.5 (current version)?

To make a crash-safe alternative to MyISAM. That is, when mysqld restarts after a crash occurs, Aria will recover all tables to the state as of the start of a statement or at the start of a previous LOCK TABLES.

The current goal is to keep the code stable and fix all found bugs.

What is the goal of Aria 2.0 (next version)

A fully transactional storage engine with at least all of InnoDB's major features.

for the moment Aria 2.0 is on hold as we in Monty program Ab are focusing our main efforts on improving MariaDB. However we are interested in working with customers/partners to add more features to Aria and over time finish Aria 2.0.

The goal is more clearly defined in the WorkLog for Aria 2.0. Click on 'Dependent Tasks' to see the details.

Here is a short description:

• ACID compliant
• Commit/Rollback
• Concurrent updates/deletes
• Row locking
• Group commit (Already in MariaDB 5.2)
• Faster lookup in index pages (Page directory)

In Aria 2.5 we will start focusing on performance.

How do you plan to reach these goals?

Continuously improve MariaDB and release until we have a proper stable release.

The improvements between releases should be made in such a way that upgrades are trivial (that is, no requirement to dump and reload data and no application changes).

We will work closely with the community to improve both the Aria engine and the MySQL server that is distributed as part of MariaDB. We will also work with Sun to get the Aria storage engine into MySQL 6.0.

The MariaDB development trees at launchpad are open for download and community development.

What is the ultimate goal of Aria?

NOTE: The following goals are the technical goals of the project team. How Aria will ultimately be incorporated in the MySQL® Server product and whether it will replace MyISAM there is subject to decision by MySQL Product Management.

• To create a new, ACID and Multi-Version Concurrency Control (MVCC), transactional storage engine that can function as both the default non-transactional and transactional storage engine for MariaDB and MySQL®.
• To be a MyISAM replacement. This is possible because Aria can also be run in non-transactional mode, supports the same row formats as MyISAM, and supports or will support all major features of MyISAM.
• Aria to be standard part of MySQL 6.0; Aria is already in the MySQL 6.0 tree supported by Sun (now Oracle).

What are the 'design goals' in Aria?

• Multi-Version Concurrency Control (MVCC) and ACID storage engine.
• Optionally non-transactional tables that should be 'as fast and as compact' as MyISAM tables.
• Be able to use Aria for internal temporary tables in MySQL (instead of MyISAM).
• All indexes should have equal speed (clustered index is not on our current road map for Aria. If you need clustered index, you should use XtraDB).
• Allow 'any' length transactions to work (Having long running transactions will cause more log space to be used).
• Allow log shipping; Ie, you can do incremental backups of Aria tables just by copying the Aria logs.
• Allow copying of Aria tables between different Aria servers (under some well-defined constraints).
• Better blob handling (than is currently offered in MyISAM, at a minimum).
• No memory copying or extra memory used for blobs on insert/update.
• Blobs allocated in big sequential blocks - Less fragmentation over time.
• Blobs are stored so that Aria can easily be extended to have access to any part of a blob with a single fetch in the future.
• Efficient storage on disk (that is, low row data overhead, low page data overhead and little lost space on pages). Note: There is still some more work to succeed with this goal. The disk layout is fine, but we need more in-memory caches to ensure that we get a higher fill factor on the pages.
• Small footprint, to make MariaDB + Aria suitable for desktop and embedded applications.
• Flexible memory allocation and scalable algorithms to utilize large amounts of memory efficiently, when it is available.

Where can I find documentation and help about Aria?

The project is maintained on Launchpad.

If you want to know what happens or be part of developing Aria, you can subscribe to the maria-captains, maria-developers, maria-docs, or maria-discuss groups on Launchpad.

You can report and check bugs in Aria at Launchpad or in the MySQL bugs system.

You can usually find some of the Maria developers online on the IRC channel #maria at freenode.

Who is behind Aria?

The current core team developing Aria is:

Technical lead:

• Michael "Monty" Widenius - Creator of MySQL and MyISAM

Core developers (in alphabetical order)

• Guilhem Bichot - Replication expert, on line backup for MyISAM, etc.
• Kristian Nielsen - MySQL build tools, NDB, MySQL server
• Oleksandr Byelkin - Query cache, sub-queries, views.
• Sergei Golubchik - Server Architect, Full text search, keys for MyISAM-Merge, Plugin architecture, etc.

Some people are working for Monty Program Ab, others are working for Oracle.

What is the release policy/schedule of Aria?

Aria follows the same release criteria as for MariaDB.

Some clarifications, unique for the Aria storage engine:

• Aria index and data file formats should be backward and forward compatible to make it easy to upgrade/downgrade.
• We will try to keep the log file format compatible, but for this we don't want make any guarantees yet. In other words, in some cases when upgrading, you must remove the old aria_log.## and maria_log.## files before restarting mysqld. So far this is only the case between MariaDB 5.1 and 5.2

Extended commitment for Beta 1.5

• Aria is feature complete according to specification for Aria 1.5 in worklog.

How does Aria 1.5 Compare to MyISAM?

Aria 1.0 was basically a crash-safe non-transactional version of MyISAM. Aria 1.5 added more concurrency (multiple inserter) and some optimizations.

Aria supports all aspects of MyISAM, except as noted below. This includes external and internal check/repair/compressing of rows, different row formats, different index compress formats, aria_chk etc. After a normal shutdown one can copy Aria files between servers.

Advantages of Aria (Compared to MyISAM)

• Data and indexes are crash safe.
• On a crash, changes will be rolled back to state of the start of a statement or a last LOCK TABLES commands.
• Aria can replay almost everything from the log. (Including create/drop/rename/truncate tables). Therefore, you make a backup of Aria by just copying the log. The things that can't be replayed (yet) are:
  • Batch INSERT into an empty table (This includes LOAD DATA INFILE, SELECT ... INSERT and INSERT (many rows)).
  • ALTER TABLE. Note that .frm tables are NOT recreated!
• LOAD INDEX can skip index blocks for unwanted indexes.
• Supports all MyISAM row formats + new PAGE format where data is stored in pages. (default size is 8K).
• Multiple concurrent inserters into the same table.
• When using PAGE format (default) row data is cached by page cache.
• Aria has unit tests of most parts.
• Supports both crash-safe (soon to be transactional) and not transactional tables. (Non-transactional tables are not logged and rows uses less space): CREATE TABLE foo (...) TRANSACTIONAL=0|1 ENGINE=Aria.
• PAGE is the only crash-safe/transactional row format.
• PAGE format should give a notable speed improvement on systems which have bad data caching. (For example Windows).

Differences between Aria and MyISAM

• Aria uses BIG (1G by default) log files.
• Aria has a log control file (aria_log_control) and log files ( aria_log.???????). The log files can be automatically purged when not needed or purged on demand (after backup).
• Aria uses 8K pages by default (MyISAM uses 1K). This makes Aria a bit faster when using keys of fixed size, but slower when using variable-length packed keys (until we add a directory to index pages).

Disadvantages of Aria (compared to MyISAM), that will be fixed soon

• Aria doesn't support INSERT DELAYED.
• Aria does not support multiple key caches.

Disadvantages of Aria (compared to MyISAM), that will be fixed in later releases

• Storage of very small rows (< 25 bytes) are not efficient for PAGE format.
• MERGE tables don't support Aria (should be very easy to add later).

Differences that are not likely to be fixed

• Aria data pages in block format have an overhead of 10 bytes/page and 5 bytes/row. Transaction and multiple concurrent-writer support will use an extra overhead of 7 bytes for new rows, 14 bytes for deleted rows and 0 bytes for old compacted rows.
• No external locking (MyISAM has external locking, but this is a rarely used feature).
• Aria has one page size for both index and data (defined when Aria is used the first time). MyISAM supports different page sizes per index.
• Small overhead (15 bytes) per index page.
• Aria doesn't support MySQL internal RAID (disabled in MyISAM too, it's a deprecated feature).
• Minimum data file size for PAGE format is 16K (with 8K pages).

What are the differences between MariaDB 5.1 release and the normal MySQL-5.1 release?

See:

• Aria storage engine
• MariaDB versus MySQL

Why do you use the TRANSACTIONAL keyword now when Aria is not yet transactional?

In the current development phase Aria tables created with TRANSACTIONAL=1 are crashsafe and atomic but not transactional because changes in Aria tables can't be rolled back with the ROLLBACK command. As we will make Aria tables fully transactional in a relatively short time frame we think it's better to use the TRANSACTIONAL keyword now so that applications don't need to be changed later.

Tables marked with TRANSACTIONAL=1 will gain more transactional features with each Aria release. We expect these tables to be fully transactional (in the traditional sense) when we reach Aria 2.0.

What are the currently known problems with the MySQL-5.1-Maria release?

• See KNOWN_BUGS.txt for open/design bugs.
• See http://bugs.launchpad.net/maria or http://bugs.mysql.com/ for newly reported bugs. Please report anything you can't find here!
• If there is a bug in the Aria recovery code or in the code that generates the logs, or if the logs become corrupted, then mysqld may fail to start because Aria can't execute the logs at start up.

If Aria doesn't start or you have an unrecoverable table (shouldn't happen):

• Remove the aria_log.???????? files from the data directory.
• Restart mysqld and run CHECK TABLE / REPAIR TABLE or mysqlcheck on your Aria tables.

or

• Remove logs and run aria_chk on your *.MAI files.

What things are going to change in later Aria main releases?

LOCK TABLES will not start a crash-safe segment. You should use BEGIN/COMMIT instead. To make things future safe, you could do this:

BEGIN;
LOCK TABLES ....
UNLOCK TABLES;
COMMIT;

And later you can just remove the LOCK/UNLOCK part.

How can I create a MyISAM like (non-transactional) table in Aria?

Example:

CREATE TABLE t1 (a int) ROW_FORMAT=FIXED TRANSACTIONAL=0 PAGE_CHECKSUM=0;
CREATE TABLE t2 (a int) ROW_FORMAT=DYNAMIC TRANSACTIONAL=0 PAGE_CHECKSUM=0;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;

Note that the rows are not cached in the page cache for FIXED or DYNAMIC format. If you want to have the data cached (something MyISAM doesn't support) you should use ROW_FORMAT=PAGE:

CREATE TABLE t3 (a int) ROW_FORMAT=PAGE TRANSACTIONAL=0 PAGE_CHECKSUM=0;
SHOW CREATE TABLE t3;

You can use PAGE_CHECKSUM=1 also for non-transactional tables; This puts a page checksums on all index pages. It also puts a checksum on data pages if you use ROW_FORMAT=PAGE.

You may still have a speed difference (may be slightly positive or negative) between MyISAM and Aria because of different page sizes. You can change the page size for MariaDB with --aria-block-size=#, where # is one of 1024, 2048, 4096, 8192, 16384 or 32768.
Note that if you change the page size you have to dump all your old tables into text (with mysqldump) and remove the old Aria log and files:

rm datadir/aria_log*

What are the advantages/disadvantages of the new PAGE format compared to the old MyISAM like row formats (DYNAMIC and FIXED)

The MyISAM like DYNAMIC and FIXED format are extremely simple and has very little space overhead, so it's hard to beat them for when it comes to simple scanning of not modified data. The DYNAMIC format does however get notable worse over time if you update the row a lot in a manner that increases the size of the row over time.

The advantages of the PAGE format (compared to DYNAMIC or FIXED) for non transactional tables are:

• It's cached by the page cache, which gives better random performance (as it uses less system calls).
• Does not fragment as easily easily as the DYNAMIC format during UPDATES; Maximum number of fragments are very low.
• Code can easily be extended to only read the accessed columns (for example to skip reading blobs).
• Faster updates (compared to DYNAMIC).

The disadvantages are:

• Slight storage overhead (should only be notable for very small row sizes)
• Slower full table scan time.
• When using row_format=PAGE (default) aria first write the row, then the keys at which point the check for duplicate keys happens. This makes PAGE format slower than DYNAMIC (or MyISAM) if there is a lot of duplicated keys because of the overhead of writing + removing the row. If this is a problem, you can use row_format=DYNAMIC to get same behavior as MyISAM.

What's the proper way to copy a Aria table from one place to another?

An Aria table consists of 3 files:

XXX.frm ; The definition for the table, used by MySQL.
XXX.MYI ; Aria internal information about the structure of the data
and index and data for all indexes.
XXX.MAD ; The data.

It's safe to copy all the Aria files to another directory or MySQL instance if any of the following holds:

• If you shutdown mysqld properly with 'mysqladmin shutdown', so that there is nothing for Aria to recover when it starts.

or

• You have done 'flush tables ' and not accessed the table using SQL from that time until the tables have been copied.

In addition, you must adhere the following rule for transactional tables:

You can't copy the table to a location within the same MySQL server if the new table has existed before and the new table is still active in the Aria recovery log (that is, Aria may need to access the old data during recovery). If you are unsure whether the old name existed, run 'aria_chk --zerofill' on the table before you use it.

After copying a transactional table and before you use the table, we recommend that you run the command:

aria_chk --zerofill table_name

This will overwrite all references to the logs (LSN), all transactional references (TRN) and all unused space with 0. It also marks the table as 'movable'. An additional benefit of zerofill is that the Aria files will compress better. No real data is ever removed as part of zerofill.

Aria will automatically notice if you have copied a table from another system and do 'zerofill' for the first access of the table if it was not marked as 'movable'. The reason for using <<code>>aria_chk --zerofill<<code>> is that you avoid a delay in the MySQL server for the first access of the table. Note that this automatic detection doesn't work if you copy tables within the same MySQL server!

When is it safe to remove old log files?

If you want to remove the Aria log files (aria_log.*) with 'rm' or delete, then you must first shut down MySQL cleanly (for example, with 'mysqladmin shutdown') before deleting the old files.

The same rules apply when upgrading MySQL; When upgrading, first take down MySQL in a clean way and then upgrade. This will allow you to remove the old log files if there are incompatible problems between releases.

Don't remove the aria_log_control file! This is not a log file, but a file that contains information about the Aria setup (current transaction id, unique id, next log file number etc).

If you do, Aria will generate a now aria_log_control file at startup and will regard all old Aria files as files moved from another system. This means that they have to be zerofilled before they can be used. This will happen automatically at next access of the Aria files, which can take some time if the files are big.

If this happens, you will see things like this in your mysqld.err file:

Note] Zerofilling moved table: '.\database\xxxx'

As part of zerofilling no vital data is removed.