Consistency management analysis of MySQL group replication data

A question from the customer. The customer is very confused about the data consistency guarantee mechanism of group replication, and has not understood it. In fact, it is the group replication parameter_ replication_ The meaning of several values of consistency is not clear. Here I give a few simple examples to illustrate the true meaning of several options contained in this parameter.

1, Concept

Concept of group replication: Group replication is a plug-in developed by MySQL based on traditional master-slave replication. This plug-in improves some major functional defects of the original master-slave replication, such as data consistency monitoring management, automatic conflict monitoring, automatic fault monitoring, automatic shrinkage and expansion node, automatic data compensation, etc.

Group replication from MySQL five point seven Publish to the latest version of MySQL 8.0.20 Through various defect repair and function upgrade, it has been relatively perfect.

Parameter group_ replication_ There are 5 values of consistency available:

1. EVENTUAL: to ensure the final consistency does not guarantee the real-time synchronization of data. (MySQL 8.0.14 Only this option before)
2. BEFORE: ensure strong local consistency, and do not guarantee real-time synchronization of data of other nodes.
3. AFTER: ensure strong global consistency, and ensure real-time synchronization of all node data.
4. BEFORE_AND_AFTER: the highest level to ensure strong local consistency and global consistency. Combine the features of befre and AFTER.
5. BEFORE_ON_PRIMARY_FAILOVER: ensures local consistency after promotion from node to primary.

Next, in the default mode of group replication, we discuss the meaning and usage scenarios of EVENTUAL, BEFORE and AFTER.

2, Environmental preparation

• Debian ytt1: 3306 (write node, referred to as node 1)
• Debian ytt2:3306 (read node, referred to as node 2)
• debian-ytt3:3306 (read node, referred to as node 3)

Here is the cluster YTT_ In the state of Mgr, node 1 is dominant, node 2 and node 3 are subordinate.

