Month: June 2017

Back in the days when somebody said MySQL, there was only the MySQL. You could pick different versions (4.0, 4.1) but the vendor was the same. This changed around MySQL 5.0/5.1 when Percona decided to release their own flavour of MySQL – Percona Server. A bit later, MariaDB joined with MariaDB 5.1 and the fun (or confusion) increased. What version should I use? What is the difference between MySQL 5.1, Percona Server 5.1 and MariaDB 5.1? Which one is faster? Which one is more stable? Which one has superior functionality? With time, this got worse as more and more changes were introduced in each of flavours. This blog post will be our attempt to summarize the key features that differentiate them. We will also try to give you some suggestions about which flavour may be the best for a given type of project. Let’s get started.

Oracle MySQL

It used to be the MySQL, now it’s the upstream. Most of the development starts here, each version starting from 5.6 resolves some internal contentions and brings better performance. New features are being added too on a regular basis. MySQL 5.6 brought us (among others) GTID and an initial implementation of parallel replication. It also gave us ability to execute most of the ALTERs in an online manner. Let’s take a look at the features of the latest MySQL version – MySQL 5.7

Features of MySQL 5.7

One of the major changes are changes in the deployment process – instead of different scripts, you can just run mysqld –initialize to set MySQL up from scratch. Another very important change – parallel replication based on logical clock. Finally, we can use parallel replication in all cases – no matter if you use multiple schemas or not. Another replication improvement is multi-source replication – a 5.7 slave can have multiple masters – it’s great feature if you want to build an aggregation slave and, let’s say, combine data from multiple, separate clusters.

InnoDB started to support spatial types, InnoDB buffer pool can finally be resized at runtime, online ALTERs have been improved to support more cases like partitioning and no-op ALTERs.

MySQL started to support JSON data types natively along with several new functions which are focused on adding functionality around JSON. Security of your data is very important these days, MySQL 5.7 supports data-at-rest encryption for file-per-table tablespaces. Some improvements have also been added to SSL support (SSL will be configured if keys are in place, a script is included which can be used to create certificates). From a user management perspective, password lifetime setup has been added which should make the design of password expiration policies a bit easier.

Another feature which is intended to assist DBAs is the ‘sys’ schema, a set of views designed to make it easier to use Performance Schema. It’s been included by default in MySQL 5.7.

Finally, MySQL Group Replication (and eventually MySQL InnoDB Cluster) has been added to MySQL 5.7. It works as a plugin and is included in recent versions of the 5.7 branch, but that is a topic of its own. In short, Group Replication allows you to build a “virtually” synchronous cluster.

This is definitely not a full list of features – if you are interested in all of them, you may want to consult MySQL 5.7 documentation.

Percona Server

At the beginning, there was a set of patches to apply to the MySQL source code which added some performance improvements and functionality. At some point, Percona decided to release their own build of MySQL which included these patches. In time, more development resources became available, so more and more features have been added.

In general, you can view Percona Server as the latest MySQL version with multiple patches/improvements. With time, some of the Percona Server feature improvements are replaced by features from the upstream – whenever Oracle develops a feature which supersedes one of functionalities added in Percona Server. As long as the implementation is on par, Percona removes their own code in favor of code from the upstream. This makes Percona Server basically a drop-in replacement for Oracle’s MySQL. One of the areas in which major performance improvements have been made is InnoDB. It’s been modified significantly enough to brand it as XtraDB. Currently it is fully compatible with InnoDB but it hasn’t always been like that. For instance, some features in Percona Server 5.5 were not compatible with MySQL 5.5. So you should exercise caution when you plan to migrate from Percona Server to MySQL.

What’s worth highlighting is that Percona strives to reimplement enterprise features of the upstream. In case of MySQL, examples are implementation of a thread pool or PAM authentication plugin. Let’s take a quick look at some of the features of the Percona Server.

Features of Percona Server 5.7

One of the main features of XtraDB is improved buffer pool scalability – even though there’s less and less contention due to the work Oracle does on every MySQL version, Percona’s engineering team strives to push performance even further and remove additional mutexes which may limit performance. Additionally, more data is written into InnoDB monitor (accessible via SHOW ENGINE INNODB STATUS) regarding contentions within InnoDB – e.g., a section on semaphores has been added.

Another set of improvements have been made in the area of I/O. In InnoDB, you can set a flush method only for InnoDB tablespaces and this causes double-buffering for InnoDB redo logs. XtraDB makes it possible to use O_DIRECT also for those files. It also adds more data regarding checkpointing to the output of SHOW ENGINE INNODB STATUS. Additionally to that, parallel doublewrite buffer and multi-threaded LRU flusher have been implemented to reduce contention in I/O operations within InnoDB.

