Oracle MySQL 5.6 vs Percona Server 5.5 vs MariaDB 5.5

Database management systems overview

(Oracle) MySQL 5.6

The world’s most popular open source database currently owned and driven by Oracle.

There were big optimizer improvements planned for MySQL 6.0, but it was eventually abandoned. MariaDB and Oracle teams have ported code from MySQL 6.0 to MariaDB 5.3 and MySQL 5.6 respectfully.

MySQL 5.6.x has brought great improvements in Innodb scalability, transparency and replication. For example, multi-threaded slaves delivered significant performance increases to MySQL replication when handling multiple schemas.

First version of newest stable branch (5.6.x) has been released in April 2011. It’s a big leap - in terms of performance - in comparison with previous 5.5.x branch, so anything lower than 5.6 should not be even considered.

Percona Server 5.5

Percona Server is an enhanced drop-in replacement for MySQL. It offers breakthrough performance, scalability, features, and instrumentation.

Percona focus on providing a solution for the most demanding applications, empowering users to get the best performance and lowest downtime possible. Percona focuses on compatibility with (Oracle’s) MySQL, high quality, and small but high value changes focused on the most pressing problems they observe in the demanding environments their customers are running.

Percona is known for numerous optimizations for InnoDB engine. They have developed XtraDB (drop-in replacement for InnoDB).

Unfortunately it’s newest 5.6 branch is still not stable.

MariaDB 5.5

MariaDB is a community-developed fork of the MySQL led by its original developers (including Michael Widenius, the founder of MySQL) started after Oracle had taken over MySQL.

MariaDB 5.5 is compatible with MySQL 5.5. However next version is numbered 10 as it will not import all features from MySQL 5.6 breaking compatibility between these systems.

MariaDB’s API and protocol are compatible with those used by MySQL, which means that all connectors, libraries and applications which work with MySQL should also work on MariaDB.

MariaDB is known of pushing boundaries with the most advanced optimizer in MySQL space as well as leading the way in integrating with NoSQL solutions with its Cassandra Storage Engine.

Worth mentioning is that upgrade from MySQL 5.0 to MariaDB is easier than from MySQL 5.0 to MySQL 5.1.

Storage engines overview

InnoDB

InnoDB is the default storage engine for MySQL as of MySQL 5.5. It provides the standard ACID-compliant transaction features, along with foreign key support).

InnoDB became a product of Oracle Corporation after its acquisition of Innobase Oy in October 2005.

In contrast to MyISAM (previous default storage engine), it uses row-level locking, which means fewer lock conflicts when different sessions access different rows, fewer changes for rollbacks and possibility to lock a single row for a long time.

XtraDB

Percona XtraDB is an enhanced version of the InnoDB storage engine, designed to better scale on modern hardware, and including a variety of other features useful in high performance environments. It is fully backwards compatible, and so can be used as a drop-in replacement for standard InnoDB.

Percona XtraDB includes all of InnoDB’s robust, reliable ACID-compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability, more metrics, and more scalability.

It is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful. The new features are especially designed to alleviate some of InnoDB’s limitations.

Percona improvements include, among other things, CPU scalability fixes (improves performance on systems with multi-cpus), IO scalability fixes, better diagnostics, faster crash recovery, etc.

XtraDB is available as part of Percona Server and MariaDB only.

Comparison matrix

  Oracle MySQL 5.6 (InnoDB) Percona Server 5.5 (InnoDB) MariaDB 5.5 (XtraDB)
General information MySQL Percona MariaDB
Latest stable release 5.6.11 (April 2013) 5.5.30 (April 2013) 5.5.30 (March 2013)
Future MySQL compatibility + + - (SQL syntax and startup options in 10.0, full in 10.1)
MySQL data and table definition files (.frm) files binary compatibility + + +
Client APIs, protocols and connectors compatibility + + +
InnoDB MySQL Percona MariaDB
InnoDB compatibility + (native InnoDB) + (XtraDB) + (XtraDB)
Availability of XtraDB engine - (not available) + +
InnoDB-specific improvements + - (only in XtraDB) - (only in 10.x)
CPU scalability fixes (using multi-cpus more efficiently) + (using operating system memory allocators) + (XtraDB) + (using jemalloc)
I/O scalability fixes + (not exactly the same as XtraDB) + (XtraDB) + (XtraDB)
Crash recovery improvements + + (XtraDB) + (XtraDB)
Replication MySQL Percona MariaDB
Replication with multi-threaded slaves + (per database) - (per database in 5.6 alpha) - (per table in 10.x
Optimized row-based replication + n/a n/a
Multi-source replication - - - (only in 10.x)
Others MySQL Percona MariaDB
Optimizer enhancements + - (in 5.6)? + (probably best of all)
NoSQL Interface via memcached + (memcached) - + (cassandra, memcached in 10.x)
File Sort Optimization + - (in 5.6)? - (only in 10.x)
Faster ORDER BY nidxcol LIMIT N + - (in 5.6)? - (in 10.x)
JOIN optimizations - - +
EXPLAIN improvements + - (in 5.6)? - (partly, fully in 10.0.2)
Disk access optimizations + - (in 5.6)? + (more than MySQL)
Cost-based choice of range vs. index_merge - - +
Subquery optimizations + - (in 5.6)? +

Summary (pros and cons)

Oracle MySQL 5.6

Pros

Cons

Percona Server 5.5.

Pros

Cons

MariaDB 5.5

Pros

Cons

Update (03-09-2013): Feedback from Michael Widenius (MariaDB)

Update (05-09-2013): Feedback from Michael Widenius (MariaDB)

References

  1. by sobstel • May 2013 • blog archive