Improved Semi-Sync Replication in MySQL 5.7

The latest version of MySQL, MySQL 5.7, has seen great improvements in semi-sync replication, a feature which is implemented by a plugin that has to be installed on both master and slave. Traditionally, MySQL replication has been asynchronous and has not functioned in real-time. As a result, it had some delays(this can be improved with a more reliable GTID based,MTS replication) in applying and replicating datasets from master to slave. With semi-synchronous replication, there has been a great performance improvement in addition to the built-in asynchronous replication since the MySQL 5.6.

To learn more about MySQL 5.7, please download Datavail’s recently released whitepaper, MySQL 5.7 Features, Enhancements and Upgrade Path. This whitepaper discusses enhancements, security plugins and benefits of upgrading to MySQL 5.7.

This blog post focuses on the improved semi-sync replication in MySQL 5.7, including its benefits, features, and limitations.

Benefits

Semisynchronous replication can be used as an alternative to asynchronous replication:

The important improvements to semi-sync replication include better concurrency and loss-less replication, which can help users improve their data integrity. Transaction commit latency is highly minimized in MySQL 5.7 as user sessions wait for acknowledgement from a slave before committing transaction.

With semi-synchronous replication, you will be able to get a guarantee that transactions will be replicated to at least one slave in the case of a master crash. This presents a great benefit in that it offers strong data integrity with no phantom read and great ease in the recovery process in the crashed semi-sync master servers.

Features

One great feature is the ability to set the number of slaves the transaction can be replicated to before externalizing it to users. This is achieved when the semi-sync master makes the transaction pause until an acknowledgment is received from those many slaves. At least one slave in the HA group has to acknowledge that transaction has been received before the client can confirm the transaction commit on the master. This can be configured as needed and adds resiliency.

A considerable effort has been made in MySQL 5.7 to improve replication performance, such as the new semi-sync master plugin that creates separate threads to send/receive acknowledgements from its semi-sync slaves. This thread is automatically created when a semi sync master is enabled, and gets destroyed automatically when it is disabled — so no action is required from the user.

The throughput gap to asynchronous replication has now become relatively small. Even with expected latency per transaction, the “pipeline” is now optimized and allows very effective use of a significantly larger number of concurrent threads. This results in a reduced effect on the system’s throughput, with great benefits to both local and wide area network deployments when the added latency gets supported by user applications.

Semi-sync throughput is higher than that of an asynchronous replication when the master has durability settings set to (sync-binlog=1; innodb_flush_log_at_trx_commit=1), and greatly increases when fast storage systems are used. There are status variables to monitor the semi-sync status on master and slaves.

Limitations

The performance of semi-sync is greatly dependent on the workload type being executed on the master. Also, the main shortcoming of semi-sync is added transaction latency, but it is significantly reduced on a fast network for most purposes. Semi-synchronous replication commits are a bit slower due to the need to await slaves, therefore resulting in some performance impact.

There are many scenarios where Semi-sync replication may be useful. Semi-synchronous replication performance is best suited for close servers communicating over fast networks, as opposed to the distant servers communicating over slow networks.