Thread pool is another feature made available by Percona Server. In Oracle MySQL it’s available only in the Enterprise edition. Here you can use Percona’s implementation for free. In general, thread pool reduces contentions while serving high number of connections from the application by reusing existing connections to the database.

Two more features are direct replacements of features from the Enterprise version of MySQL. One of them is the PAM authentication plugin which has been developed by Percona and is designed to allow the use of plenty of different authentication options like LDAP, RSA SecurID or any other methods supported by PAM. Second feature is also related to security – audit log plugin. It will create a file with a record of actions taken on the database server.

From time to time, Percona introduces significant improvements to other storage engines like the changes they made in MEMORY engine which allowed VARCHAR or BLOB type of data to be used.

Introduction of backup locks was also a rather significant improvement. In Oracle and MariaDB the only method of locking the table to get a consistent backup was to use FLUSH TABLES WITH READ LOCK (FTWRL). It’s rather heavy and it forces MySQL to close all of the opened tables. Backup locks, on the other hand, use a more lightweight approach of metadata locks. In many cases of heavy loaded server running FTWRL takes too long (and locks the server for too long) to be considered feasible while backup locks make it possible to take a backup using mysqldump or xtrabackup.

Percona is open also on porting features from other vendors. One such example is port of MariaDB’s START TRANSACTION WITH CONSISTENT SNAPSHOTS. This feature is also related to backups – with its help, you can take a consistent logical backup (using mysqldump) without running FLUSH TABLE WITH READ LOCK.

Finally, three features which improve observability – first: user statistics. This is fairly light-weight feature which collects data about users, indexes, tables and threads. It allows you to find unused indexes or determine which user is responsible for the load on the server. Currently it’s partially redundant to performance_schema but it’s a bit lighter and it was created in the days of MySQL 5.0 – 5.1, where no one even dreamt about the performance_schema.

Second – enhanced slow query log. Again, it was added in times where the highest granularity of long_query_time was 1 second. With this addition you had microsecond granularity and a bunch of additional data about InnoDB stats per query or its overall performance characteristics. Did it create a temporary table? Did it use an index? Has it been cached in the MySQL query cache?

Third feature we mentioned couple of times above – Percona Server exposes significantly more data in SHOW ENGINE INNODB STATUS than upstream. It definitely helps to further understand the workload and catch more issues before they unfold.

Of course, this is not a full list – if you are interested in more details, you may want to check Percona Server’s documentation.

MariaDB

MariaDB started as a fork of MySQL but, with part of original MySQL development team joining MariaDB, it quickly focused on adding features. In MariaDB 5.3, lots of features had been added to the optimizer: Batch Key Access, MultiRange Read, Index Condition Pushdown to name a few. This allowed MariaDB to excel in some workloads where MySQL or Percona Server would struggle. Until now, some of those features have been added to MySQL (mostly in MySQL 5.6) but some are still unique to MariaDB.

Another important feature which was introduced by MariaDB was Global Transaction ID. Not too much later Oracle released its own implementation but MariaDB was the first to have it. Similar story is with another replication feature – multisource replication: MariaDB had it before Oracle. Now, recently released MariaDB 10.2 also contains features which will be made available in MySQL 8.0, which is still under development. We are talking about, for example, recursive common table expressions or window functions.

Features of MariaDB 10.2

As we mentioned, MariaDB 10.2 introduces window functions and recursive common table expressions – enhancements in SQL which should help developers to write more efficient SQL queries.

Very important change is that MariaDB 10.2 uses InnoDB. Up to 10.1, XtraDB has been used as the default storage. This, unfortunately, makes features added in latest XtraDB unavailable for MariaDB 10.2 users.

Improvements have been made in virtual columns – more limitations have been lifted in 10.2.

Finally, support for multiple triggers for the same event has been added – now you can create several, for example, ON UPDATE triggers on the same table.

Developers should benefit from JSON support, along with a couple of functions which are related to it. They should also like new functions which allows them to export spatial data into GeoJSON format. Talking about JSON, improvements have been made in EXPLAIN FORMAT=JSON output – more data is shown.

On security front, support for OpenSSL 1.1 and LibreSSL has been added.

Of course, this list is not complete and if you are interested into what has been added to MySQL 10.2, you may want to consult documentation.

Additionally to new features MariaDB 10.2 benefits from features implemented in previous versions. We’ll go through the most important ones.

The most important features of MariaDB 10.1

First of all, MariaDB since 10.1 comes bundled with Galera cluster – no need to install additional libraries, everything is ready to use.

