Write Set In MySQL 5.7: Group Replication

write set并不是只出现在mysql 8.0中，其实在mysql 5.7中就有了，只是做了一些隐藏。本文会将讲解mysql 5.7中的write set。

 

在组复制中，write set用于两个方面：

1.加快组内成员之间的复制

2.多写环境的验证过程

 

组复制使用了标准复制的很多特性，包括logical_clock并行复制。这意味着它的applier进程可以利用中继日志中的并行间隔来并行地应用事务。这里强调relay log是因为在组复制中，并不是使用binary log进行复制，而只是使用relay log。

让我们来看看relay log：

> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)


-- On a member of the group that is not the primary, to rotate the relay logs
--   of the applier (FLUSH RELAY LOGS does not work with Group Replication):
> STOP GROUP_REPLICATION; START GROUP_REPLICATION;
Query OK, 0 rows affected (9.10 sec)

Query OK, 0 rows affected (3.15 sec)


-- All the next commands on the primary member of the group:
> SET autocommit = ON;
Query OK, 0 rows affected (0.00 sec)

> FLUSH BINARY LOGS;
Query OK, 0 rows affected (0.00 sec)

> CREATE DATABASE test_jfg_ws;
Query OK, 1 row affected (0.01 sec)

> CREATE TABLE test_jfg_ws.test_jfg_ws (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   str varchar(80) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.01 sec)

> INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('a');
Query OK, 1 row affected (0.00 sec)

> INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('b');
Query OK, 1 row affected (0.00 sec)

> INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('c');
Query OK, 1 row affected (0.00 sec)

　　

执行上面的命令后，在一个从节点的relay log中可以看到日志信息。注意，三个插入的并行间隔(parallelism intervals)是有重叠的

# mysqlbinlog my_relaylog-group_replication_applier.N | grep -e last_  |
    sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/'
#180106 19:31:36 [...] last_committed=0 sequence_number=0 [...]
#180106 19:31:36 [...] last_committed=1 sequence_number=2 [...] -- CREATE DB
#180106 19:31:36 [...] last_committed=2 sequence_number=3 [...] -- CREATE TB
#180106 19:31:36 [...] last_committed=3 sequence_number=4 [...] -- INSERT a
#180106 19:31:36 [...] last_committed=3 sequence_number=5 [...] -- INSERT b
#180106 19:31:36 [...] last_committed=3 sequence_number=6 [...] -- INSERT c

因为间隔(intervals)是重叠的，三个事务可以被并行执行。然而，直至mysql 5.7.20，在binary log中是没有重叠的间隔(intervals)的。这也意味着，常规的slave不能执行并行执行这些事务。(注意这三个事务在主库上是串行的)

这里你就能理解我为什么要执行flush binary logs了吧。下面的信息是我从主库的binary log中看到的：

# mysqlbinlog my_binlog.M | grep -e last_ |
    sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/'
#180106 19:31:59 [...] last_committed=0 sequence_number=1 [...] -- CREATE DB
#180106 19:32:02 [...] last_committed=1 sequence_number=2 [...] -- CREATE TB
#180106 19:32:05 [...] last_committed=2 sequence_number=3 [...] -- INSERT a
#180106 19:32:08 [...] last_committed=3 sequence_number=4 [...] -- INSERT b
#180106 19:32:11 [...] last_committed=4 sequence_number=5 [...] -- INSERT c

这里我们先停下，想一想：中继日志比二进制日志的并行度间隔更宽。组复制中的成员可以比常规复制的复制速度更快，因为relaylog的并行间隔比binarylog更宽。

这怎么可能，又是如何实现的呢？

这张图解释了组复制是如何执行和提交事务的。可以看到master2和master3并不是从master1的binarylog进行复制，但是仍会使用relaylog。

我们现在能理解或测试到，并行间隔宽(parallelism interval widening)发生在certify阶段，这一阶段会使用write set来检测事务冲突，同时，做了并行间隔宽。这样，如果开启的并行复制，就可以在组复制内运行多个并发事务。(Avoiding lag in Group Replication is what made parallelism interval widening in MySQL 8.0 possible.)

由于bug#86078，在mysql8.0和mysql5.7.20中，没有正确地结合不区分大小写的排序规则来识别事务依赖关系。来看看这是如何影响组复制：

-- On a secondary member of the group, to rotate the relay logs
--   of the applier (FLUSH RELAY LOGS does not work with Group Replication):
> STOP GROUP_REPLICATION; START GROUP_REPLICATION;
Query OK, 0 rows affected (9.26 sec)

Query OK, 0 rows affected (3.16 sec)


-- All the next commands on the primary member of the group:
> DELETE FROM test_jfg_ws.test_jfg_ws WHERE str = 'c';
Query OK, 1 row affected (0.00 sec)

> INSERT INTO test_jfg_ws.test_jfg_ws (str) VALUES ('C');
Query OK, 1 row affected (0.00 sec)

执行结束后，查看relaylog的内容(注意观察delete和insert的间隔重叠)

# mysqlbinlog my_relaylog-group_replication_applier.N | grep -e last_  |
    sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/'
#180106 19:36:14 [...] last_committed=0 sequence_number=0 [...]
#180106 19:36:14 [...] last_committed=1 sequence_number=2 [...] -- DELETE c
#180106 19:36:14 [...] last_committed=1 sequence_number=3 [...] -- INSERT C

在这里，delete和write可以并行是错误的(bug#86078)