MariaDB vs. MySQL

The dominance of the LAMP (Linux, Apache, MySQL, PHP/​Python/​Perl) stack of technologies for websites has had a lot to do with MySQL’s position as a popular open source database. This preeminent position is probably why so many people were worried  when Sun Microsystems purchased MySQL (the company behind MySQL) and then when Oracle purchased Sun. One group of (mostly) former MySQL employees, led and funded by MySQL co-founder Michael “Monty” Widenius, was concerned enough to leave Sun/​Oracle, start a new company (Monty Program), and create a branch of MySQL called MariaDB.

MariaDB

The goal for Maria-DB is to be a drop-in replacement for MySQL – with more features and better performance.

MariaDB is based on the corresponding version of MySQL, if one exists. For example, MariaDB 5.1.53 is based on MySQL 5.1.53, with some added bug fixes, additional storage engines, new features, and performance improvements. Versions of MariaDB that do not have an equivalently numbered version of MySQL (e.g., MariaDB 5.2.4) contain major new features the developers felt warranted a new version number. When comparing the two here, I’ll focus on the additional features of MariaDB.

Differences and Features

As I write, the current stable releases for each database are MySQL 5.5.8 and MariaDB 5.2.4. MariaDB releases tend to lag behind equivalent versions of MySQL. The lag can be as short as a couple weeks or as long as two or three months, but it allows the MariaDB developers to merge the new MySQL code into the MariaDB source trees properly and to test for performance regressions or new bugs in both the MySQL code and the new MariaDB code.

At first glance, not much seems to have changed. The server is still mysqld , the command-line client is still mysql , the main configuration file is still my.cnf , and all connectors (e.g., the PHP or Python connectors) happily connect as if nothing has changed.

The most visible change, at least to users of the mysql client application is purely cosmetic: The command prompt has been altered to be more informative by indicating whether you are connected to a MariaDB or MySQL database, and it displays the name of the database you are connected to (it shows (none) if you aren’t connected to a specific database). Because of the special attention given by the MariaDB developers to maintain compatibility with MySQL, either client application can connect to either database, as shown in Figures 1 and 2.

Figure 1: The MariaDB 5.2.4 (top) and MySQL 5.5.8 mysql clients connecting to a MySQL 5.5.8 server. The MariaDB client shows (none) as the current database.

Figure 2: The MariaDB 5.2.4 (top) and MySQL 5.5.8 mysql clients connecting to a MariaDB 5.2.4 server. The MariaDB client shows (none) as the current database.

The next most visible change are the additional storage engines that ship with MariaDB (enter

show storage engines;

for a list). These include the Aria, XtraDB (an enhanced and extended version of the InnoDB storage engine), PBXT, FederatedX (a drop-in replacement for Federated), OQGraph, and SphinxSE storage engines. All of these are available for MySQL; however, to use them, you need to compile or install them yourself. With MariaDB, they are part of the default install. All of the standard storage engines in MySQL (MyISAM, blackhole, CSV, Memory, etc.) are included in MariaDB.

The INFORMATION_SCHEMA has also been enhanced. MariaDB has added more data in various tables. For example, the INFORMATION_SCHEMA.PLUGINS table now has PLUGIN_MATURITY and PLUGIN_AUTH_VERSION columns, so you can see the version numbers of your plugins and whether a version you have installed is of stable, beta, or other quality.

The INFORMATION_SCHEMA.PROCESSLIST table has been given a new column, TIME_MS . This column provides the same information as the column TIME , but in units of milliseconds with microsecond precision.

The slow query log has also been improved. In MariaDB, you can set the verbosity, filter out queries you don’t want logged, and limit the logging rate (e.g., to log only a quarter or tenth of the queries). As with other new features, the default configuration has this extra functionality turned off to be compatible with the behavior of MySQL [1].

Other Changes

Other changes in MariaDB are not so visible, but they help to improve performance, give MariaDB more flexibility, or both. One example on the performance side is with the new “table elimination” feature. Often, when you have highly normalized data [2] in your database, “views” are used for convenience when querying the database.

A view is kind of like a saved query. You can think of it as a particular way of viewing the data in your database. Views are very handy when used properly, but they come with a performance cost. Every time you use a view, all of the tables to which it is connected are queried, even if your query doesn’t actually need to query one or more of them.

The table elimination feature in MariaDB is designed to detect when a query will not use certain tables and eliminate them from the query plan. The performance increase this provides can be dramatic (depending on your database usage).

If you use the MyISAM storage engine for your database tables, the segmented MyISAM key cache could benefit you. The MyISAM key cache can suffer from lock contention if you have many application threads accessing the cache. The segmented key cache alleviates this problem by dividing the key cache into a number of “segments.” Up to 64 segments can be specified. Not every application will benefit from this feature, but if key cache lock contention is a bottleneck, it is a great way to increase performance without any application changes. For compatibility, this feature is turned off by default.

