I was interested to hear about semi-sync replication improvements in MySQL’s 5.7.4 DMR release and decided to check it out. I previously blogged about poor semi-sync performance and was pretty disappointed from semi-sync’s performance across WAN distances back then, particularly with many client threads.
The basic environment of these tests was:
Without further ado, here’s the TpmC results I got for a single client thread:
These graphs are interactive, so mouse-over for more details. I’m using log scales to better highlight the differences.
The blue bars represent transactions per second (more is better). The red bars represent average latency per transaction per client (less is better). Remember these transactions are synchronously being copied across the US before the client can execute another.
The first test is our control: Async allows ~273 TPS on a single thread. Once we introduce synchronicity, we clearly see the bulk of the time is that round trip. Note that MySQL 5.5 and 5.6 are a bit higher than MySQL 5.7 and Percona XtraDB Cluster, the latter of which show pretty similar results.
This gets more interesting to see if we redo the same tests, but with 32 test threads:
In the MySQL 5.5 and 5.6 tests, we can clearly see nasty serialization. Both really don’t allow more performance than single threaded sysbench. I was happy to see, however, that this seems to be dramatically improved in MySQL 5.7, nice job Oracle!
AFTER_SYNC and AFTER_COMMIT vary, but AFTER_SYNC is the default and is preferred over AFTER_COMMIT. The reasoning here is AFTER_SYNC forces the semi-sync wait BEFORE the transaction is committed on the master. The client still must wait for the semi-sync in AFTER_COMMIT, but other transactions may see its data on the master BEFORE we confirm the semi-sync slave has received it. This is potentially bad because if the master crashed at that instant, clients may have read data from the master that did not make it to a failover slave. This is a type of ‘phantom read’ and Yoshinori explains it in more detail here.
I also want to discuss the Percona XtraDB Cluster results, Galera here is somewhat slower than MySQL 5.7 semi-sync. There may be some enhancements to Galera that can be made (competition is a good thing), but there are still some significant differences here:
Semi-sync in MySQL 5.7 looks like a great improvement. Any form of synchronicity is always going to be expensive, particularly over 10s and 100s of milliseconds of latency. With MySQL 5.7, I’d be much more apt to recommend semi-sync as an option than in previous releases. Thanks to Oracle for investing here.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.