MySQL-8.0 Group Replication Research and Reform Summary

Since late February 2020, the author of this article (I) have researched and modified several new feature implementations of Percona-MySQL-8.0.18-9, mainly MySQL Group Replication (MGR) and clone, and filled functional gaps in Percona-MySQL-8.0.18-9 regarding distributed transaction disaster recovery, fixed its bugs and defects, and also carried out several other feature developments targeting the overall planning of KlustronDB. Now, I would like to share some of my findings and ideas based on Percona-MySQL-8.0.18-9. This article does not intend to fully introduce any new features of MySQL 8.0, because there are already several articles written by other peers online, and the most authoritative and complete introduction is always the official MySQL documentation. This article assumes that the reader is already familiar with these concepts and features, and I focus on my exploration and findings on the MGR and clone features of Percona-MySQL-8.0.18-9 for KlustronDB's requirements, as well as improvements to MGR in terms of distributed transaction disaster recovery.

Although there are still many vulnerabilities and functional gaps in disaster recovery handling for distributed transactions, MGR single primary mode has very good data consistency guarantees and disaster recovery capabilities while maintaining high performance, making it suitable for widespread use in production practice. MGR multi-primary mode, under heavy write loads, can cause significant TPS and latency fluctuations due to numerous transaction rollbacks resulting from data write conflicts among multiple nodes. Therefore, it is only suitable for scenarios with very few writes and primarily read-heavy workloads, such as storing a small amount of configuration parameters, similar to the use cases of ZooKeeper. In multi-primary mode, if one tries to avoid write conflicts by cleverly arranging at the application layer which primary node writes or updates which data sets so that they do not overlap, the complexity of application development would increase significantly, and existing applications would not automatically adapt, so it is believed that few users would do this. Finally, whether in single primary mode, multi-primary mode, or traditional asynchronous replication mode, a MySQL cluster can only handle data within several terabytes, because all nodes in the cluster store exactly the same N copies of the data. The cluster's data processing capacity is still limited by the data processing capability of each node, which in turn is constrained by server node computing resources and hardware costs, and cannot continuously increase its data storage and processing capability. Therefore, when the data scale grows beyond a certain point, it is still necessary to use a distributed database cluster.

In KlustronDB, we will use MGR single-primary mode, with each storage shard being an MGR single-primary cluster. All data is stored across multiple storage shards, and Kluscomp instances will ensure that data from different shards does not overlap.

MGR Automatic Cluster Management

One advantage of MGR is its automatic cluster management capability. This not only includes the commonly mentioned automatic master-slave switch capability, but also automatic group membership management and provisioning capability based on distributed recovery technology, which means that backup nodes can be automatically added during cluster operation.

First, let's talk about master-slave switching: All nodes in the cluster exchange node states by periodically running the Paxos protocol, allowing each MGR node to automatically detect if other nodes disappear or are added, and synchronize this information within the cluster. If the master node is found to be missing, all current nodes in the cluster will independently run the leader election algorithm to select a new consistent master node and synchronize the new decision within the cluster. If several nodes disappear and a quorum is lost, the cluster will automatically switch to read-only mode to prevent split-brain. This accomplishes a lot of the work that TDSQL or various distributed middleware have been doing for years with ZooKeeper/etcd, but with higher quality and reliability. It should be noted that in using and maintaining ZooKeeper, no one can say they haven't encountered pitfalls. By running the Paxos protocol in the MySQL node cluster, MGR avoids reliance on an external Paxos cluster, reducing operational workload and sources of errors. At the same time, MGR organically integrates Paxos with master-slave replication to achieve atomic broadcasting of transaction binlogs, greatly improving the cluster's disaster recovery capability. This part will be introduced later.

Through MGR's group member management, the MGR cluster can track nodes joining and leaving, and synchronize these states across all nodes in the cluster. After the master node leaves, a master election is automatically performed; if the cluster loses quorum due to network partition or node failure, the master node can also be automatically set to read-only to avoid split-brain. Every group member set generated by members joining or leaving is called a view in MGR. Each change of a view is a view change event, representing a member joining or leaving. This event is recorded in the binlog, so changes in the group topology are persistent and globally consistent information. Therefore, view change events are used as stopping points for data synchronization on the group_replication_recovery channel in MGR's distributed recovery.

