Maria

From Askmonty.org

Contents


Purpose of this FAQ

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

What is Maria?

Maria is a new storage engine for MySQL that is developed with the goal of being the default transactional AND non-transactional storage engine for MySQL.

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

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

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

What is the goal of Maria 1.5 (current release, developed based on MySQL 5.1)?

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 to make Maria code 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 the WorkLog for Maria 1.5. Click on 'Dependent Tasks' to see the details.

Maria 1.5 is now in beta and the source tree can be accessed through Launchpad.

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

We also have a Maria 1.5 tree based on MySQL 6.0. We will update this from time to time but we will not focus development on the 6.0 branch until MySQL 6.0 is reasonably stable.

What is the goal of Maria 2.0 (current release, developed based on MySQL 5.1)?

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

The goal is more clearly defined in the WorkLog for Maria 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
  • Faster lookup in index pages (Page directory)

In Maria 2.5 we will start focusing on performance.

How do you plan to reach these goals?

Continuously improve the MariaDB (aka MySQL-5.1-Maria) and release until we have a proper stable release. We will also, until Maria 1.5 reaches gamma quality, add stable community patches and bug fixes to MariaDB.

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 Maria engine and the MySQL server that is distributed as part of MariaDB. We will also work with Sun to get the Maria storage engine into MySQL 6.0.

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

We are setting up a system to be able to do monthly binary releases. The Maria team will provide bug fixes to the Maria engine for at least one year.

What is the ultimate goal of Maria?

NOTE: The following goals are the *technical goals* of the project team. How Maria will ultimately 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 both the default non-transactional and transactional storage engine for MySQL.
  • To be a MyISAM replacement. This is possible because Maria 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.
  • Maria to be standard part of MySQL 6.0; Maria is already in the MySQL 6.0 tree supported by Sun.

What are the 'design goals' in Maria?

  • 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 Maria for internal temporary tables in MySQL (instead of MyISAM).
  • All indexes should have equal speed (clustered index is not on our current road map).
  • 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 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 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 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 utilize large amounts of memory efficiently, when it is available.

Where can I find documentation and help about Maria?

The project is maintained on Launchpad.

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

Documentation about Maria can be found at Sun's Maria manual and on this wiki page.

You can 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 and check bugs in Maria 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 Maria?

The current core team developing Maria is:

Technical lead:

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

Core developers (in alphabetical order)

  • Christoffer Hall - Works on performance.
  • 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.

Other developers

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

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

What is the release policy/schedule of Maria?

Maria follows the same release criteria as for MariaDB.

Some clarifications, unique for the Maria storage engine:

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

Extended commitment for Beta 1.5

How does Maria 1.5 Compare to MyISAM?

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

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).
  • Multiple concurrent inserters into the same table.
  • 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 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 (< 25 bytes) are not efficient for PAGE format.
  • 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 this is a rarely used feature).
  • 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.
  • Small overhead (15 bytes) 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).

What are the differences between MariaDB 5.1 (aka MySQL-5.1-Maria) release and the normal MySQL-5.1 release?

See:

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 now so that applications don't need to be changed later.

Tables marked with TRANSACTIONAL=1 will gain more transactional features with each Maria release. We expect these tables to be fully transactional (in the traditional sense) when we reach Maria 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 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.

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

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

or

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

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: 

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

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.

What's the proper way to copy a Maria table from one 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.

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 you 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!

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.

How does the other Maria look?

Maria, 4 years old
Maria, 4 years old
Retrieved from "http://askmonty.org/wiki/Maria"