Write Set In MySQL 5.7: Group Replication
2021-08-31 15:07 abce 阅读(197) 评论(0) 编辑 收藏 举报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)