MySQL Replication--参数binlog_transaction_dependency_tracking测试

官方参数解释

binlog_transaction_dependency_tracking specifies the source of dependency information that the source records in the binary log to help replicas determine which transactions can be executed in parallel. The possible values are:

COMMIT_ORDER: Dependency information is generated from the source's commit timestamps. This is the default.

WRITESET: Dependency information is generated from the source's write set, and any transactions that write different tuples can be parallelized.

WRITESET_SESSION: Dependency information is generated from the source's write set, and any transactions that write different tuples can be parallelized, with the exception that no two updates from the same session can be reordered.

In WRITESET or WRITESET_SESSION mode, transactions can commit out of order unless you also set slave_preserve_commit_order=1.

For some transactions, the WRITESET and WRITESET_SESSION modes cannot improve on the results that would have been returned in COMMIT_ORDER mode. This is the case for transactions that have empty or partial write sets, transactions that update tables without primary or unique keys, and transactions that update parent tables in a foreign key relationship. In these situations, the source uses COMMIT_ORDER mode to generate the dependency information instead.

MySQL对参数binlog_transaction_dependency_tracking提供了三个可选值：COMMIT_ORDER\WRITESET\WRITESET_SESSION,这三个可选值会影响MySQL如何Track事务操作，最终影响Binlog中last_committed值。

COMMIT_ORDER(默认参数)，依赖信息根据源上事务提交的时间顺序产生。
WRITESET，依赖信息根据源上WRITESET产生，写入不同元组(Tuples)的事务可以并发执行。
WRITESET_SESSION，依赖信息根据源上WRITESET产生，写入不同元组(Tuples)的事务可以并发执行，但相同会话的操作不能并发执行。

在参数transaction_write_set_extraction被设置为XXHASH64后，对于WRITESET和WRITESET_SESSION两种方式，MySQL不再按照事务提交时间来计算事务是否可以并发执行，而是根据事务中修改记录的唯一性来判定事务是否可以执行，当两个事务修改的数据在主键索引或唯一索引上不存在冲突时，则可认为这两个事务可以并发执行。

WRITESET和WRITESET_SESSION的区别在于如何"处理统一会话下多个事务"：

WRITESET：同一个会话中多个事务如果没有"唯一数据"冲突，则这些事务可以并发执行(分配相同last_committed值)。
WRITESET_SESSION：同一个会话中多个事务即使没有"唯一数据"冲突，分配上个事务的sequence_number为下个事务的last_committed值，这些事务也不可以并发执行(不同last_committed值)。

对于不同会话的两个事务，WRITESET和WRITESET_SESSION都只考虑两个事务修改的数据是否存在"唯一数据"冲突，如果不冲突则分配相同的last_committed值，使得两个事务可以并发执行。

基础配置文件

transaction_write_set_extraction  = XXHASH64

测试脚本(同一会话执行)

分别设置binlog_transaction_dependency_tracking参数的值为WRITESET_SESSION|WRITESET|COMMIT_ORDER，然后执行下面脚本测试：

SET GLOBAL binlog_transaction_dependency_tracking= XXXX;

drop table if exists tb1001;

CREATE TABLE `tb1001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

flush logs;

insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
update tb1001 set c2=now() where id=2;
insert into tb1001(c1,c2)select 1,now();

1、基于COMMIT_ORDER方式

## 查看last_committed和sequence_number
mysqlbinlog  mysql-bin.000170  |grep 'last_committed'
#210318 22:55:01 server id 16124245  end_log_pos 259 CRC32 0xa923a87f   GTID    last_committed=0        sequence_number=1       rbr_only=yes
#210318 22:55:06 server id 16124245  end_log_pos 603 CRC32 0x3904fd26   GTID    last_committed=1        sequence_number=2       rbr_only=yes
#210318 22:55:07 server id 16124245  end_log_pos 947 CRC32 0x35d878e0   GTID    last_committed=2        sequence_number=3       rbr_only=yes
#210318 22:55:10 server id 16124245  end_log_pos 1291 CRC32 0x00893153  GTID    last_committed=3        sequence_number=4       rbr_only=yes
#210318 22:55:15 server id 16124245  end_log_pos 1635 CRC32 0x164e7579  GTID    last_committed=4        sequence_number=5       rbr_only=yes
#210318 22:55:17 server id 16124245  end_log_pos 1992 CRC32 0xf0bd97d3  GTID    last_committed=5        sequence_number=6       rbr_only=yes

2、基于WriteSet方式

## 查看last_committed和sequence_number
mysqlbinlog  mysql-bin.000173  |grep 'last_committed'
#210318 22:36:56 server id 16124245  end_log_pos 259 CRC32 0xfdf7cdae   GTID    last_committed=0        sequence_number=1       rbr_only=yes
#210318 22:36:57 server id 16124245  end_log_pos 603 CRC32 0x895d39e8   GTID    last_committed=1        sequence_number=2       rbr_only=yes
#210318 22:36:58 server id 16124245  end_log_pos 947 CRC32 0x3c08fe04   GTID    last_committed=1        sequence_number=3       rbr_only=yes
#210318 22:36:59 server id 16124245  end_log_pos 1291 CRC32 0x1b1332c6  GTID    last_committed=1        sequence_number=4       rbr_only=yes
#210318 22:37:22 server id 16124245  end_log_pos 1635 CRC32 0x6d8777d6  GTID    last_committed=2        sequence_number=5       rbr_only=yes
#210318 22:37:25 server id 16124245  end_log_pos 1992 CRC32 0xdef06b83  GTID    last_committed=1        sequence_number=6       rbr_only=yes

3、基于WRITESET_SESSION方式

## 查看last_committed和sequence_number
mysqlbinlog  mysql-bin.000176  |grep 'last_committed'
#210318 23:06:24 server id 16124245  end_log_pos 259 CRC32 0xf290ad17   GTID    last_committed=0        sequence_number=1       rbr_only=yes
#210318 23:06:25 server id 16124245  end_log_pos 603 CRC32 0x863a5951   GTID    last_committed=1        sequence_number=2       rbr_only=yes
#210318 23:06:25 server id 16124245  end_log_pos 947 CRC32 0x674db7f5   GTID    last_committed=2        sequence_number=3       rbr_only=yes
#210318 23:06:26 server id 16124245  end_log_pos 1291 CRC32 0x15a470d8  GTID    last_committed=3        sequence_number=4       rbr_only=yes
#210318 23:06:30 server id 16124245  end_log_pos 1635 CRC32 0xfcedbae5  GTID    last_committed=4        sequence_number=5       rbr_only=yes
#210318 23:06:32 server id 16124245  end_log_pos 1992 CRC32 0xed7fab96  GTID    last_committed=5        sequence_number=6       rbr_only=yes

测试脚本(多个会话执行)

分别设置binlog_transaction_dependency_tracking参数的值为WRITESET_SESSION|WRITE_SET，然后执行下面脚本测试：

SET GLOBAL binlog_transaction_dependency_tracking= XXXX;

drop table if exists tb1001;

CREATE TABLE `tb1001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