MySQL  debian-ytt1:3306 ssl  ytt  Py > c1 = dba.get_cluster('ytt_mgr');
MySQL  debian-ytt1:3306 ssl  ytt  Py > c1.status();
{
    "clusterName": "ytt_mgr",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "debian-ytt1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "debian-ytt1:3306": {
                "address": "debian-ytt1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "debian-ytt2:3306": {
                "address": "debian-ytt2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            },
            "debian-ytt3:3306": {
                "address": "debian-ytt3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": null,
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.20"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "debian-ytt1:3306"
}

3, Meaning and applicable scenarios of three option values

3.1 EVENTUAL

These options represent final consistency, and group replication defaults. That is to say, if the node with EVENTUAL is set, its read or write request can return the result immediately, without waiting for the relay log before the new request to finish processing.

Create a test table t1.

<debian-ytt1|mysql>create table t1 (id serial primary key, r1 int,r2 int,r3 char(36));
Query OK, 0 rows affected (0.07 sec)

Node 1 normally inserts a record.

<debian-ytt1|mysql>insert into t1 (r1,r2,r3) select 10,20,uuid();
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Node 2 applies this record to table t1 in time.

<debian-ytt2|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |   10 |   20 | e878289e-89c4-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
1 row in set (0.00 sec)

At this time, add a server level shared read lock to node 2 to artificially create congestion delay.

<debian-ytt2|mysql>lock table t1 read;
Query OK, 0 rows affected (0.00 sec)

Node 1 inserts a new record with ID 2 again.

<debian-ytt1|mysql>insert into t1 (r1,r2,r3) select 20,20,uuid();
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

<debian-ytt1|mysql>select  * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |   10 |   20 | e878289e-89c4-11ea-861d-08002753f58d |
|  2 |   20 |   20 | 2982d33f-89c5-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
2 rows in set (0.00 sec)

At this time, query node 2 again to return the result immediately, but the data is not up-to-date, and does not contain the record with the latest ID of 2, or the previous old data.

<debian-ytt2|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |   10 |   20 | e878289e-89c4-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
1 row in set (0.00 sec)

The current status of the record with ID 2 on node 2 is: it has been pulled to its own relay log, but has not been applied to table t1. After the shared read lock of table t1 is released, the application can continue. Now release the shared read lock of table t1, and query again for the latest records.

<debian-ytt2|mysql>unlock tables;
Query OK, 0 rows affected (0.01 sec)

<debian-ytt2|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |   10 |   20 | e878289e-89c4-11ea-861d-08002753f58d |
|  3 |   20 |   20 | 759cc5c0-89c7-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+

From the above examples, we can see the advantages and disadvantages of the final consistency pattern.

• Advantage: it can quickly return the data that this node has successfully applied without waiting for all data applications to complete.
• Disadvantage: the data returned may be older.

3.2 BEFORE

This type of option represents strong consistency of local nodes. That is to say, the local node set to this option must wait for the relay log data to be fully applied before executing a new request, otherwise it will wait all the time. The waiting time is proportional to the number of transactions not applied in the relay log.

For clarity, clear table t1 data.

<debian-ytt1|mysql>truncate t1;
Query OK, 0 rows affected (0.17 sec)

Start a new connection to node 2, share the read lock on table t1, and the corresponding SESSION ID =1.

<debian-ytt2|mysql>lock table t1 read;
Query OK, 0 rows affected (0.01 sec)

Insert a new record on node 1.

<debian-ytt1|mysql>insert into t1 select 1,1,1,uuid();
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

In addition, open a new connection to node 2, with the corresponding SESSION ID = 2. Set parameter group_replication_consistency=before, query table t1 data immediately after completion and wait.

<debian-ytt2|mysql>set @@group_replication_consistency='before';
Query OK, 0 rows affected (0.00 sec)

<debian-ytt2|mysql>select * from t1;

# HANG here!

Query table t1 data on node 3, and immediately return the record just inserted, that is, it has no impact on node 3.

<debian-ytt3|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |    1 |    1 | ee83837e-89ce-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
1 row in set (0.00 sec)

At this time, switch to the session with SESSION ID = 1 of node 2 and unlock table t1.

<debian-ytt2|mysql>unlock tables;
Query OK, 0 rows affected (0.00 sec)

Check the session with SESSION ID = 2 of node 2 again. The result has been returned. The time is 3 minutes and 17 seconds. Compared to EVENTUAL, it does not return results immediately.

<debian-ytt2|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |    1 |    1 | ee83837e-89ce-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
1 row in set (3 min 17.51 sec)

As you can see, the BEFORE mode ensures that the local node always reads the latest data. The biggest disadvantage is that we have to wait for the data in the local node relay log to be used normally. If there are many bad transaction blocks or large transactions written in the log, the node will be delayed greatly.

3.3 AFTER

Such options represent strong global consistency. For nodes set to this mode, you must wait for all other nodes in the cluster to apply the transactions in their relay logs before returning the results.

Group node 1 parameter_ replication_ Consistency is set to AFTER to clear table t1.

<debian-ytt1|mysql>truncate t1;
Query OK, 0 rows affected (0.22 sec)

<debian-ytt1|mysql>set @@group_replication_consistency='after';
Query OK, 0 rows affected (0.00 sec)

On node 2, add shared read lock to table t1.

<debian-ytt2|mysql>lock table t1 read;
Query OK, 0 rows affected (0.01 sec)

After that, a record is inserted in node 1, which does not return immediately and is in the waiting state. Because table t1 on node 2 is locked, the log of node 2 must wait for table t1 to be unlocked before it can be successfully applied.

<debian-ytt1|mysql>insert into t1 select 1,1,1,uuid();

# Waiting

At this time, return to node 2. Because of the mode default, return the result immediately, but the data is very old.

<debian-ytt2|mysql>select * from t1;
Empty set (0.00 sec)

At this time, query table t1 on node 3 and find that the request is also waiting. That is to say, although node 3 is also the default mode, since the master node is set to AFTER, node 3 must wait for other slave node logs to be applied before returning results.

<debian-ytt3|mysql>select * from t1;

Now unlock table t1 on node 2 and go back to the connection with ID 121 on node 1. The result has been returned, but it took 6 minutes and 47 seconds.

<debian-ytt1|mysql>insert into t1 select 1,1,1,uuid();
Query OK, 1 row affected (6 min 47.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

At this time, check the previous query on node 3, and the result has been returned.

<debian-ytt3|mysql>select * from t1;
+----+------+------+--------------------------------------+
| id | r1   | r2   | r3                                   |
+----+------+------+--------------------------------------+
|  1 |    1 |    1 | 49430687-89e9-11ea-861d-08002753f58d |
+----+------+------+--------------------------------------+
1 row in set (18.25 sec)

As can be seen from the above process, AFTER is a strong synchronization option. The priority is to ensure the data consistency of all nodes in the cluster, but it also brings a big performance problem: the total transaction submission time of the cluster depends on the slowest node in the group. If the slowest node encounters a failure, the other nodes must wait for a timeout rollback.

summary

In this paper, the setting of data consistency level parameter value of group replication is demonstrated in detail. As you can see, I only described the first three options. The latter two options are based on the combination of the first three options. There is no separate description here. You can experiment on your own if you are interested.