This FAQ explains what to expect of the Aria storage engine (below referred to only as 'Aria').
Note: The Aria storage engine was previously called Maria (see The Aria Name for details on the rename) and in current versions of MariaDB you can refer to the engine as Maria or Aria. This will be changed in a future version of MariaDB so please change references in scripts and automation to Aria!
- What is Aria?
- Why is the engine called Aria?
- What is the current goal of Aria 1.5 (current version)?
- What is the goal of Aria 2.0 (next version)
- What is the ultimate goal of Aria?
- What are the 'design goals' in Aria?
- Where can I find documentation and help about Aria?
- Who is behind Aria?
- What is the release policy/schedule of Aria?
- How does Aria 1.5 Compare to MyISAM?
- Advantages of Aria (Compared to MyISAM)
- Differences between Aria and MyISAM
- Disadvantages of Aria (compared to MyISAM), that will be fixed soon
- Disadvantages of Aria (compared to MyISAM), that will be fixed in later releases
- Differences that are not likely to be fixed
- What are the differences between MariaDB 5.1 release and the normal MySQL-5.1 release?
- Why do you use the TRANSACTIONAL keyword now when Aria is not yet transactional?
- What are the currently known problems with the MySQL-5.1-Maria release?
- What things are going to change in later Aria main releases?
- How can I create a MyISAM like (non-transactional) table in Aria?
- What are the advantages/disadvantages of the new PAGE format compared to the old MyISAM like row formats (DYNAMIC and FIXED) ==
- What's the proper way to copy a Aria table from one place to another?
- When is it safe to remove old log files?
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.
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 Aria Name 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 the developers are focusing their main efforts on improving MariaDB. However they are interested in working with customers/partners to add more features to Aria and eventually 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
- Concurrent updates/deletes
- Row locking
- Group commit (Already in MariaDB 5.2)
- Faster lookup in index pages (Page directory)
In Aria 2.5 the plan is to start focusing on performance.
What is the ultimate goal of Aria?
- 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.
- To be the default non-transactional engine in MariaDB (instead of MyISAM).
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 Aria section of the Knowledge Base contains documentation about the storage engine and related topics.
The project is maintained on Launchpad.
You can usually find some of the Maria developers online on the IRC channel #maria at freenode.
Who is behind Aria?
The core team who have developed Aria are:
- 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.
All except Guilhem Bichot are working for SkySQL.
What is the release policy/schedule of Aria?
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?
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.
- 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?
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
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:
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.
- 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
aria_chk --zerofill is that you avoid a delay in the MySQL server for the first access of
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.