flush logs;

# 先在会话1执行：
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();

# 再在会话2执行：
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();

# 最后在会话3执行：
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();
insert into tb1001(c1,c2)select 1,now();

1、基于WRITESET方式

#210318 23:43:14 server id 16124245  end_log_pos 259 CRC32 0xd04c8e39   GTID    last_committed=0        sequence_number=1       rbr_only=yes
#210318 23:43:14 server id 16124245  end_log_pos 603 CRC32 0x494d111d   GTID    last_committed=1        sequence_number=2       rbr_only=yes
#210318 23:43:15 server id 16124245  end_log_pos 947 CRC32 0x6c592029   GTID    last_committed=1        sequence_number=3       rbr_only=yes
#210318 23:43:17 server id 16124245  end_log_pos 1291 CRC32 0xfe96d168  GTID    last_committed=1        sequence_number=4       rbr_only=yes
#210318 23:43:17 server id 16124245  end_log_pos 1635 CRC32 0xadb9bd3b  GTID    last_committed=1        sequence_number=5       rbr_only=yes
#210318 23:43:18 server id 16124245  end_log_pos 1979 CRC32 0x17717497  GTID    last_committed=1        sequence_number=6       rbr_only=yes
#210318 23:43:21 server id 16124245  end_log_pos 2323 CRC32 0xe985ea83  GTID    last_committed=1        sequence_number=7       rbr_only=yes
#210318 23:43:21 server id 16124245  end_log_pos 2667 CRC32 0xa9073906  GTID    last_committed=1        sequence_number=8       rbr_only=yes
#210318 23:43:21 server id 16124245  end_log_pos 3011 CRC32 0x9d5c4644  GTID    last_committed=1        sequence_number=9       rbr_only=yes

2、基于WRITESET_SESSION方式

#210318 23:38:28 server id 16124245  end_log_pos 259 CRC32 0xc539fe7f   GTID    last_committed=0        sequence_number=1       rbr_only=yes
#210318 23:38:29 server id 16124245  end_log_pos 603 CRC32 0xb1930a39   GTID    last_committed=1        sequence_number=2       rbr_only=yes
#210318 23:38:30 server id 16124245  end_log_pos 947 CRC32 0xd6b333dc   GTID    last_committed=2        sequence_number=3       rbr_only=yes
#210318 23:38:40 server id 16124245  end_log_pos 1291 CRC32 0xc53b1434  GTID    last_committed=3        sequence_number=4       rbr_only=yes
#210318 23:38:41 server id 16124245  end_log_pos 1635 CRC32 0x51f0323c  GTID    last_committed=4        sequence_number=5       rbr_only=yes
#210318 23:38:42 server id 16124245  end_log_pos 1979 CRC32 0xd5364c5c  GTID    last_committed=5        sequence_number=6       rbr_only=yes
#210318 23:38:54 server id 16124245  end_log_pos 2323 CRC32 0xe9c26fd3  GTID    last_committed=1        sequence_number=7       rbr_only=yes
#210318 23:38:55 server id 16124245  end_log_pos 2667 CRC32 0xecc7f42b  GTID    last_committed=7        sequence_number=8       rbr_only=yes
#210318 23:38:56 server id 16124245  end_log_pos 3011 CRC32 0x7e26ca87  GTID    last_committed=8        sequence_number=9       rbr_only=yes

参考资料

https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_transaction_dependency_tracking

https://www.jianshu.com/p/9fc9144fa3ac

posted @ 2021-04-11 13:36 TeyGao 阅读(2080) 评论(0) 编辑收藏举报

刷新页面返回顶部