One new feature in MariaDB that adds flexibility is virtual columns, which are columns that are calculated and updated “on the fly.” An early version of this feature was contributed to MySQL back in 2008 by Andrey Zhakov, but it never found its way into a stable release of MySQL.

Often, you want your applications to do all the calculations and have your database just hold the data. However, in some cases, pushing calculations down to the database layer can be beneficial. For example, if you have several applications interacting with the database, pushing some of the calculations down into the database might help simplify application development and enforce data integrity.

Other new features in MariaDB have been made to make the code more modular and prepare for future development. One example is with the new storage engine-specific CREATE table feature. Previously, if a storage engine wanted to implement a feature that required new options for the CREATE table command, a patch would have to be applied to that section of the server code.

Ideally, storage engine code should be isolated so that, if you add it and don’t use it, no other part of the server is affected. If storage engines patch other parts of the code, a new beta or alpha storage engine cannot be included safely in a stable release.

MariaDB now has a standard way for storage engines to extend the CREATE table statement that keeps the storage engine code isolated and independent. Now, new storage engines can be included earlier in development because, if you choose not to use it, there is no effect.

All of the above features and others are described in greater detail in the MariaDB Knowledgebase [3].

Compatibility

With MariaDB, every effort has been made to make all improvements and new features as seamless and as transparent to end users as possible so they don’t have to throw out all of their previous work. To this end:

• Data and table definition files (.frm ) files are binary compatible.
• All client APIs, protocols, and struct s are identical.
• All filenames, binaries, paths, ports, sockets, and so on, are the same.
• All MySQL connectors (PHP, Perl, Python, Java, MyODBC, Ruby, the MySQL C connector, etc.) work unchanged with MariaDB.
• mysql-client packages work with MariaDB server and vice versa.

Therefore, for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. If you use the same main version, you don’t need to convert any data files, just as when upgrading from one MySQL 5.1.x version to another.

Additionally, a lot of development and testing of the upgrade scripts (which update data files when such updating is required) has made it easier to upgrade from MySQL 5.0 to MariaDB 5.1 than from MySQL 5.0 to MySQL 5.1.

The standard policy for any new feature is this: If the feature would introduce a compatibility issue, the default MariaDB configuration for the feature will either turn it off entirely (as with segmented key cache) or configure it to behave like the equivalent version of MySQL.

Because enforcement of the standard policy is not always possible, some unavoidable incompatibilities exist between MariaDB and MySQL. For example, if you are using a binary-only storage engine library, it must be recompiled specifically for the version of MariaDB you are using because of some internal changes to the storage engine API. Also, some output, such as the slow query log and the results of the CHECKSUM TABLE command, are different. So, if your scripts parse this output, you might have to make some changes.

One last note about compatibility: If you start using any of the new features in or configuration options of MariaDB, you will not be able to go back easily to even an equivalent version of MySQL.

Conclusion

Your situation should determine whether you choose MariaDB over MySQL. If you have a support contract with Oracle, then the choice has already been made; they don’t support MariaDB. However, several companies, such as SkySQL, offer full 24/​7 support for both MariaDB and MySQL.

MariaDB does trail behind the latest official MySQL version by anywhere from a few weeks to a few months, depending on how big the changes are between versions.

On the flip side, MariaDB includes many features you might find useful that you will not find in MySQL, and MariaDB eventually will have any new features introduced, after they’ve been vetted and tested extensively.

Another reason to choose MySQL over MariaDB is MySQL’s presence in the official repositories of just about every Linux distribution. Installing MySQL is a simple apt‑get or yum away from being installed. MariaDB is working toward inclusion in official repositories, but for now, it is only included in a few [6].

One reason to continue using MySQL is if you have a commercial license from Oracle that allows you incorporate MySQL into a closed source application. The MariaDB developers are bound by the terms of the GPL and cannot dual-license MariaDB.

Whichever database you ultimately choose, the competition between MariaDB and MySQL can only be good. After all, no one can say Microsoft’s Internet Explorer did not benefit from its long rivalry with Firefox.

Info

[1]
 MySQL Reference Manual: [http://dev.mysql.com/doc/refman/5.5/en/]

[2]
 Wikipedia article on database normalization: [http://en.wikipedia.org/wiki/Database_normalization]

[3]
 MariaDB Knowledgebase: [http://kb.askmonty.org/v/mariadb]

[4]
 Outside contributions in MySQL 5.5: [http://www.lenzg.net/archives/325-A-quick-summary-of-patch-contributions-included-in-MySQL-5.5.html]

[5]
 Outside contributions in MariaDB 5.2: [http://askmonty.org/blog/mariadb-5-2-is-released-as-stable]

[6]
 Distros with MariaDB: [http://kb.askmonty.org/v/where-can-i-download-mariadb]

Author

Daniel Bartholomew is a technical writer and system administrator for Monty Program. He lives in the US state of North Carolina.