MySQL 8.0: New Features in Replication

It is with extreme delight and a big smile on our face that we share the news that a new major MySQL release has been declared Generally Available (GA). The road to MySQL 8 was pretty eventful, sometimes painful and sometimes hard, always challenging, but nonetheless a great ride and an extremely rewarding journey for the engineers that have worked on it. This is another awesome release and we want you, as a MySQL user, to fully realize the exciting new things all around, in particular in replication! And if you want to learn more about other features in the MySQL server, I invite you to have a look at this post.

Introduction

In this post we will showcase the new MySQL Replication features introduced. It is no surprise that replication has been changing rapidly over the past years. In fact, more recently a new technology, MySQL Group Replication, was released in MySQL 5.7, and later it was also included in MySQL 8. It is continuously improved either through bug fixing or new features in both MySQL 5.7 and MySQL 8. But this is not the only new thing in MySQL 8. There is more.

Relevant replication areas that have been improved in MySQL 8:

  • Observability: more replication applier queuing details; improved tracing for Group Replication; replication filters statistics; additional cluster statistics on group replication; enhanced visibility into row-based replication.
  • Performance and efficiency: WRITESET based parallel replication applier; efficient synchronization between receiver and applier threads; flow control fine tuning; partial replication of JSON data types.
  • Operations: improved GTIDs handling; better delayed replication;  RESET MASTER command allows specifying the number of the next binary log file; option to expire binary logs with sub-day precision; extraction of log consistent positions for backup purposes; primary election with member weights; whitelisting with support for hostnames; per-channel replication filters in multi-source replication.
  • Resiliency: crash-recoverable DDLs in replication.
  • Usability: support for transaction savepoints for group replication; less locking between receiver, applier and user threads when disk is full.
  • Integration: More table related metadata was added to the binary log; More transaction related metadata also added to the binary log.
  • Code maintenance: removed legacy code (v1-v3 binary log support); removed duplicated information and code; improved structure of the group communication system component in the MySQL sources; removed old LOAD DATA events; removed old log events; added a service to notify group membership events to other internal components.
  • Defaults: several changes in defaults. This shall be covered on a separate blog post.
  • Deprecations: several features were deprecated. This shall be covered on a separate blog post.

New Features

Let’s detail the features in each individual section. This blog post will not address deprecations, default changes and feature removals. This will be done on a separate post.

Observability

A lot of work was done to make replication more observable. The focus was set on instrumenting the code and exposing data collected through performance schema. This was done across the board, from instrumenting the group communication system in group replication to the row based replication applier. Let’s have a look short at what was done:

  • Monitor row event apply progress. The row-based replication applier reports progress while applying changes. Does this through performance_schema event stages. Therefore, if the applier is seemingly taking a long time to apply a large set of rows, you can check its progress through the performance schema event stages infrastructure.
  • Fetch statistics on the entire replication pipeline. The applier pipeline is now instrumented and data collected is made available to the user through new columns on the existing receiver and applier tables. The user has access to data such as last transaction handled identifier, or commit timestamp with respect to the immediate and original server. Therefore they can use this data as input to diagnose and troubleshoot replication lag. This is also a better framework to infer how much time a replica is behind with respect to the original execution or the immediate relay server in the replication chain.
  • Observe and understand replication filters. A new set of performance schema tables tracks replication filters activities and displays information such as the number of times the filter was triggered or when was it activated.
  • Read cluster-wide statistics through any single member. Performance schema tables for group replication member stats and membership were extended with information stating, for instance, what is the member role or its version. Moreover, every member shows stats of all other members in the group, not just its own stats.
  • Analyze all Group Replication mutexes and threads. Group communication threads, mutexes and conditional variables are instrumented and are now visible in performance schema tables. More details here.
  • Trace low level events in Group Replication. A new tracing infrastructure was introduced in Group Replication so that the user can collect additional information that helps diagnosing and troubleshooting message passing issues in the group communication infrastructure. Read more about this here.

Performance and Efficiency

