by Pim van der Wal
I recently attended the Percona Live MySQL conference and wanted to share some of the exciting activity in the MySQL community. For our databases we use Percona Server 5.5 and standard MySQL replication with a multi-region offering using Tungsten Replicator.
At the conference there was a lot of attention for MySQL 5.6. The first aspect to hit the spotlight was the fact that Oracle had produced another quality release among worries that under their stewardship MySQL would slowly be killed off. There are a couple of aspects that still warrant attention like the number of bugs in the official MySQL bug database that are not open to the public. This makes it harder to do proper research on open or fixed bugs and introduces the possibility of duplicate bugs and duplicate work. That said, most of the presenters at the conference seem to agree that MySQL 5.6 is the way to go. It’s still early in the life cycle so there are not many production deployments yet and it makes sense to wait a couple of minor versions to ensure the bugs have been shaken out. Oracle is however starting to create a good track record for their QA of MySQL releases, especially in comparison to previous versions like 5.0 and 5.1.
So what makes MySQL 5.6 so interesting? First of all it performs better. Both MySQL 5.5 and 5.6 have seen numerous improvements in the way mutexes are handled resulting in much better concurrency. This shows especially on servers with more than 16 cores but we also expect to see improvements in systems with less cores. As usual everything is dependent on the type of workload so we’ll have to run our own benchmarks to know what to expect for Drupal. The other improvement worth mentioning here is for the query optimizer. There are a whole list improvements there but Index Condition Pushdown can have a big impact on complex queries.
What also has us very excited here at Acquia is the improved replication in MySQL 5.6. Multi-threaded slaves, global transaction identifiers and crash-safe slaves are great additions for our environment. These functionalities will allow us to reduce slave lag on multi-tenant systems during periods of high transaction volumes and make binary logs from multiple masters easier to analyze and process.
As I mentioned at the start we are currently using Percona Server 5.5 so we’ll wait for Percona Server 5.6 to be available and start running our benchmarks shortly after that to determine how Drupal fares with that version.
Of course MySQL 5.6 was not the only topic of the conference. In past years there was always a lot of attention for third-party storage engines. This has been replaced with replication and clustering solutions. The two main contenders in this arena are Tungsten by Continuent and Galera by Codership. Both allow a multi-master cluster with multi-threaded replication but Tungsten is asynchronous where Galera is synchronous. In reality Galera is “almost” synchronous which avoids the performance hit from using the dreaded two-phased commits but still gives most of the benefits. Both products have been around for many years now and have been proven in production situations. Each product has its pros and cons. From our perspective the pros are for Tungsten the negligible overhead on each master and for Galera the fact there is no replication lag. The cons are the lack of conflict resolution for Tungsten and the overhead of synchronous replication in a multi-region environment for Galera. Since we only use these technologies for our multi-region deployments the effect of high latency between those regions is a big driver in our choice.
So what else do you get to see at a MySQL conference? Engineers from Facebook, Google and a myriad other big websites always show up to tell you how they use, and more importantly, how they scale MySQL. Facebook for example, has so heavily modified their version of MySQL that they cannot easily upgrade anymore. They’re still on a 5.0 version although with all their modifications it’s probably faster than the standard 5.6, at least for their workload. The good news is that they release their changes into the open source community so we all get to benefit. It also makes it harder for Oracle to create closed source paid features for MySQL if there are free patches around that do the same thing.
I haven’t mentioned MariaDB, PostgreSQL or cloud related initiatives yet. MariaDB seems to be gaining more and more traction as a viable alternative to MySQL. MySQL consultancy company SkySQL has now been brought under the same business umbrella which broadens the appeal for companies looking to get solid support. Whoever comes out ahead doesn’t matter so much to users in my opinion. They’re pushing each other forward and growing the ecosystem. Even PostgreSQL fits in there. Open source databases have matured and are being used in large enterprises with well-known companies offering consultancy, support and even patches. Even if Oracle were to kill MySQL tomorrow most users won’t have to skip a beat and have several alternatives available.
So the cloud. Acquia offers Drupal as a Platform-as-a-Service using cloud-based hosting which allows for quick up and down scaling of servers. We configure and manage the database servers ourselves in order to provide the best MySQL experience possible for our clients. Now Amazon, HP and RackSpace are expanding their capabilities and are offering Database-as-a-Service. This means you as a user get to use your database as just that, a database, and the cloud provider will take care of maintenance, backups, restores, replication, etc. Interesting as this sounds this does mean giving up control. No more ssh access to the box, no more digging through binlogs, etc. The argument is of course that you shouldn’t need to. This service has a higher cost over regular instances but does offer higher performance with different I/O tiers and lower maintenance cost. At the moment this technology does not look very interesting to us considering that we have already automated most of those services. More importantly, it won’t allow us to use Percona Server instead of MySQL but it’s definitely something we’re going to continue watching.