The Maria engine is released


We have now made the bitkeeper tree of Maria public at http://mysql.bkbits.com

Maria is a new storage engine that Guilhem, Sanja, Sergei and I have been working on for the last 2 years.

(Before anyone comments that this is not 'that much' for 2 years work, I would lke to note that the first 1 1/2 this was done part time while we have finished other projects; It's only during the last 4 months we have been working full time on this. From now on we Maria is our main focus.)

We are still discussing in MySQL AB how and when we will start to make binary distributions of Maria; Until then, please use the bk tree to test the Maria engine! We are now in a state of Maria 1.0 with 'no known bugs' (which of course doesn't mean that there isn't any bugs just that we belive we are in 'reasonable good shape') and we need the help from other developers to find the hidden bugs so that we can quickly fix them and make Maria stable!

Here follows an FAQ.txt that I have written and to which the current Maria developers have committed. Note that this is NOT a commitment from MySQL AB but a personal commitment from me and the other developers working on the Maria project.

If you have questions, comments bugs or patches, please send them to the soon-to-be made Maria mailing list and the Maria forum).



This FAQ explains what to expect of the Maria storage engine
(below referred to only as 'Maria') and of the mysql -5.1-maria release.

NOTE: The opinions and promises stated in this FAQ is by the Maria development team and not promises by MySQL AB.

Overview of questions:

Q: Why is the engine called Maria ?
Q: What is goal of Maria in current (MySQL 5.1) release ?
Q: How do you plan to reach this goal ?
Q: What will be in the next major release of Maria ?
Q: What is ultimate goal of Maria ?
Q: What are the 'design goals' in Maria ?
Q: Where can I find documentation and help about Maria ?
Q: Who is behind Maria ?
Q: What is the release policy/schedule of Maria ?
Q: How does Maria 1.0 Compare to MyISAM ?
Q: What is the differences between the MySQL-5.1-Maria release and the normal MySQL-5.1 release ?
Q: Why do you use the TRANSACTIONAL keyword now when Maria is not yet transactional ?
Q: What are the currently known problems with the MySQL-5.1-Maria release ?
Q: What things are going to change in later Maria main releases ?

Usage questions:
Q: How can I create a MyISAM like (non-transactional) table in Maria ?
Q: Whats the proper way to copy a Maria table from on place to another ?
Q: When is it safe to remove old log files ?
Q: Why is the engine called Maria?

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...


Q: 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.

Another primary goal for this release is make Maria of good quality; performance is not a primary goal for this release! We will start to work on performance when we know we have a good basis for Maria!

The goal is more clearly defined in WorkLog at http://forge.mysql.com/worklog/task.php?id=3871

We are now at Maria 1.0 in the worklog schedule and we will take it to Maria 1.5 during the alpha period of Maria-5.1-Maria-alpha.

For things that still need to be done for Maria 1.5, the first Maria main release, check section "Missing features that is planned to fix before Beta" in the KNOWN_BUGS.txt file


Q: How do you plan to reach this goal?

Continuously improve the MySQL-5.1-Maria release until we reach the goal above. 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).

The Maria development tree will be open and we will do monthly source and binary releases until the Maria engine reaches a stable state. After first release, for at least one year, we will
provide bug fixes and source and binary releases of the mysql-5.1-maria tree. (See also Q: What is the release policy/schedule of Maria)

When Maria 5-1-Maria gets to be declared Beta, we will start implementing the new features for Maria 2.0 into a MySQL-6.X-Maria-alpha branch.


Q: What will be in the next major release of Maria?

This is defined in http://forge.mysql.com/worklog/ for Maria 2.0. Here follows a short description:
  • ACID
  • Commit/Rollback
  • Concurrent selects (thanks to MVCC)
  • Row locking
  • Group commit

Q: What is ultimate goal of Maria?

NOTE: The following goals are the *technical goals* of the project team. How Maria in the end will be incorporated in the MySQL Server product and whether it will replace MyISAM 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 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


Q: What are the 'design goals' in Maria?

  • Multi-version concurrency Control (MVCC) and ACID storage engine
  • Optionally not transactional tables that should be 'as fast and as compact' as MyISAM tables.
  • Be able to use Maria for internal temporary tables in MySQL (instead of MyISAM)
  • All indexes should have equal speed (clustered index is not on current road map).
  • Allow 'any' length transactions to work (Having long running transactions will cause more logs space to be used)
  • Allow log shipping; Ie, you can do incremental backups of Maria tables just by copying the maria logs.
  • Allow copying of Maria tables between different Maria servers (under some well-defined constraints)
  • Better blob handling (than that in MyISAM at least):
  • 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 Maria 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 MySQL + Maria suitable for desktop and embedded applications.
  • Flexible memory allocation and scalable algorithms to use huge memory efficient, if available.

Q: Where can I find documentation and help about Maria?

Documentation about Maria can be found at: http://forge.mysql.com/wiki/Maria_Docs

You can find also use the Maria email list at: maria@lists.mysql.com or at the Maria forum at: http://forums.mysql.com, section Maria

You can report bugs and check bugs in Maria in the MySQL bugs system at http://bugs.mysql.com/


Q: Who is behind Maria?

The current MySQL AB Maria team 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.
Oleksandr Byelkin - Query cache, sub-queries, views
Sergei Golubchik - Server Architect, Full text search,
keys for MyISAM-Merge, Plugin architecture, etc.