Through distributed recovery, we can easily add a new standby node with a single SQL statement, or bring an old standby node up to the latest state. This feature means that two complex and important tasks are automated and simplified.

First, we can use it to perform a full data backup, replacing Percona Xtrabackup — simply create a new DB instance, add it to the MGR cluster as a standby node, execute the statement 'start group_replication', and after completing the distributed recovery and shutting it down, this instance will have all the current data of the primary node. Then, archiving its data directory can serve as a full backup.

Secondly, redoing a standby server is a common operation for DBAs, and now it can be completed very easily with just one command. Of course, under the MGR single-primary mode, DBAs probably no longer need to redo standby servers frequently—the redo operation used to be a DBA's powerful and ultimate weapon. DBAs would redo a standby server when the replication thread on the standby got stuck and couldn't proceed for various reasons, when the standby couldn't reconnect to the primary after the primary and standby were disconnected (for example, parts of the primary's binlogs required by the standby were purged), when using MyISAM tables and the standby exited abnormally leading to data inconsistencies on the standby, or when the standby replication fell so far behind the primary's data version that catching up was hopeless. It can be said that for a DBA managing hundreds of MySQL clusters, redoing a standby server is a daily task. If one day they didn't redo any standby servers, that would be unusual, and they would certainly suspect that something was wrong somewhere. Previously, DBAs mainly used Percona Xtrabackup combined with the mysqlbinlog tool to apply binlogs to redo standby servers. In TDSQL, there's an even cooler way to apply binlogs—treat the binlogs replicated from the primary as relay logs, perform a 'change master to' operation on a MySQL node using full data, and simulate it as a standby to apply these binlogs (this also requires developing a specific feature on the MySQL server).

All these tricky techniques are no longer needed. Users only need to create a new empty DB instance, configure the MGR parameters, and then run 'start group_replication'. MGR will perform distributed recovery: if it finds that the replica is brand new, or too far behind the master's latest binlog, or none of the group nodes have the binlog required by the new node, it will clone a donor's full InnoDB data (this step is similar to using Percona Xtrabackup for full replication in the past), and then use the traditional asynchronous replication combined with MGR to replay the transactional binlogs to complete the DB instance recovery. This step is similar to the old binlog catching-up process, but divided into two smaller steps: first, perform asynchronous replication to catch up the binlogs to the moment the node joins (view change marker), and then replay the binlogs received during the previous two phases.

Unlike the previous methods in the industry for creating standby nodes, MySQL MGR's distributed recovery is more efficient and more user-friendly. For example, both the binlog and clone processes can use multithreaded concurrent transmission for binlogs, and both can compress the transmission of binlog and InnoDB data files, and come with built-in rate limiting configuration to avoid exhausting disk and network bandwidth, which would affect business request processing. At the same time, the clone process does not block DML statements at all (though it does block DDL statements), meaning that unlike Percona Xtrabackup, it does not require a FTWRL operation for a period during the full backup to lock all DML write operations and transactions that are about to be committed; therefore, the impact on business request processing is minimal. The reason FTWRL can be avoided is thanks to MySQL 8.0's transactional data dictionary (data dictionary stored in InnoDB tables) and the fact that all metadata tables are stored in InnoDB. However, the disadvantage of MySQL 8.0's clone feature compared to Percona Xtrabackup is that it does not support other storage engines; it only supports InnoDB, as it is bound to InnoDB.

MGR's transactional atomic broadcast

Another major advantage of MGR is its atomic broadcast feature implemented based on the Paxos protocol. This ensures that whenever a transaction is committed on the master, MGR first makes sure that a simple majority (quorum) of nodes have received the complete binlog of that transaction, and only then do the nodes actually start the local commit. This avoids many of the primary-replica disaster recovery issues under the previous asynchronous replication model. These issues essentially occurred because, at the moment the primary node crashed, the binlogs of transactions being committed might be inconsistent between the primary and some or all of the replicas. For example, when the primary node crashes during a transaction T being committed, some replicas have received the full binlog of T, some replicas have received none of T's binlog, and others have received only part of T's binlog. In the case of an XA transaction, in MySQL 5.7, if the XA transaction is incomplete and the primary-replica link is broken, under certain conditions, the replica's event dispatch thread would continue waiting for the remaining binlog events of the transaction without being able to roll back and re-execute it, causing replication on the replica to completely hang, rendering that replica unusable. If this replica were to be elected as the primary at that time, it would be unable to become the primary because it can't complete 'stop slave', making that storage shard unwritable. I have already resolved these issues in TDSQL. In MySQL 8.0, these issues have been completely solved by MGR's atomic broadcast capability.