Making replication efficient and fast is also something that is continuously worked on. In MySQL 8, there are a set of features that enhance the replication applier pipeline, so that it is able to ingest more load coming in, faster and in a more efficient way. Here are the highlights:

  • Transactions spend less time in the replication applier pipeline. There is an improved synchronization mechanism between receiver and applier threads, which translates into less contention between receiver and applier threads. This enhancement allows both receiver and applier threads to do more work in parallel instead of serializing themselves in a lock that protects the relay log.
  • More transactions are applied in parallel.  Much improved parallel applier by relying on transaction WRITESETs (roughly the set of rows changed). This also allows the applier to install changes in parallel even for single threaded workloads coming in from replication. There is much to say about this feature, but you can learn more about the details here.
  • Predictable and sustained replication throughput fine tuning flow control. Flow control was also revised and improved. The user can now fine tune a group replication cluster so that the impact of imbalanced members is limited and the entire system converges to a sustained throughput.
  • Replicate partial JSON objects. The server is able to capture, replicate and apply partial JSON document changes. This makes replication of JSON objects quite efficient, since in row-based replication only a small fraction of the JSON document is captured – the actual changes. This means that the footprint for using JSON objects is small. Read about all the details here.

Operations

A lot of effort was put into operations. As MySQL provides more and more functionality it becomes more of a challenge to make the entire setup easier to use, manage and sustain. Add high availability and replication and all the sudden the system’s complexity can easily grow an order of magnitude. Therefore, the ability to observe and then react/manage a system like this becomes rather important. Here are the relevant improvements on this area in MySQL 8:

  • Update @@GTID_PURGED on an active server. The user is able to manipulate GTID_PURGED dynamically on a server that already has a GTID history in it. This facilitates use case scenarios where a logical backup, for instance, needs to be installed on a running server. You can find more details in the manual about this.
  • Delay your replication… in a better way! This is a feature that some users really appreciate because it allows them to keep a server running with artificially delayed replication. This gives the user a constantly updated “backup” shifted in time by a predefined time window. If catastrophic errors happen, for example an unintended DROP TABLE, within the window they can quickly resort to the delayed slave and use it for a fast recovery scheme. The enhancements include clarified semantics (delays are introduced per transaction and not per event as it used to happen) and a reimplementation that builds on new metadata – commit timestamps – introduced also in MySQL 8. Learn more about this here.
  • Reset and name your binary logs with one command. An extension to the RESET MASTER command was contributed and then merged – thank you Daniël van Eeden. Now one can specify the index number of the binary log file created after removing the existing ones by reseting the logs. This removes some operations burden by collapsing into a single command a set of commands, thus making this easier to sustain and operate. Read more about it here.
  • Expire your half day long binary logs. There is now an option to automatically purge the binary logs by setting a time window that is expressed in seconds. Thus the granularity is not days as it used to be, but rather seconds. This is also explained here.
  • Lock and read log positions almost instantly while taking a backup. A more intuitive and simple way to fetch consistent log positions was introduced in MySQL 8. By querying the performance schema table log_status, the server will “freeze” for a short while. However, despite being quite short, it will collect a consistent view over the log positions, which includes: GTID_EXECUTED; current binary log file name and offset; each replication channel relay log file name and offset; and storage engine log position (in case of InnoDB, Log Sequence Number), provided that the storage engine implements the interface to provide this data. This allows the backup tools to get a consistent position snapshot across binary logs, relay logs and storage engines. More details about this here.
  • Influence the primary election. In Group Replication, when deployed in single primary mode and the primary is removed from the group, a new server is automatically selected from the remaining servers and elected as the new primary. This is all fine and good. In MySQL 8 the user was given a mean to influence which server is going to be picked as the primary when the next primary failure happens. The user can do it by assigning different election weights to different members. The member with the highest weight wins the election and becomes the next primary. As such, this is a big operational enhancement. Many users expressed their needs to be able to create a failover plan that would consider different hardware profiles, different timezones, or different generic properties altogether. More technical details can be found here. This feature was backported also to MySQL 5.7.
  • Whitelist servers based using hostnames instead of IPs. In Group Replication, the user can restrict which servers are allowed to connect. Before this feature got in, the user could do it by specifying an IP address. However, in some setups the IP can float and only the hostnames are reliable for this purpose. As such having the whitelist understand names in addition to IPs would make this possible. Therefore this functionality was introduced in MySQL 8 (and backported to MySQL 5.7 as well). You can read more on this here.
  • Notice Group Replication protecting your data. This feature makes sure that if a server drops out of the group it will automatically set itself to read only. This prevents accidental writes to the members that leave the group both intentionally or unintentionally. More on this here. This feature was first introduced in MySQL 8 and later backported to MySQL 5.7.
  • Filter different data from different sources. Multi-source replication is quite a big feature in MySQL 5.7. In addition to being able to replicate from multiple sources, users want to also have different filter setups per source. MySQL 8 introduced a feature that the user can specify for which channel a given filter is set up. You can read more about it here.

