There's more than enough reason to ditch MySQL and switch over to MariaDB and stay there.

The MariaDB Blog’s performance test.

MariaDB is a fork of the MySQL source code, split off in the wake of concerns over what Oracle would do with MySQL licensing. (MySQL was purchased by Sun, which was subsequently snatched up by Oracle.) These are valid concerns, which I get to later in this article. In addition to its role as a “drop-in replacement” for MySQL, MariaDB also includes some new features that (some claim) make it better than MySQL.

Before getting into those features, I want to talk about MariaDB’s version-numbering scheme. At first, MariaDB’s versions were matched with MySQL versions—so MariaDB 5.1, for example, used the same code base as MySQL 5.1. As updates and fixes were supplied to the MySQL source tree, MariaDB would attempt to bring those same patches in. (In theory, there are monthly merges with the MySQL code.) But if new and unique features are added on a regular basis, I imagine that code-parity has transformed into a nightmare to maintain.

The MariaDB team seems to be aware of this, as they’ve decided to start over with a new numbering scheme. The newest version of MariaDB (which is currently still in alpha) is Maria 10.0, followed by a minor number:

mysql -P 3406 -u root -p

Enter password: ********

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 10.0.2-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> select version();

+—————-+

| version()      |

+—————-+

| 10.0.2-MariaDB |

+—————-+

1 row in set (0.01 sec)

MariaDB [(none)]>

MariaDB’s people give a long-winded, somewhat rambling explanation of why they did this—which still hasn’t stopped some developers from being upset—but it is what it is. They can’t continue to add new features and forever insist that it’s a completely compatible MySQL drop-in replacement.

And as for the new features? Let’s look at a couple of them.

Cassandra Engine

One unique feature of MariaDB is its engine for connecting to a Cassandra backend. The engine itself is just an intermediary that joins a Cassandra server running separately. (Cassandra is a NoSQL key-value store that was created at first at Facebook and later became an Apache project; while it can be used in clusters with no single point of failure, it’s still not ACID-compliant.) Generally speaking, if you’re going to use Cassandra as the engine, you’re not going to get nearly the speed or performance that you would with InnoDB or ExtraDB.

But you can access the data through MySQL, giving it an SQL-like feel and allowing for selects, inserts, updates, deletes, and even joins to an extent. But the MariaDB team says that the Cassandra engine shouldn’t be relied upon for much more than light data usage.

So this feature could be useful if you, uh, let’s see… well…

If you’re writing a software application that needs to access data from Cassandra, then you’re probably better off using the native Cassandra API rather than going through MySQL. I suppose if you’re fussing around with the mysql command line interface and need to grab some data, this Cassandra engine could come in useful—but if you’re going to do that, you might as well just fuss around with the Cassandra command-line interface.

So I’m not really sure about the use-case here, but that hasn’t stopped some people in the blogosphere from being excited about this feature.

OQGraph Engine

I won’t say a whole lot about this one, as it follows the same idea as Cassandra: the engine is really just an interface to the Open Query Graph computation engine. This might assist in some specialized applications, although mapping graph structures to an SQL format is a bit odd on the surface.

One important enhancement that brings additional power to MariaDB is the use of XtraDB as a drop-in replacement for InnoDB. But XtraDB adds modern scalability capabilities that are needed for today’s software—and that’s where we get to the heart of the difference. Oracle claims MySQL now scales better than ever before, and perhaps it does, but it’s only as good as its engine. And if the engine can’t truly scale as well as it should, then MySQL can’t.

Atomic Writes

One of the main reasons for choosing a relational database system over a typical NoSQL is for the full ACID compliance. Simply put, if there’s a failure, you don’t want to lose data. While failures might not often occur on our development machines, they’re a fact of life in many IT centers. Presently, the default InnoDB/XtraDB engine uses double-buffer approach to writing data, in order to make sure the data is successfully written in the event of a crash. However, when dealing with high-speed SSD devices (just as an example), the double buffer can have a negative impact on performance, preventing you from accessing the speed benefits of SSDs. The solution? You can now opt out of the double buffer and use what’s known as Atomic Writes. Use at your own risk, and probably not in production.

So again, interesting feature—but not a feature that should convince you to abandon MySQL for MariaDB.

Performance of MySQL vs MariaDB

Now I want to draw attention to a benchmark that was performed by the MariaDB team and make some additional notes about it. This blog offers an interesting point: MySQL 5.6 performs well up to 16 threads, but after that—although its performance continues to rise a bit—it doesn’t go up as well as the other versions to which it’s been compared (including MariaDB-5.5.28a and MariaDB-10.0.1; see the top of this article for a graph of the performance test). This is a common problem in parallel programming while attempting to target multiple cores and threads within a core. If algorithms are constructed correctly, you will continue to see advantages as your cores increase. The problem is you need to handle two approaches in your parallel programming: (1) Multithreaded across multiple cores, and (2) vectorization. These are the two facets to today’s multicore programming, and your code needs to handle both aspects correctly.

One common result of not coding these correctly is you’ll start out seeing an improvement in the first 8 or 16 threads, and after that you won’t get nearly the hoped-for improvement. When you see that problem, it means there’s likely trouble with the algorithms. (And this will be the case with either hyperthreads or hardware threads.) That’s what we’re seeing here with the MySQL benchmarks. To me, that’s an indication of trouble with MySQL scaling, and should be a cause for concern. MariaDB also has a slight problem in the same benchmark as the performance goes down slightly, but only barely; I would surmise that this isn’t a problem with the parallel algorithms.

I also don’t know how well the specific builds matched to the machines used in the test. When you compile Intel code, you need to have the compiler generate the proper size SIMD code for the target machine; if it’s a mismatch, you won’t get the hoped-for performance in your vectorization code. To do this correctly, you have to insert the correct pragmas into your code and then write your vectorization algorithms correctly, and, finally, turn on the proper compiler switches. I know that sounds silly, but I’ve seen products released with the wrong compiler switches far more often than you might expect. In any case, clearly the MySQL code wasn’t optimized for multicore and vectorization as well as MariaDB.

(What I’d really like to see is a branch of either MySQL or MariaDB compiled specifically with the Intel Xeon Phi coprocessor in mind, with code offloaded to the 61-core coprocessor, and someone try to spin up all 244 threads. Unfortunately, I don’t have access to such a machine. Also, if you want to learn more about vectorization and parallel coding, check out the recent “Intel Xeon Phi Coprocessor High Performance Programming” by Intel’s James Jeffers and James Reinders.)