General Purpose Storage Engines in MariaDB

MariaDB tries to encourage use of many storage engines, which Oracle doesn’t bother with. The ones that could be considered — occasionally! — as InnoDB replacements are: Mroonga, TokuDB, and Aria.

Mroonga

I predicted that Mroonga would be in MariaDB 10.0.8, and behold, here it is in MariaDB 10.0.15. Truly I am the Nostradamus of the database world.

It’s a general-purpose storage engine, in that it doesn’t have serious limitations regarding what can be stored or indexed. I think its “column-store” feature has limited appeal, but as I saw in January, it’s dandy for full-text searching. This time I look at the list of “Full-Text Restrictions” in the MySQL manual, and quote from it:

Full-text searches are supported for InnoDB and MyISAM tables only.” — Not true for Mroonga, of course.

Full-text searches are not supported for partitioned tables.” — True for Mroonga, which can’t handle partitioned tables at all.

… the utf8 character set can be used, but not the ucs2 [or utf16 or utf32] character set.” — True for Mroonga.

Therefore, the FULLTEXT parser cannot determine where words begin and end in [Chinese/Japanese/Korean i.e. CJK].” — Not true for Mroonga, which is just great with CJK.

… all columns in a FULLTEXT index must use the same character set and collation.” — True for Mroonga.

The argument to AGAINST() must be a string value that is constant during query evaluation.” — True for Mroonga.

For InnoDB, all DML operations (INSERT, UPDATE, DELETE) involving columns with full-text indexes are processed at transaction commit time.” — Technically not true for Mroonga, but Mroonga indexes will get out of synch if rollbacks occur.

TokuDB

In a previous escapade I found that TokuDB was good stuff but lacked two features: foreign keys and full-text search.

So, inspired by the fact that I can use Mroonga full-text searching with InnoDB, I wondered: wouldn’t it be great if I could use that trick on TokuDB too?

Well, I gave it a try, and have to say the result was a bit disappointing …

MariaDB [test]> CREATE TABLE t (
    ->   id INT NOT NULL DEFAULT '0',
    ->   `text_column` VARCHAR(100) CHARACTER SET utf8 DEFAULT NULL,
    ->   PRIMARY KEY (id),
    ->   FULLTEXT KEY `text_column` (text_column)
    -> ) ENGINE=mroonga DEFAULT CHARSET=latin1 COMMENT='engine "tokudb"'
    -> ;
ERROR 2013 (HY000): Lost connection to MySQL server during query

… Do not do this on a production system, as it will disable all your databases.

I also have a MariaDB 10.0.15 server that I built from source, and on that server TokuDB crashes even for simple everyday statements. I suppose this is like saying “Doctor my head hurts when I beat it against the wall”. But there’s no warning during the build and no warning in the documentation. Therefore it must be somebody else’s fault.

Aria

In 2013 Stewart Smith said about Aria: “It’s still not there and I don’t think it ever will be.” I didn’t see anything in 2014 that contradicts what he predicts, and the MariaDB people themselves say improvements are “on hold”, but it does have some advantages over MyISAM.

This is another storage engine with full-text support, but I rate that feature as “poor” because (like InnoDB) it won’t do CJK well, and (unlike InnoDB) it has some irritating default settings about minimum key length and maximum frequency.

It looks nice that I can create a SPATIAL index with Aria; however, I see that this is supposedly possible with InnoDB too, according to the recent announcement of MySQL 5.7.5.

Data can be represented in tables

Capability Mroonga TokuDB Aria InnoDB
Full-text Indexes Excellent No Poor Poor
Foreign Keys No No No Yes
Rollbacks No Yes No Yes
Spatial Indexes Yes No Yes Real Soon
Maximum Key Length 3072 3072 1000 767
Smallest Lock Column Row Table Row
Allows Partition Clauses No Yes Yes Yes
Recovers after ‘kill -9’ No Yes Yes Yes
Works on Windows Yes No Yes Yes

Or, if you want to go beyond general-purpose open-source storage engines like these, there are lots more to look at. The biggest list of MySQL-compatible storage engines that I know of is in the Spanish Wikipedia.

UPDATE NOTICE: The chart above has been corrected due to comments from a Mroonga developer and a TokuDB developer.

, December 6, 2014. Category: MariaDB.

About pgulutzan

Co-author of four computer books. Software Architect at MySQL/Sun/Oracle from 2003-2011, and at HP for a little while after that. Currently with Ocelot Computer Services Inc. in Edmonton Canada.

3 Comments

  1. kou says:

    Hi!
    I’m a Mroonga eveloper.

    Mroonga supports Spatial Indexes. 🙂

    Mroonga’s Smallest Lock is a column.

    FYI, Mroonga acquires a lock for writing but other threads (and/or processes) can read data while locking. (Other threads (and/or processes) can’t *write* data while locking.)

    Thanks for explaining about Mroonga!

  2. I’m a Tokutek (TokuDB) employee and a correction and a comment:
    – TokuDB supports row level locking, not “Key Range”. Point locks are acquired until the amount of memory for locking is exhausted, at which points locks get escalated. Our locking is very similar to InnoDB.
    – Your writeup of TokuDB concerns me in that it likely leaves the reader wondering if TokuDB is production ready. We have been releasing TokuDB for over 5 years now, are currently on version 7.5, offer releases on MySQL 5.5 and MariaDB 5.5 from our website, and are included in MariaDB 5.5/10.0/10.1 from mariadb.org and Percona Server 5.6 from percona.com. I don’t think your intention was to question the stability of the product, but figured it was worth pointing this out.

  3. pgulutzan says:

    For readers who don’t know me: I may handle products in a breaks-the-warranty just-looking-for-trouble way. A former colleague once counted me as the second-most-prolific MySQL bug reporter of all time. Although I’ve stopped posting on bugs.mysql.com, I still occasionally share. But TokuDB, as I’ve said, is good stuff.