Resiliency

Metaphorically speaking, MySQL has had a brain transplant. It has now a new data dictionary, which among other improvements also brings in crash-recoverable dictionary changes. Roughly, this makes DDLs atomic and recoverable. This functionality is also extended to include binary log recoverability for DDLs.

  • On recovery, recover my atomic DDLs too! When the server is restarting after a crash, the binary log and the InnoDB synchronize on to figure out which transactions to recover to bring both to a consistent state. Now atomic DDLs recovery also takes into account the contents of the binary log, so that metadata and the binary log is consistent.
  • On recovery, recover my atomic DDLs too… Oh, and my applier positions as well! If a crash happens while a replication applier is applying an atomic DDL, then recovery will make sure that the replication positions are consistent with the metadata changes.

Usability

With the monitoring and operations enhancements, usability comes naturally. But the following functionality deserves to be highlighted:

  • Roll back to a given savepoint in Group Replication. In MySQL 8, another Group Replication limitation that was lifted was the (lack of) support for transaction savepoints. This limitation was first lifted in MySQL 8 and then backported to MySQL 5.7 (already a long time ago).
  • Don’t be afraid to ask if disk space ran out! A lock was held while a replication thread was waiting for the disk space to be freed. This prevented users from easily querying performance schema tables to figure out what was wrong. MySQL 8 improves the interaction between these threads and allows the user to quickly diagnose problematic scenarios like this one by not blocking the monitoring commands. Details are here.

Integration

The information available in the binary log was extended to better integrate MySQL replication with other bits and pieces of the infrastructure that MySQL users have deployed side-by-side with MySQL. A big part of these enhancements consist in extending the table metadata in row based replication, but also a bit of additional data was added. Here is what was added:

  • Extract more details about your table from the binary log. Several users, that for on reason or another mine the binary log, have requested that more information related to table metadata was added into the binary log. Then they can more easily mine the log and extract the information they need without having to resort to follow up queries to information_schema or other sources of data, on the server that the binary log was created, as this is rather brittle, cumbersome and does not provide any guarantee of consistency by itself. Thus, this feature adds to the binary log additional data, for instance, character sets information, which columns of the table are signed and unsigned, the column names, and more. Further details here.
  • Extract more details about your transaction from the binary log. The binary log was enriched with additional transaction metadata. This includes the transaction size as well as a few commit timestamps that help implement other features, such as better observability of replication lag, and improve others, such as delayed slave. The commit timestamps added represent the commit timestamp of the original execution and the commit timestamp of the immediate master on the replication chain. You can read more about this here.

Code Maintenance

The replication code base is quite extensive. In MySQL 8 we continued to improve the code base. Granted, there are areas that could still use a bit of work, but there are others that we had a chance to remove, refactor or restructure. This is a continuous work though and one that is never in the spotlight as other features that provide additional functionality and thus are more user visible. Regardless, but I would like to spend a few sentences on this:

  • Removed very old binary log format cross-version conversion code. This removes code that was only used for a brief period of (non-GA) time in MySQL 5.1. This relates to a family of row events that is not used anymore. If you are familiar with the code, this was what was mostly on log_event_old.cc.
  • Removed cross-version compatibility code that was obsolete. There was code that was obsolete and only used for allowing conversion between obsolete binary log versions. This code was removed.
  • Removed deprecated LOAD DATA events code. Old and unused LOAD DATA events were removed.
  • MySQL group communication infrastructure fully tracked in mysql-trunk. This was a big step that allowed moving a part of the Group Replication plugin from its own infrastructure into the mysql-trunk code base – i.e., these were remains of a point in time that Group Replication was released as a separate plugin.
  • Removed redundant information in the group communication infrastructure. This work removed duplicate code and information for each member in internal memory structures and also introduced a better and more reliable way to track a member rejoin.
  • Publishing membership events to other components in the server. Group Replication membership events are propagated, through the new service registry, to other components in the server. These components only have to implement the listener interface and then will start getting a notification of events such as: a view has changed, quorum was lost, and so forth. This is extremely powerful, if you want to extend MySQL with a new component that reacts on membership changes.

Conclusion

MySQL 8 is very compelling from the replication enhancements standpoint. In addition there is a whole new set of amazing features outside of the replication realm as well. Get it from dev.mysql.com and give it a spin. Enjoy!

Thank you for using MySQL!

A final big thank you to our users that have contributed with patches and/or feedback.

4,834 total views, 32 views today

About Luis Soares

Luis Soares is the MySQL Replication Director 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