Mysql 19-Apr-2017

Replication Features in MySQL 8.0.1

MySQL 8.0.1 has just been released with a wonderful set of features across the board. Specifically in replication, there is plenty of new and exciting functionality as well as performance improvements.  In this post I am going to summarize the replication features of this development milestone release.

  • The Group Replication plugin. You can now use Group Replication together with MySQL 8.0.1. Group Replication had been released as a server plugin in MySQL 5.7.17 and it is now released together with MySQL 8.0.1 as well.
  • Transaction SAVEPOINT support in Group Replication. Transaction savepoints are now supported in Group Replication.
  • Commit timestamps in the binary log. The binary log generated by a MySQL 8.0.1 master contains two new timestamps referring to the points in time when the transaction was committed on the original master and on the immediate master. This is interesting in chained replication scenarios, as transactions may have committed several hops up the chain. As such, these timestamps are useful for tracking how much a slave is behind the immediate and original master, or for instance, to compute more precisely how much delay the slave applier should introduce while handling a transaction.
  • Improved Delayed Replication. By relying on the extra timestamp information in the binary log, immediate commit and original commit timestamps, the binary log applier is capable of better calculating the delay it needs to introduce. This work makes the delay calculation more reliable in chained replication scenarios.
  • More instrumentation for the replication applier. New fields were added to the existing performance schema tables. The user can now see at which time the last transaction in the applier was queued in the relay log, when it was processed and when it was applied. In addition, the newly introduced immediate and original commit timestamps are also exposed. This feature will greatly help users to profile the applier execution and conduct analysis when they find that for some reason there is replication lag.
  • Channel specific filters in multi-source replication. This allows user to specify different replication filter rules for different replication channels. Therefore, the slave will be able to filter out (or transform) the execution of selected replicated changes on a per-source basis.
  • Additional Metadata in the binary log. The binary log from a MySQL 8.0.1 server shall contain additional table metadata information into the ROW-based changes. For instance, whether a numeric column is SIGNED or UNSIGNED, additional information about column character set, or even the name of the columns. The user is also able to control the amount of metadata logged.
  • RESET MASTER command extensions. The RESET MASTER command was extended. The user can now specify the binary log file index number to start from once the reset happens, instead of starting from .000001. The bulk of this patch was contributed by Daniël van Eeden (see BUG#77482). Thank you Daniël!
  • Synchronized DDL and binary log commits. With the new data dictionary work done in MySQL 8, there are several DDL statements that were made atomic. As such, DDLs can be synchronized with the binary log commit operation, and as a consequence both are kept in sync. In the event of the server crashing while the DDL changes are being written to the binary log, then on recovery, the DDL will either be committed or rolled back, depending whether the DDL changes were fully written to the binary log or not, respectively. In summary, several DDL statements are now as crash-safe as regular DML statements with respect to replication operations.
  • More efficient replication threads. By improving the synchronization between receiver (I/O) and applier (SQL) threads, the slave applier throughput goes up considerably. This improvement alone can, in some workloads, get a throughput increase of up to 24% on Sysbench RW and up to 64% on Sysbench Update Index.
  • Write-set based multi-threaded applier scheduling policy.  Group replication uses write-sets to first detect conflicting transactions and also to establish dependencies between transactions, deeming which can and cannot be applied in parallel. In MySQL 8.0.1 this functionality was extended to asynchronous replication by making the master also track dependencies between transactions by looking into their write-sets. As such, it can calculate the optimal parallelization window and encode that in the binary log. Then the applier on the slave can use this information to apply as many transactions in parallel as possible. This approach is quite interesting, since this enhancement means that the slave can even apply in parallel a workload that is single threaded on the master! Typically, this will exhibit a large throughput improvement, even when compared to the commit order parallelization scheme introduced in MySQL 5.7 which was already a huge leap forward back then.
  • Configuration variable to expire binary logs after N seconds. A new system variable, binlog_expire_logs_seconds, was introduced. It allows the user to instruct the server to automatically expire a log that is older than the number of seconds configured.

MySQL 8.0.1 is an interesting release. In the replication context alone, it exhibits quite a compelling list of new features. These range from more instrumentation and monitoring extensions, to additional metadata and information in the binary log, to better performance and efficiency and to improved configurability and overall management of the replication framework. But, there is no need to repeat myself. And as a matter of fact, there shall be additional blog posts detailing the new features, so stay tuned! Meanwhile, you can download the new MySQL 8.0.1 DMR and check it out. As usual, lets us know your feedback, e.g., through bug reports, feature requests, the replication mailing list or even through comments on this or other blogs on this website. Mind you that MySQL 8.0.1 is a development milestone release (DMR), thence not declared generally available yet. Use it at your own risk.