Other developers
Jani Tolonen - Long term all employee of MySQL AB; Worked with
Monty since 1997.

Patrik Backman - Director of Software Engineering at MySQL AB


Q: What is the release policy/schedule of Maria?

The Maria team can only promise to cover bugs in Maria. For bugs in MySQL, we can't give any guarantees. However, bugs in MySQL that directly affect Maria or are related to Maria are likely to be fixed or worked around by the Maria team.

The Maria development release policy follows the MySQL release criteria at http://dev.mysql.com/doc/refman/5.1/en/choosing-version.html. In addition to this we have the following project commitments for the maturity stages:

The release policy for all Maria storage engine releases:
  • All Maria releases should be free from bugs that can cause problem in 'normal' operation for most users.
  • We aim for one release per month until Maria is stable. This will include a source release and binary release for all machines in our build farm for which Maria passes all tests.
  • When Maria becomes stable we will do few releases monthly, but later only when critical bugs have been found and fixed.
  • The MySQL-Maria release will include all changes from the main MySQL tree up to at least the latest stable build of MySQL.
  • If we make a release with known critical bugs (for example, if there is a really nasty bug we want to fix at once and get the fix out, while we are fixing other bugs), they will be documented in the KNOWN_BUGS.txt file.
  • Maria 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 maria_log.######## files before restarting mysqld.
Commitment of Beta
  • Maria is feature complete according to specification for Maria 1.5 in worklog at http://forge.mysql.com/worklog/task.php?id=4158
  • No known bugs, except for those marked as feature requests
  • No bugs fixed since last release that caused a notable code changes.
Commitment of RC/Gamma:
  • No known bugs, except for those marked as feature requests
  • No bugs fixed since last release that caused a notable code changes.
  • We belive Maria code is ready for general usage (based on bug inflow), but we want more testing before calling it stable
Commitment of GA/stable (and all releases after this)
  • No known bugs, except for those marked as feature requests
  • No bugs fixed since last release that caused a notable code changes.
  • We belive Maria code is ready for general usage (based on bug inflow).

In the rare case when there is a bug that can't be fixed in a specific release (either because it's a design bug or the bug fix is likely to cause other, possible worse bugs), we will document it in the KNOWN_BUGS.txt section. However, we will try to keep these kinds of open bugs at a minimum.


Q: 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)

Q: What are 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.
Q: 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.


Q: What are the currently known problems with the MySQL-5.1-Maria release?
A: None (just kidding, this is software we are talking about...)
  • See KNOWN_BUGS.txt for open/design bugs
  • See http://bugs.mysql.com/ for newly reported bugs. Please report anything you can't find here!
  • If there is a bug in the Maria recovery code or in the code that generates the logs, or if the logs become corrupted, then mysqld may fail to start because Maria can't execute the logs at start up.
FIX: Remove the maria_log.???????? files from the data directory, restart mysqld and run CHECK TABLE / REPAIR TABLE or mysqlcheck on your Maria tables.

Alternative remove logs and run maria_chk on your *.MAI files


Q: What things are going to change in later Maria 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:


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


Q: How can I create a MyISAM like (non-transactional) table in Maria?



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:


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.


Q: Whats the proper way to copy a Maria table from on place to another?

A Maria table consists of 3 files:
XXX.frm ; The definition for the table, used by MySQL
XXX.MYI ; Maria 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 maria 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 Maria 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 Maria recovery log (that is, Maria may need to access the old data during recovery). If you are unsure whether the old name existed, run 'maria_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:

'maria_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'.

Maria will automatically notice if have copied a table from another system and do 'zerofill' for the first access of the table. The reason for using maria_chk 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!


Q: When is it safe to remove old log files ?

If you want  to remove the Maria log files 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.


Q: How does the other Maria look like:

Sun buys MySQL AB


Recent events have convinced me that it's finally time for me to start blogging.

The first thing I would like to talk about is about Sun buying MySQL AB. In a second blog entry I will then talk about the Maria storage engine, a project that I have been working on for the past two year (first half time and now full time).

Personally I think that the deal with Sun is the best thing that could have happened to us. When David, Allan and I originally took on investors on MySQL we did know that there was only two ways out; Either we would make an IPO or we would be bought by some large company.

I was always a bit afraid of the IPO route, as in this case there was no guarantees long term that MySQL AB would be a company that would focus on open source. I am quite confident that With Sun, who has made strong commitments to open source with things like Open Solaris, Open Office and making Java open source, our open source inheritance will be taken good care of.

I think Sun is the best possible buyer, because of the following reasons:
(Note that this is of course my interpretation)
  • Sun is committed to open source.
  • Sun doesn't have an database of their own; In other words, no risk of internal conflicts between similar products.
  • Sun understands what it means to be a virtual company where people work from home.
  • Sun has a good understanding of developers needs and there is a good chance that the integration of the two companies will be relative smooth.
  • Sun has said they will let the MySQL developers continue work as before in their own unit and without big changes (except of course changes for the better!).
Another reason I believe we are good for each other is the different history:

Sun is a hardware company who has been for a long time in a transition to also be a software company. In their software space they where first closed source but has lately started to change most of their software to open source/free software.

MySQL AB on the other hand is a company that was originally totally committed to free software / open source but who has lately changed to be more closed.

This deal will allow both companies to learn from each others successes and failures and build a stronger company than we would have been able to do separately.

A match truly made in heaven (sorry Sun :). Lets hope that we here on Earth will be able to make the best of it!