MariaDB 10.1 brought implementation of data-at-rest encryption. Compared to the feature implemented in Oracle’s MySQL, MariaDB has it more extended. Not only it encrypts tablespaces but it also encrypts redo logs, temporary files and binary logs. This comes with some issues – CLI tools like mysqldump or xtrabackup can’t access binary logs and may have problems backing up encrypted instances (this is especially true for xtrabackup – quite recently MariaDB created xtrabackup fork called MariaDB Backup which supports data-at-rest encryption).

Ok, so which flavor I should use?

As it usually is, the correct answer would be: “It depends” 🙂 . We will share couple of our observations which may or may not help you to decide, but, at the end, it’s up to you to run benchmarks and find whatever option will work best for your environment and application.

First of all, let’s talk about the flow. Oracle releases new version – let’s say MySQL 5.7. Performance-wise, at that moment, this is the fastest MySQL flavor on the market. This is because only Oracle has enough resources to work on improving InnoDB to that extent. Within a couple of months (in case of 5.7, it was 4 months) Percona releases Percona Server 5.7 with their set of improvements – depending on the type of workload, it may deliver even better performance than the upstream. Finally, MariaDB adopts new upstream version and builds its new version on top of it.

This is how it looked like in a calendar (we are still talking about MySQL 5.7).

MySQL 5.7 GA: October 21st, 2015

Percona Server 5.7 GA: February 23rd, 2016

MariaDB 10.2 GA: May 23rd, 2017

Please note how long it took MariaDB to release a version based on MySQL 5.7 – all previous versions have been based on MySQL 5.6 and, obviously, delivered performance lower than MySQL 5.7. On the other hand, MariaDB 10.2 has been released with InnoDB replacing XtraDB. While it’s true that Oracle mostly closed the performance gap between MySQL and Percona Server, it’s still just “mostly”. As a result, MariaDB 10.2 may deliver performance lower than the one of Percona Server in some cases (and better in some other cases – due to optimizer work done in MariaDB 5.3, some of which hasn’t been recreated yet in MySQL).

Feature-wise, it’s more complex. MariaDB has been adding lots of features, so if you are interested into some of them, you may definitely consider using MariaDB. There is a downside of it too. Percona Server had a great deal of features differentiating it from upstream MySQL but when Oracle started to implement them in MySQL, Percona decided to depreciate their implementations in favor of using the implementation from upstream. This reduced the amount of code that’s different between MySQL and Percona Server, makes it easier to maintain Percona Server’s code and, what’s the most important, makes Percona Server 100% compatible with MySQL.

This is, unfortunately, not true for MariaDB. MariaDB introduced GTID first, that’s true, but after Oracle developed their version of GTID, MariaDB decided to stick to their own implementation. This blog is not the place to decide which implementation is better, but as a result we have to manage two different, incompatible GTID systems – it adds burden on any tool that manages replication and reduces interoperability. Sticking to replication – group commit and parallel replication: both Oracle and MariaDB have their own implementation and if you work with both of them, you need to learn them both to apply the required tuning – the knobs are different and work in a different way. Similar case is with virtual columns support – two different, not 100% compatible implementations which, as a result, made it not possible to easily dump data from MariaDB and load into Oracle’s MySQL (and vice versa) because the syntax is slightly different. So, should you decide to use a version of MariaDB for some brand new feature, you may end up getting stuck with it even if you’d like to migrate back to MySQL to use Oracle’s implementation. At best, migration would require much more effort to execute. Of course, if you stay in the one environment all the time, it may not affect you severely. But even then, a lack of compatibility will be noticeable for you, if only while you read blogs on the internet and find solutions that are not really applicable to your flavour of MySQL.

So, to sum it up – if you are interested in maintaining compatibility with MySQL, Percona Server (or MySQL itself, of course) would probably be the way to go. If you are interested in performance, as long as there is Percona Server built on top of the latest MySQL, it may be the way to go. Of course, you may want to benchmark MariaDB and see if your workload can’t benefit from some of optimizations which are still unique to MariaDB. Operational-wise, it’s probably good to stick to one of the environments (Oracle/Percona or MariaDB), whichever would work for you better. MySQL or Percona Server have an advantage in a way that they are more commonly used and it’s slightly easier to integrate them with external tools (because not all of the tools support all of the MariaDB features). If you would benefit from a new and shiny feature which has been just implemented in MariaDB, you should consider it, keeping in mind any potential compatibility issues and possible lower performance.

We hope this blog post gave you some ideas about different choices we have in MySQL world and different angles from which you can compare them. At the end of the day, it will be your task to decide what’s best for your setup. It may not be easy but then we still should be grateful that we have a choice and we can pick what works best for us.