MGR's improvement of standby replication performance

The replica replication in MySQL 5.7 is based on the logical clock algorithm, which has a subtle potential flaw: the concurrency of replica replication is limited by the number of client connections on the primary server. This is because, in the logical clock algorithm, the total number of transactions whose sequence numbers are not greater than the current global sequence number cannot exceed the number of client connections. For example, if the primary server always has only 20 connections executing transactions, then the concurrency of the replica will never exceed 20. This means that if the replica needs to replay a large number of binlogs generated by a small number of connections, it will be very slow. Additionally, if a table has no primary key or unique index, replication on the replica will be very slow when processing transactions that delete or update many rows, causing the replica to fall further behind the primary. As a result, there may be no replica that can quickly take over in a primary-replica switch. Once the primary node fails, the cluster will be write-inaccessible for a relatively long period of time. At such times, DBAs become very anxious, and for critical business operations, they sometimes set up a new replica node to ease the pressure.

MGR can easily determine transaction dependencies based on the write set, allowing concurrent execution as long as two transactions do not conflict. If transactions T1 and T2 modify the same row R sequentially, then transaction T2 depends on T1, meaning T2 can only execute after T1 has completed. If the write sets of T1 and T2 do not intersect, then T1 and T2 have no dependency and can execute concurrently. MGR can even optionally execute non-conflicting transactions generated by the same connection concurrently (binlog_transaction_dependency_tracking = writes_set); however, considering that this does not conform to chronological logic, especially if the replica is used for read operations, this approach is not suitable, and it is still recommended to use binlog_transaction_dependency_tracking = write_set_session. Replica replication based on write_set and write_set_session completely removes the limitation of client concurrency, achieving very high replication performance. Coupled with MGR’s requirement for tables to have a primary key or unique index, this completely resolves the issue of replicas falling behind the primary and being unable to catch up. It can be expected that in production systems, the distance between the replica and the primary can generally remain very small, making master-slave switchover quick to implement under any circumstances. There will be no issue of not having a sufficiently up-to-date replica available, which would prevent rapid master-slave switchover. Additionally, with the full use of transactional storage engines and MGR's atomic broadcast capability, it can be expected that DBAs will rarely need to redo replicas in the future. Once everything is automated, one can’t help but wonder if DBAs should be happy or even happier 😉

The cost of MGR single primary

Of course, any good thing comes at a cost. Using MGR single-primary mode has slightly higher performance overhead and latency compared to the traditional binlog replication mode, and there are some functional constraints. These additional overheads are mainly due to the certify process—besides needing to transmit the transaction’s binlog, the certify process also requires calculating and transmitting the write set on the primary node, and the slave nodes need to receive and store the write set (even in single-primary mode, calculating, transmitting, and storing the write set is necessary); additionally, there is extra network latency caused by running the Paxos protocol, which slightly increases the commit latency of each transaction. Moreover, to maintain MGR’s high performance, it is required that at least half of the replicas be in the same data center as the primary node, otherwise the latency caused by the Paxos protocol will be higher—of course, this requirement is not excessive.

The functional constraint of MGR refers to the requirement that a table must have a primary key or a unique index. Moreover, when using MGR, due to the requirements of the clone plugin used for distributed recovery, only the InnoDB storage engine can be used. However, these constraints are completely acceptable — from the perspective of replication performance, every table should have a primary key or unique index, and TDSQL also has this requirement. Starting from MySQL 8.0, since the data dictionary is stored in InnoDB, InnoDB is fully integrated with MySQL. Therefore, the significance of other storage engines seems to be only as temporary tables or log tables (general log, slow log, if stored in a table, often use the CSV storage engine). Other transactional engines that meet specific needs — for example, MyRocks with high compression rates suitable for storing historical data — would require support for the MyRocks engine to be added to the clone plugin; otherwise, they cannot be used in MGR. The problem is that the working principle of clone is deeply tied to InnoDB. For example, InnoDB's undo log has been specifically modified to support the clone feature. How other engines can be integrated into the clone plugin is still an issue and would require considerable effort.

