2008-01-27

The Maria engine is released

Hi!

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

Enjoy!
Monty

--------------

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?
A:

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 ? 
A:

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?
A:

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?
A:

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?
A:

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?
A:

  • 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?
A:

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?
A:

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.

Managers
Patrik Backman - Director of Software Engineering at MySQL AB

---------

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

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?
A:

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?
A:
  • 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?
A:

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?
A:

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.

------------

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

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 t1;

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:

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.

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 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 ?
A:

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:
A:

22 comments:

Baron said...

What license is Maria released under?

(I don't have BK and don't know how else to get the source files and check for myself!)

Mark Callaghan said...

Thanks for providing the details. This is a big help for me to evaluate whether I can use Maria when it is in a production release. Supporting one writer and many readers is all you need to use this on a slave.

pabloj said...

My questions:

How does Maria play with InnoDB, Falcon and MyISAM?

Is there a coordinated strategy about storage engines (at least for the ones by MySQL AB)?

Could you please shed some light?
Thanks and best regards

Anonymous said...

Can we move away from the "VAR = 0|1" ? Not all DBAs are programmers and even those that are may have different expectations about 0 and 1. For example, Perl programmers treat 0 as false and 1 as true. C programmers may treat 0 as true and 1 as false (same for shell script.) And then there are those DBAs who have never written a line of code other than SQL.

Unknown said...

How about concentrate on eliminating support for the MERGE table misfit and add real support for views, maybe materialized views...?
And give us your word that you're gonna quit supporting that "genius" engine called myisam and implement all the MySQL meta data dictionaries on this Maria engine. I'm soooooo sick of myisam.

kryton said...

are you planning on having another child, or is this the last mysql related project you will be doing?

seriously... I look forward to taking see the engine being put to hard work on our sites.

LozK said...

The other Maria's cute! :)

And that's some big-ass house you have in the background!

Fitz said...

Very exciting... unfortunately with my current employer i have been stuck in T-SQL for the last 2 years so my OpenSource passion has been quelled. I look forward to getting back in the MySQL mix.

Unknown said...

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

Monty, this is fantastic news. I knew that you and your excellent team would produce something great, and now it is out!

LS

Anonymous said...

What's the relation between Maria and Falcon?

Unknown said...

Monty, good to see your blog..

Thanks
Venu

Monty said...

Hi!

Will here try to answer all comments so far:
(Sorry for the late answers, but I got a bit confused by where you could access the comments in the blogspot software and didn't notice these until now).

- Maria is of course licensed under GPL. You can now get the source and binaries http://forge.mysql.com/wiki/Maria_Preview

- The goal of the Maria development team is to make Maria to be the default transactional engine for MySQL by MySQL AB. The Falcon team has the same goal with Falcon engine. Let the best engine win :)

Seriously, both engines will have their own place and 'sweet spots' where they outperform each other and InnoDB. When both engines are stable we will start doing serious benchmarks to find out exactly where the sweet spots are.

- Regarding VAR=0|1, we will probably in the near future also allow the syntax VAR=TRUE | FALSE for cases like this.

- Faster View and materialised views are handled above the storage interface and thus not related to Maria. MERGE tables on the other hand has are very useful in environments where you want to easily manage many different collection of tables and is easiest handled by it's own engine.

- I don't plan to have more children. (And please don't hold it against me that I had said this once before :)

Unknown said...

Instead of reinventing the wheel, why not help projects like PrimeBase XT ?

Or if the wheel has to be reinvented, why not work on something MySQL lacks, like a columnar engine?

C.J. Adams-Collier said...

Nifty. I hear good things about this SE. I'll try playing around with it.

qu1j0t3 said...

Correcting Stoner... 0/1 (in tests, 0/non-zero) are indeed false/true in C, predating Perl. The concept of 0/1 as false/true should already be very familiar to MySQL users, as those are the values of MySQL's TRUE/FALSE and logical expressions. PHP and other scripting languages often interpret empty/non-empty as false/true, as does bash - but the latter's stricter interpretation of 'empty' means that all of '0', ' ' and '1' are all regarded as true - not the reversal of sense you claim, but arguably counterintuitive.

Fathers Day Gift Baskets said...

Ah Maria. What an amazing story. Things could have been a lot worse though. Geez, I wonder what people are going to do in the future? Probably hire more Los Angeles Car accident lawyers. Thanks for taking the time to post this, I think we all needed to read it.

DaveB said...

Hi.
I presume that Maria will support all the index types that MyISAM currently supports - in particular FULLTEXT?
If so, then FT together with row-level locking will be fantastic!
Keep it coming Monty.

Unknown said...

Can someone share when Maria with Rowlevel Locking will be ready for test? What version is included with Mysql 6.0, is that Maria 1.5 or Maria 2.0?

Im very interested in testing row level locking with fulltext indexes. We have a huge problem with updates causing Select statements to bank up and eventually the database gets locked up. Id like to see if this is fixed.

Unknown said...

Does the mysql 6.0 download include Maria 1.5 or 2.0?

I'd like to test fulltext indexes and row level locking. We have a production database that requires fulltext indexes - and we get lockups when the system is busy and we update the main table.

Does anyone know when we may be able to test out Maria 2.0?

Monty said...

About which Maria release MysQL 6.0 contains and row level locking.

MySQL 6.0 did include Maria 1.5, but as MySQL 6.0 is not going to be released, this isn't that relevant.

MariaDB 5.1, is based on MySQL 5.1 and includes the Maria storage engine version 1.5. MariaDB 5.1 has higher concurrency than MyISAM for inserts but still holds table level locks for updates and deletes. This is scheduled to be fixed in MariaDB 5.4 which we are working on now. Our primary focus just now is on MariaDB, but we aim to have a better Maria storage engine out this year.

You can follow our progress at askmonty.org.

Los Angeles Personal Injury Lawyer said...

Thanks for providing such a descriptive articles, I was wondering on Maria Engine.. I never thought that its named after the Monty's Child.. What would be next release coming up on MYSQL?

Jorge Aguero said...

Undoubtedly, Maria is a great storage engine. I would like to appreciate the whole team behind this development and efforts. This is another fantastic news in the field of computing. Consumers can achieve something beneficial out of this great development.
Brandon Personal Injury Lawyer