MARIA, An extended MyISAM engine for MySQL

Let us talk about Maria.. no no not Maria Sharapova, its new version of MyISAM engine for MySQL which is claimed to be crash safe and provides more concurrency. It aims at becoming fully transactional (ACID). Following are the features of Maria.

Features

• ACID (Atomicity, Consistency, Isolation, Durability)
• Commit/Rollback
• Concurrent selects (via MVCC)
• Row locking
• Group commit

Although Maria itselft is released, but not part of MySQL’s binaries yet. There are some really good faqs on Monty’s blog (Developer of Maria), although the content is huge, I have taken few useful FAQ about Maria and copied here. Take a look.

Frequently Asked Question (FAQ) about Maria.

1. How does Maria 1.0 Compare to MyISAM ? Maria 1.0 is basically a crash-safe non transactional version of MyISAM. Maria 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, maria_check etc. After a normal shutdown one can copy Maria files between servers.

Advantages of Maria (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.
• Maria can replay almost everything from the log. (Including create/drop/rename/truncate tables). Therefore, you make a backup of Maria 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)
• When using PAGE format (default) row data is cached by page cache.
• Maria 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=Maria
• 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 Maria and MyISAM:

• Maria uses BIG (1G by default) log files.
• Maria has a log control file (maria_log_control) and log files ( maria_log.???????). The log files can be automatically purged when not needed or purged on demand (after backup).
• Maria uses 8K pages by default (MyISAM uses 1K). This makes Maria 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 Maria (compared to MyISAM), that will be fixed soon

• Maria 1.0 has one writer or many readers. (MyISAM can have one inserter and many readers when using concurrent inserts).
• Maria doesn’t support INSERT DELAYED.
• Maria does not support multiple key caches.

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

• Storage of very small rows.
• MERGE tables don’t support Maria (should be very easy to add later).

Differences that are not likely to be fixed.

• Maria 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 is not much used)
• Maria has one page size for both index and data (defined when Maria is used the first time). MyISAM supports different page sizes per index.
• Index number requires one extra byte per index page.
• Maria 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)

2. What is the differences between the MySQL-5.1-Maria release and the normal MySQL-5.1 release ?

• Maria is compiled in by default and required to be ‘in use’ when mysqld is started.
• Internal on-disk tables are in Maria table format instead of MyISAM table format. This should speed up some GROUP BY and DISTINCT queries because Maria has better caching than MyISAM.

New options to CREATE TABLE:

• TRANSACTIONAL= 0 | 1 ; Transactional means crash-safe for Maria <>
• PAGE_CHECKSUM= 0 | 1 ; If index and data should use page checksums for extra safety.
• TABLE_CHECKSUM= 0 | 1 ; Same as CHECKSUM in MySQL 5.1
• ROW_FORMAT=PAGE ; The new cacheable row format for Maria tables. Default row format for Maria tables and only row format that can be used if TRANSACTIONAL=1. To emulate MyISAM, use ROW_FORMAT=FIXED or ROW_FORMAT=DYNAMIC
• CHECKSUM TABLE now ignores values in NULL fields. This makes CHECKSUM TABLE faster and fixes some cases where same table definition could give different checksum values depending on row format. The disadvantage is that the value is now different compared to other MySQL installations. The new checksum calculation is fixed for all table engines that uses the default way to calculate and MyISAM which does the calculation internally. Note: Old MyISAM tables with internal checksum will return the same checksum as before. To fix them to calculate according to new rules you have to do an ALTER TABLE. You can use the old ways to calculate checksums by using the option –old to mysqld or set the system variable ‘@@old’ to 1 when you do CHECKSUM TABLE … EXTENDED;
• At startup Maria will check the Maria logs and automatically recover the tables from last checkpoint if mysqld was not taken down correctly.
• There are some improvements to DBUG code to make its execution faster when debug is compiled in but not used.

3. Why is the engine called Maria ? A funny answer!! Monty, the creator of MySQL, named MySQL after his first child ‘My’. His second child, Max, gave his name to MaxDB and the MySQL-Max distributions. His third and youngest child is named Maria…!!! 4. What is goal of Maria in current (MySQL 5.1-Maria) release ? To make a crash-safe alternative to MyISAM. That is, when mysqld restarts after a crash occurs, Maria will recover all tables to the state as of the start of a statement or at the start of a previous LOCK TABLES. 5. What is ultimate goal of Maria?

• To create a new, ACID and multi-version concurrency Control (MVCC), transactional storage engine that can function as the default non-transactional an the default transactional storage engine for MySQL.
• To be a MyISAM replacement. This is possible because Maria can also be run in non-transactional mode, supports all row formats as MyISAM, and supports or will support all major features of MyISAM.
• Maria to be standard part of MySQL 6.X

6. Where can I find documentation and help about Maria ? Documentation about Maria can be found at: http://forge.mysql.com/wiki/Maria_Docs 7. Why do you use the TRANSACTIONAL keyword now when Maria is not yet transactional ? In the current development phase Maria tables created with TRANSACTIONAL=1 are crashsafe and atomic but not transactional because changes in Maria tables can’t be rolled back with the ROLLBACK command. As we will make Maria tables fully transactional in a relatively short time frame we think it’s better to use the TRANSACTIONAL keyword already now so that applications don’t need to be changed later. Tables marked with TRANSACTIONAL=1 will for each Maria release get more and more transactional aspects and when we reach Maria 2.0 they will be fully transactional in the traditional sense. 8. How can I create a MyISAM like (non-transactional) table in Maria ? 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;
Code language: SQL (Structured Query Language) (sql)

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 t1;
Code language: SQL (Structured Query Language) (sql)

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.