The Latest and Greatest MySQL Replication Features in MySQL 5.7.5

The latest and greatest MySQL 5.7 development milestone release (DMR) is out (changelog). It is great to see such a strong and steady cadence of development releases.  The latest one, 5.7.5, is packed with awesome features. Let me highlight a few ones related to replication.

Storing Global Transaction Identifiers History in a system table.

MySQL 5.7.5 introduces a new replication system table that is used by the server to save global transaction identifiers (GTIDs) execution history. This means that the user can setup slaves without binary logs and still use GTIDs. Such slaves may not be candidates to replace the master in the event a fail-over needs to be done – they do not have the binary log enabled – but since they save GTID history means that they can auto position themselves in the replication stream and the user can track their data using transaction identifiers as well.

This is a nice feature and one that has been asked for a few times.

Options to fine tune the binary log group commit.

MySQL 5.7.5 presents a couple of options that allow the user to introduce artificial delays during the binary log group commit pipeline.

This means that the user can, depending on the workload, reduce the fsync rate by grouping more transactions together before synchronizing the data to the disk device. This is valuable especially in systems where such operation is expensive. In such cases, the user may want to improve throughput by delaying transactions just enough to create bigger groups, which translates into a smaller number of fsync events, which in turn translates into higher system throughput.

Incidentally, this option also allows more transactions to “see” each other during the transaction commit stage. This means that more transactions can be marked as non-contending and therefore more transactions may be scheduled in parallel on the slave. The end result is that the slave gets also an improvement in throughput, because the transaction scheduler has a chance to apply more transactions in parallel.

Support for slave_transaction_retries in Multi-threaded applier mode.

This change is actually  lifting a limitation on the existing multi-threaded slave implementation.

The basic idea is that the transaction scheduler (coordinator thread in technical lingo) will track each binary log event position and pass that onwards to the worker threads. Workers will keep track of where a transaction begins, so in the event of a transient failure, they can re-fetch the events needed and retry the transaction automatically.

Multi-threaded applier option to commit transactions following the transaction order in the relay-log.

In a nutshell, this feature ensures that the multi-threaded applier will commit transactions in the order that they appeared in the relay-log.

The server tracks dependencies between transactions and thus produces valid execution histories when applying and committing concurrent (but non-conflicting) transactions in any given order. However, some users have requested an option to force the slave worker threads to commit transactions in the exact same order as observed on the relay-log. As a consequence, when this option is set, transactions are logged to the slave’s binary log in the same order as they were observed on the master’s binary log.

This feature only works when the slave has the binary log ON and the slave threads are instructed to log into it.

Extended functionality around Global Transaction Identifiers.

The global transaction identifiers framework is continuously being extended and improved. MySQL 5.7.5, specifically, contains a new function named WAIT_FOR_EXECUTED_GTID_SET .

This function blocks until all transactions, with identifiers in the given set, to be applied to the server. It is useful for synchronizing different sessions together or even a session and the slave applier threads.

For instance, the application may reach out to the slave to perform some read operation. If the application has a requirement that the read must be done after a specific set of writes, then it can force the waiting by calling WAIT_FOR_EXECUTED_GTID_SET with the necessary GTIDs before issuing the read.

Try it!

The list above adds to the already extensive list of replication features in 5.7. Just to refresh your mind, let me list a few:

  • Semisync enhancements: it is now more flexible, a lot faster and more dependable;
  • Performance and scalability improvements: faster sender threads on the master and reduced contention on the binary log;
  • Flexibility/usability: More online operations – dynamic filters rules and changing master on the fly;
  • Improved monitoring: Instrumented replication codebase to report more internal status through performance_schema tables;

Go and try the latest MySQL 5.7 DMR. Let us know what you think. Your feedback is highly appreciated.

About Luis Soares

Luis Soares is a Principal Software Engineer and the MySQL Replication Team lead at Oracle. His research interests include replication technologies, dependable systems and high availability. Before joining the MySQL team, he was both a post-graduate student and a researcher at the University of Minho, Portugal, where he designed and implemented group-based replication protocols. Before that, he worked for a Portuguese multi-national company as a system analyst.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.