Finally, MGR imposes a limit on the size of transaction binlogs. This is because if the transmission of a transaction binlog takes longer than the timeout of the Paxos protocol, other nodes may perceive that they have timed out without receiving any messages from the primary node and mistakenly assume that the primary node has failed. For exactly the same reason, although TDSQL does not use the Paxos protocol, this limitation also exists in TDSQL — if the binlog of a committing transaction is too large and causes a transmission timeout, the DB instance's agent may mistakenly think that the primary and standby nodes are disconnected, potentially triggering an unintended primary-standby switch. Therefore, it is necessary to limit the size of transaction binlogs.

My modifications to MySQL 8.0

Unfortunately, MySQL 8.0's MGR still has many defects in XA transaction binlog disaster recovery. I have already fixed these bugs in TDSQL-Percona-MySQL-5.7.17-11 and reported them to the MySQL official team, also submitting the patch for the fix. These fixes have been thoroughly tested by the TDSQL team and long-term verified by a large number of users both inside and outside Tencent, proving them to be reliable. However, the MySQL official team had not done any fixes up to MySQL 8.0.18. These bugs still exist in MySQL 8.0, and new problems have arisen due to the addition of some new features. I spent a lot of time fixing these bugs and passed both MySQL test packages and disaster recovery tests. As a result, KlustronDB now has an indestructible disaster recovery capability.

In addition, some MySQL functions were implemented to meet the overall needs of KlustronDB, which will not be elaborated here.

The single primary mode of MGR is not suitable for direct use by users because users' programs need to adapt to changes in the primary node due to failover, as well as manage shard clusters (for example, starting and stopping clusters involves quite a bit of nuance), which increases development difficulty and is prone to errors. For this reason, the official MySQL documentation also recommends using MySQL Router or InnoDB Cluster together with MGR. However, due to the lack of XA transaction support in the official MySQL version (i.e., a deficiency in disaster recovery capability), using MySQL Router or InnoDB Cluster cannot reliably write to multiple shards within the same transaction. I have not used these two tools, but I assume they do not support distributed transactions either. I wonder if this is intended to avoid internal product competition with Oracle Database.

KlustronDB will use the MGR single primary mode, and will automatically handle MGR primary-secondary switch adaptation, distributed transaction disaster recovery and restoration, cluster management, and all other common tasks, becoming a simple and easy-to-use distributed database system for users and DBAs with complete disaster recovery capabilities.

MGR's status and contributions

I believe MGR further consolidates the position of the binlog subsystem in the MySQL ecosystem and prevents the marginalization of the binlog subsystem. It's important to know that originally, as MySQL gradually shifted towards fully using the InnoDB storage engine (the data dictionary uses InnoDB, eliminating .frm files; the industry gradually abandons MyISAM), the necessity of the binlog system became very low. Users could entirely use InnoDB redo log replication to set up standby machines, also achieving HA and read access on standby, which could completely replace traditional asynchronous replication. Moreover, this approach can avoid the resource consumption of writing and storing binlogs, especially since it introduces noticeable latency to transaction commits—the master node, in order to generate a binlog transaction sequence, serializes multiple transactions that could have been executed concurrently, executing the binlog flush stage and optionally the engine layer commit stage in a single thread. Although the InnoDB redo log occupies expectedly more storage space than the binlog (mainly due to page-level redo logs caused by B-tree page splits, etc.), this can also be offset by compression. Considering the common issues in traditional asynchronous replication, such as the standby getting stuck or falling too far behind the master, the replay of InnoDB redo logs is extremely fast because it only performs page-level modifications, saving the overhead of the upper-level code, making InnoDB redo log replication more attractive. During the KlustronDB planning last year, I had considered the option of using only InnoDB without MySQL. It should be noted that InnoDB itself comes with a simple SQL processor, fully capable of executing single-table queries and simple table joins. However, in the end, considering the many advantages of MGR mentioned above, I chose to use MGR.

I believe that MGR will continue to develop in the future, further enhancing MySQL's position and value in the database industry. I also believe that KlustronDB can fully harness the value of MySQL MGR and achieve larger-scale, efficient, and automated data management based on MGR.

END