Andrew Morgan on Databases

A new MySQL MySQL 5.7 Multi-threaded slave labs load has now been published on MySQL Labs. This represents a signifficant step in the process of speeding up the processing of replication events on the slave – letting it keep up with the master.

The original implementation of MTS made a simple assumption about what events could safely be applied in parallel (with no dependency on the ordering). That assumption was that different schemas had no dependencies on each other and so if you had 5 schemas then one event from each schema could be applied in parallel (this is described in MySQL 5.6 GA – Replication Enhancements). This approach delivered a much higher throughput on the slave but only if you were using multiple schemas – perfect for many deployments (such as multi-tenant systems) but if you only had a single database then it didn’t help.

Last year we released a new version which removed that dependency on multiple schemas – allowing events to be applied to the same schema in parallel. For this implementation the new assumption was that if a set of transactions were part of the same binlog group commit on the master then the locking on the master has already determined that they’re indepent of each other and so that same set of events can be applied in parallel on the slave.

The latest enhancement is to allow the user to tune how many transactions are likely to be in each of those BGC buckets. This is done at the expense of delaying commits on the master and can be controlled by specifying the longest time that the system should wait (binlog-group-commit-delay) and the maximum number of transactions that should be committed together (binlog-group-commit-count). Initial benchmark results are very possitive – a 6X throughput (if willing to tolerate a small impact to the latency on the master) or 3X with no impact to the master.

This is a feature where the mileage will definitely vary depending on your application and how you tune these two new parameters and so I’d certainly recommend that you try it out for yourselves. As always we’re anxious to get your feedback – for example through comments on this post or on the MySQL Replication forum.