MySQL face-off: MySQL or MariaDB?

Comments

You may have heard about the upstart MySQL database MariaDB, a branch of MySQL created in the wake of Oracle's purchase of Sun Microsystems. You'll find many great reasons to consider MariaDB, not least that MariaDB is led by the original author of MySQL, Monty Widenius. But there are reasons to stick with MySQL too.

It's best to think about this decision in terms of the community you'll be turning to for support and bug resolution. For example, if you want to purchase a support contract and feel that Oracle support is your best avenue for problem resolution, you should surely go with MySQL. Also, if you're considering an upgrade to MySQL Cluster CGE (Carrier Grade Edition) down the road, you'll want to start with Oracle's community edition.

The choice will probably depend a lot on your team and their familiarity with the open source culture. If they're more comfortable having a support contract and official answers from Oracle's call center, go with MySQL.

Feels like MySQL

For MySQL users who are considering a move away from Oracle's version, compatibility is the foremost concern. That's why knowing that MariaDB is a complete drop-in replacement is key. What does it mean?

Although the package names differ, when you check them out of the repository, most everything else remains the same. Binary names of command-line tools, such as mysqladmin, mysqldump, mysql shell, and the daemon all retain the same names. What's more, the data files are completely compatible. MariaDB will work "out of the box" with the data files and table definitions of your existing MySQL instance.

Day-to-day advantages

What's great about a drop-in replacement is that your application doesn't need to change one bit. In the case of replacing MySQL with MariaDB, you'll immediately enjoy performance advantages. Community improvements from Facebook, Twitter, Google, and Percona all roll into MariaDB sooner than they appear in MySQL.

MariaDB also provides user statistics and better instrumentation through the data dictionary information schema, including microsecond support. If you're using TIME or DATETIME data types, you can specify precision, such as TIME(4), where the number represents the number of digits after the decimal place. MariaDB supports up to six digits -- for example, 0.000001 second or one microsecond. No more wondering how fast is fast. For those cases where precision is not specified, MariaDB defaults to 0 for easy backward compatibility -- nice!

Have you ever waited in frustration while executing a long-running ALTER TABLE in MySQL? MariaDB gives you a command-line progress indicator of such operations so that you can plan ahead. Welcome changes indeed!

Query execution is typically a big challenge for Web-facing applications. The brains behind query execution in MySQL is the query optimizer, and here too MariaDB offers serious improvements, including better subquery optimization, as well as faster, more efficient, and more consistent joins, derived tables, and views. In addition, MariaDB gives you additional control over how the optimizer makes decisions, exposing more internal instrumentation and configuration as server variables you can set.

MariaDB has also incorporated a kernel enhancement that removes mutexes that were terrible for performance. A mutex is a type of lock that serializes access to resources in the kernel. If the resource is currently held, a process must wait until it is available. An existing mutex built into the MySQL kernel slowed things down dramatically on modern hardware. Removing it helps MariaDB scale on the large SMP boxes that are becoming increasingly common these days.

Lastly, if you've wanted to move to row-based replication but been held back by the omission of SQL statements in row-based replication logs, MariaDB has addressed this too.

Row-based replication isn't generally readable because you're sending the raw data block, the before and after "image" of that data, not the SQL statement that was executed. The data block is delivered to the slave database and written directly to the file, no reexecution of SQL. For this reason, the SQL statement was left out of logging in MySQL.

In MariaDB, the SQL statements are logged to the binary logs for row-based replication just as they are for statement-based replication. Having that SQL statement can be very helpful for troubleshooting and in cases where you want to do point-in-time recovery. It's a much-needed addition.

Pushing the envelope

For those who want to push the envelope further, MariaDB has features that take you beyond the stock MySQL functionality.

For starters, you have two new high-performance storage engines to choose from: Aria and XtraDB. Although these are not drop-in compatible with existing MySQL deployments, you can rebuild a table to or from these engines with a simple ALTER command.

Next up you get access to a whole new clustering technology called Galera. This is completely different from NDB Cluster and its many known problems. Galera allows active-active multimaster updating, which does not work well with NDB Cluster because of the limitations on JOINs. Here you can really start to scale writes on cloud servers. What's more, you get access to parallel and synchronous replication features.

Want to get NoSQL speed? Consider the HandlerSocket plug-in, which enables direct access to storage engines without going through the optimizer, boosting velocity by 10 times or more. You can also now get a row of data returned in JSON format using dynamic columns in MariaDB -- not so in MySQL.

MariaDB includes yet another new storage engine, Cassandra SE, that allows you to read or write data into a Cassandra data store. Finally, integration between SQL and NoSQL made easy!

Want to consolidate data from multiple master databases? Multisource replication is exactly what you're looking for. Assuming your source data is stored in multiple schemas, they can all be brought together on one instance downstream -- again, not possible in MySQL.

As if all of these reasons aren't enough, MariaDB is a fully GPL-licensed version of MySQL. None of the plug-ins or other components are closed source. This brings all of the advantages of open source, from security and transparency to identifying bugs. MySQL is available under the GPL or a commercial license provided by Oracle. As a result, some components are open source, but others are not.

Comparing apples and oranges

If you're evaluating the various alternative distributions of MySQL, you'll need to weigh the pros and cons.

Percona, for example, offers another alternative to Oracle's MySQL Community Edition. Percona tends to take a more conservative approach to rolling in new features. This likely amounts to a small plus for stability but a minus point for not having the very latest and greatest features. Percona is still a big step ahead of MySQL, but perhaps not as close to the bleeding edge of what's happening across the MySQL world as MariaDB.

Drizzle is yet another fork of MySQL. In this case, though, it's a complete rewrite aimed at cloud deployments. Open source yes, but it's not a drop-in replacement for MySQL. You'll have to dump and reload your data, perhaps tweak your application as well, to get everything to work perfectly.

Although MariaDB may lag Percona a bit in terms of adoption, its popularity is growing quickly. For instance, Red Hat is replacing MySQL with MariaDB in its enterprise distribution, and Google recently devoted an engineer to the MariaDB project. As a serious alternative to MySQL, the case for MariaDB seems only to get stronger.