20 InnoDB Cluster - Changing History’s New Product #
In the previous lectures, we built solutions for read-write separation, high availability, and database management using MySQL replication technology. It is clear that all our discussions were based on MySQL replication.
However, MySQL replication is just a data synchronization technology. In order to achieve a high availability solution for the database, additional external components are required, such as MHA, Orchestrator, and database management platforms.
On the other hand, all the failover judgments we discussed before were based on a set of external heartbeat mechanisms. This relies on the reliability of the high availability suite itself. If the high availability suite is not reliable, it means that the high availability is not reliable either. For example, when the database crashes, can we ensure a successful switch to another node?
Lastly, the bottleneck of database replication technology lies in the fact that writes can only be done on one node, and then the logs are synchronized to other nodes. This means that single point writes will prevent the scalability of the database performance. So, can we have a technology that allows multiple nodes to write to MySQL and ensure data synchronization?
Yes, this is what we are going to study today: InnoDB Cluster. It is built on top of MySQL Group Replication (referred to as MGR). In order for you to make good use of InnoDB Cluster, today’s lecture will focus on explaining MGR technology, multi-node writing, InnoDB Cluster solutions. I hope that after learning this new high availability solution for MySQL, you will be able to master it.
MGR Technology #
MGR is a state machine-based data synchronization mechanism officially introduced in MySQL version 5.7. Similar to the semi-synchronous plugin, MGR can be enabled or disabled as a feature through the plugin.
MGR replication structure diagram
Note that when we talk about MGR, we should not simply consider it as a new data synchronization technology, but rather as a high availability solution. It is particularly suitable for financial-grade business scenarios where data consistency is highly important.
First of all, data synchronization between MGR nodes does not use replication technology, but instead uses log synchronization technology based on the Group Communication System (GCS) protocol.
GCS is itself a protocol similar to the Paxos algorithm, which requires that most nodes in the group receive the log before a transaction can be committed. Therefore, MGR strictly requires data consistency and is especially suitable for financial-grade environments. Since it is a Paxos-like algorithm, the number of nodes in the cluster must be an odd number in order to meet the requirement of majority.
Some of you may ask: Can the lossless semi-synchronous replication we introduced earlier also guarantee the strong consistency requirement of data?
Yes, although lossless semi-synchronous replication can guarantee the consistency of master-slave data, using GCS for data synchronization has better performance: when the MGR plugin is enabled, MySQL opens a new port for data synchronization rather than using the MySQL server port like replication does. This greatly improves the efficiency of replication.
Secondly, MGR has two modes:
- Single Primary mode;
- Multi Primary mode.
In Single Primary mode, only one node can write, while in Multi Primary mode, each node can write. In case there are conflicts in changes to the same row by multiple nodes, MySQL will automatically roll back one of the transactions and ensure the integrity and consistency of the data between multiple nodes.
Lastly, in Single Primary mode, MGR can automatically perform failover switching without relying on various external high availability suites. Everything is handled by the database itself, including the most complex primary election logic, without the need to deploy additional agents or components.
Now that we have mentioned the advantages of MGR, are there any disadvantages or limitations? Of course, there are several main points:
- Only InnoDB tables are supported, and each table must have a primary key;
- Currently, a maximum of 9 nodes is supported in an MGR cluster;
- If one node in the cluster experiences network jitter or instability, it will affect the performance of the entire cluster.
The first two points are not a big problem, as InnoDB is currently the mainstream storage engine used in MySQL, and 9 nodes are sufficient. And the third point I want to remind you about is that, unlike replication, MGR is extremely sensitive to network issues due to its use of the Paxos protocol. If one node’s network slows down, it will affect the performance of the entire cluster. On the other hand, with semi-synchronous replication, such as with an ACK of 1, if one node experiences network issues, it does not affect the overall cluster performance. Therefore, when deciding to use MGR, it is important to strictly ensure the quality of the network.
Now let’s take a look at the architectural adjustments that need to be made when using the multi-master mode in order to fully leverage the advantages of MGR in multi-master mode.
Considerations for Multi-Master Mode #
Conflict Detection #
Multi-master mode is the biggest innovation in the database field in recent years, and currently, only MySQL supports this multi-write Share Nothing architecture.
In multi-master mode, each transaction needs to verify if other nodes are also modifying the same record when it is committed on the local node. If there is a conflict, one of the transactions needs to be rolled back.
For example, if two nodes simultaneously execute the following SQL statements:
-- Node 1
UPDATE User SET money = money - 100 WHERE id = 1;
-- Node 2
UPDATE User SET money = money + 300 WHERE id = 1;
If the initial balance of the user is 200, when Node 1 executes the SQL, the user’s balance becomes 100. When Node 2 executes the SQL, the user’s balance becomes 500, resulting in different data on the nodes. Therefore, in multi-master mode, conflict detection is performed when a transaction is committed, and if conflicts are detected, the transaction is rolled back.
In the example above, if the transaction on Node 2 is committed first, the transaction on Node 1 will fail and be rolled back.
Therefore, to leverage the advantages of multi-master mode, conflicts during write operations should be avoided. The best practice is for each node to write to its own database. For example, Node 1 writes to DB1, Node 2 writes to DB2, and Node 3 writes to DB3. This way, the write performance of the cluster can be linearly improved.
However, this requires careful consideration during architecture design. Otherwise, multi-master mode may not bring the expected performance improvement.
Handling Auto-Increment #
In multi-master mode, there is a significant change in the logic of auto-increment. In simple terms, auto-increment is no longer continuous.
This is because if it were continuous, it would require waiting for the allocation of the auto-increment value across multiple nodes for each write operation, which would significantly decrease performance. In multi-master mode, we can address the performance issue of auto-increment by setting the initial value and step size. Let’s look at the following parameter:
group_replication_auto_increment_increment = 7
The parameter group_replication_auto_increment_increment default is 7, and the initial value of the auto-increment is the server-id.
For example, if the MGR has three nodes, Node1, Node2, and Node3, with server-ids 1, 2, and 3 respectively, then if the multi-master inserts auto-increment values in the order of Node1, Node1, Node2, Node3, Node1, the resulting auto-increment values will be:
As you can see, in multi-master mode, multiple nodes are allowed to concurrently generate auto-increment values. Therefore, the resulting auto-increment values are 1, 8, 16, 17, 22, and they may not be strictly continuous but are monotonically increasing, which is significantly different from single-instance MySQL. In Lecture 05 on table structure design, I also emphasized that it is best to avoid using auto-increment values as primary keys. This is because there are issues with MGR and similar auto-increment problems in subsequent distributed architectures. Therefore, for core business tables, it is more reliable and performs better to use ordered UUID.
In any case, after using MGR technology, all high availability tasks are automatically handled by the database. So, how should businesses utilize the capabilities of MGR? Do they still need mechanisms like VIP and DNS to ensure business transparency? Next, let’s take a look at how businesses can utilize the features of MGR to build high availability solutions.
InnoDB Cluster #
MGR is a data synchronization mechanism based on the Paxos algorithm. It synchronizes database status and logs to each node using the Paxos algorithm. However, to implement a complete high availability database solution, a higher level of InnoDB Cluster is needed.
An InnoDB Cluster consists of three components: MGR cluster, MySQL Shell, and MySQL Router. Specifically, they are shown in the following diagram:
MySQL Shell is used to manage the creation and modification of the MGR cluster. In the future, it is best not to manually manage the MGR cluster, but to use various interfaces encapsulated by MySQL Shell to perform various operations on MGR. For example:
mysql-js> cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-1:3306": {
"address": "ic-1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"ic-2:3306": {
"address": "ic-2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"ic-3:3306": {
"address": "ic-3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@localhost:6446"
}
MySQL Router is a lightweight proxy used for accessing the data in the MGR cluster by the business. When MGR switches (referring to the Single Primary mode), it automatically routes to the new MGR primary node, so the business does not need to be aware of the switching of the lower-level MGR data.
In order to minimize the performance impact caused by the introduction of MySQL Router, it is recommended by the official documentation to deploy MySQL Router together with the client program, in a similar way to sidecar deployment. This can reduce the additional network overhead and basically eliminate the impact of introducing MySQL Router.
Therefore, MySQL Router is positioned as a lightweight routing forwarding tool, rather than a database middleware, mainly to achieve transparency to the business after database switching.
Summary #
In this lecture, we learned about a new MySQL high availability solution: InnoDB Cluster. This high availability solution is likely to become the standard database high availability solution for the next generation of financial scenarios. InnoDB Cluster is based on MGR, which uses a Paxos-like algorithm for data synchronization, resulting in better performance and data integrity.
By combining the management tool MySQL Shell and the routing tool MySQL Router, a complete MySQL high availability solution can be built.
For financial users, I highly recommend this high availability solution. Of course, I recommend using InnoDB Cluster in the latest MySQL 8.0 version.