🪴 Obsidian Publish

MySQL 并行复制Binlog

23分钟阅读

Releted：26.问题定位：备库为什么延迟几个小时？

单线程复制/重放 binlog

在官方 5.6 版本之前，MySQL 只支持单线程复制。所以，如果主库并发高、TPS 高时，就会出现严重的主备延迟问题。（主库一次执行 N 个指令，但是备库只执行 1 个指令）

600|600

能够增强备库复制能力的，就是 sql_thread 更改为多线程处理。下图中，coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。而 work 线程的个数，就是由参数 slave_parallel_workers 决定的。根据我的经验，把这个值设置为 8~16 之间最好（32 核物理机的情况），毕竟备库还有可能要提供读查询，不能把 CPU 都吃光了。

600|600

但是由于事务的存在，有些任务是不能在 worker 线程并行执行的，否则可能导致不一致。两个最基本的要求就是：

  1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
  2. 同一个事务不能被拆开，必须放到同一个 worker 中。

MySQL 5.5 版本的并行复制策略

MySQL 5.5 版本是不支持并行复制的。但是，在 2012 年的时候，我自己服务的业务出现了严重的主备延迟，原因就是备库只有单线程复制。然后，我就先后写了两个版本的并行策略。

按表分发策略

按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。当然，如果有跨表的事务，还是要把两张表放在一起考虑的。

每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。hash 表的 key 是“库名.表名”，value 是一个数字，表示队列中有多少个事务修改这个表。

在有事务分配给 worker 时，事务里面涉及的表会被加到对应的 hash 表中。worker 执行完成后，这个表会被从 hash 表中去掉。

600|600

假设在图中的情况下，coordinator 从中转日志中读入一个新事务 T，这个事务修改的行涉及到表 t1 和 t3。

现在我们用事务 T 的分配流程，来看一下分配规则。

  1. 由于事务 T 中涉及修改表 t1，而 worker_1 队列中有事务在修改表 t1，事务 T 和队列中的某个事务要修改同一个表的数据，这种情况我们说事务 T 和 worker_1 是冲突的。
  2. 按照这个逻辑，顺序判断事务 T 和每个 worker 队列的冲突关系，会发现事务 T 跟 worker_2 也冲突。
  3. 事务 T 跟多于一个 worker 冲突，coordinator 线程就进入等待。
  4. 每个 worker 继续执行，同时修改 hash_table。假设 hash_table_2 里面涉及到修改表 t3 的事务先执行完成，就会从 hash_table_2 中把 db1.t3 这一项去掉。
  5. 这样 coordinator 会发现跟事务 T 冲突的 worker 只有 worker_1 了，因此就把它分配给 worker_1。
  6. coordinator 继续读下一个中转日志，继续分配事务。

也就是说，每个事务在分发的时候，跟所有 worker 的冲突关系包括以下三种情况：

  1. 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
  2. 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
  3. 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。这个按表分发的方案，在多个表负载均匀的场景里应用效果很好。

但是，如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。

按行分发策略

要解决热点表的并行复制问题，就需要一个按行并行复制的方案。按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。

这时候，我们判断一个事务 T 和 worker 是否冲突，用的就规则就不是“修改同一个表”，而是“修改同一行”。

按行复制和按表复制的数据结构差不多，也是为每个 worker，分配一个 hash 表。只是要实现按行分发，这时候的 key，就必须是“库名+表名+唯一键的值”。

但是，这个“唯一键”只有主键 id 还是不够的，我们还需要考虑下面这种场景，表 t1 中除了主键，还有唯一索引 a：

CREATE TABLE `t1` (
	`id` int(11) NOT NULL,
	`a` int(11) DEFAULT NULL,
	`b` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

假设，接下来我们要在主库执行这两个事务：

session Asession B
update t1 set a = 6 where id =1;
update t1 set a = 1 where id=2;

可以看到，这两个事务要更新的行的主键值不同，但是如果它们被分到不同的 worker，就有可能 session B 的语句先执行。这时候 id=1 的行的 a 的值还是 1，就会报唯一键冲突。

因此，基于行的策略，事务 hash 表中还需要考虑唯一键，即 key 应该是“库名+表名+索引 a 的名字 +a 的值”。

比如，在上面这个例子中，我要在表 t1 上执行 update t1 set a=1 where id=2 语句，在 binlog 里面记录了整行的数据修改前各个字段的值，和修改后各个字段的值。

因此，coordinator 在解析这个语句的 binlog 的时候，这个事务的 hash 表就有三个项:

  1. key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变，出现了两次。
  2. key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表 a=2 的行。
  3. key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表 a=1 的行。

相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。计算资源。你可能也发现了，这两个方案其实都有一些约束条件：

  1. 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说，主库的 binlog 格式必须是 row；
  2. 表必须有主键；
  3. 不能有外键。表上如果有外键，级联更新的行不会记录在 binlog 中，这样冲突检测就不准确。

但，好在这三条约束规则，本来就是 DBA 之前要求业务开发人员必须遵守的线上使用规范，所以这两个并行复制策略在应用上也没有碰到什么麻烦。

对比按表分发和按行分发这两个方案的话，按行分发策略的并行度更高。不过，如果是要操作很多行的大事务的话，按行分发的策略有两个问题：

  1. 耗费内存。比如一个语句要删除 100 万行数据，这时候 hash 表就要记录 100 万个项。
  2. 耗费 CPU。解析 binlog，然后计算 hash 值，对于大事务，这个成本还是很高的。

所以，我在实现这个策略的时候会设置一个阈值，单个事务如果超过设置的行数阈值（比如，如果单个事务更新的行数超过 10 万行），就暂时退化为单线程模式，退化过程的逻辑大概是这样的：

  1. coordinator 暂时先 hold 住这个事务；
  2. 等待所有 worker 都执行完成，变成空队列；
  3. coordinator 直接执行这个事务；
  4. 恢复并行模式。

MySQL 5.6 版本的并行复制策略

官方 MySQL5.6 版本，支持了并行复制，只是支持的粒度是按库并行。理解了上面介绍的按表分发策略和按行分发策略，你就理解了，用于决定分发策略的 hash 表里，key 就是数据库名。

如果你的主库上的表都放在同一个 DB 里面，这个策略就没有效果了；或者如果不同 DB 的热点不同，比如一个是业务逻辑库，一个是系统配置库，那也起不到并行的效果。理论上你可以创建不同的 DB，把相同热度的表均匀分到这些不同的 DB 中，强行使用这个策略。不过据我所知，由于需要特地移动数据，这个策略用得并不多。

MariaDB 的并行复制策略

在第 23 篇文章中，我给你介绍了 redo log 组提交(group commit)优化，而 MariaDB 的并行复制策略利用的就是这个特性：

  1. 能够在同一组里提交的事务，一定不会修改同一行；
  2. 主库上可以并行执行的事务，备库上也一定是可以并行执行的。

在实现上，MariaDB 是这么做的：

  1. 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；
  2. commit_id 直接写到 binlog 里面；
  3. 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
  4. 这一组全部执行完成后，coordinator 再去取下一批。

当时，这个策略出来的时候是相当惊艳的。因为，之前业界的思路都是在“分析 binlog，并拆分到 worker”上。而 MariaDB 的这个策略，目标是“模拟主库的并行模式”。

但是，这个策略有一个问题，它并没有实现“真正的模拟主库并发度”这个目标。在主库上，一组事务在 commit 的时候，下一组事务是同时处于“执行中”状态的。

如图 5 所示，假设了三组事务在主库的执行情况，你可以看到在 trx1、trx2 和 trx3 提交的时候，trx4、trx5 和 trx6 是在执行的。这样，在第一组事务提交完成的时候，下一组事务很快就会进入 commit 状态。

600|600

而按照 MariaDB 的并行复制策略，备库上的执行效果如图 6 所示。

600|600

可以看到，在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行，这样系统的吞吐量就不够。另外，这个方案很容易被大事务拖后腿。

MySQL 5.7 的并行复制策略

官方的 MySQL5.7 版本也提供了类似的功能，由参数 slave- parallel-type 来控制并行复制策略：

  1. 配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；

  2. 配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。不过，MySQL 5.7 这个策略，针对并行度做了优化。

同时处于“执行状态”的所有事务，是不是可以并行？不可以。
但是同时处于 redolog prepare 状态，就表示事务已经通过锁冲突的检验了。

因此，MySQL 5.7 并行复制策略的思想是：

  1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的；
  2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

我在第 23 篇文章，讲 binlog 的组提交的时候，介绍过两个参数：

  1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
  2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数。在 MySQL 5.7 的并行复制策略里，它们可以用来制造更多的“同时处于 prepare 阶段的事务”。这样就增加了备库复制的并行度。

也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在 MySQL 5.7 处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

MySQL 5.7.22 的并行复制策略

在 2018 年 4 月份发布的 MySQL 5.7.22 版本里，MySQL 增加了一个新的并行复制策略，基于 WRITESET 的并行复制。

相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种。

  1. COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
  2. WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。
  3. WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

当然为了唯一标识，这个 hash 值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值。

你可能看出来了，这跟我们前面介绍的基于 MySQL 5.5 版本的按行分发的策略是差不多的。不过，MySQL 官方的这个实现还是有很大的优势：

  1. writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；
  2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；
  3. 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。

因此，MySQL 5.7.22 的并行复制策略在通用性上还是有保证的。当然，对于“表上没主键”和“外键约束”的场景，WRITESET 策略也是没法并行的，也会暂时退化为单线程模型。

课后问题补充

同时处于 prepare 状态的事务，在备库执行时是可以并行复制的，是这个 prepare 就可以生成了改组的 commited Id 吗？

答：进入 prepare 的时候就给这个事务分配 commitid，这个 commitid 就是当前系统最大的一个 commitid

5.7 版本的基于组提交的并行复制。last_commitid 是在什么时候生成的？

事务提交的时候

更详细的 commit-id 的信息：

步骤有下面 5 步
1 redo log prepare write
2 binlog write
3 redo log prepare fsync
4 binlog fsync
5 redo log commit write 1)如果更新通一条记录是有锁的，只能一个事务执行，其他事务等待锁。 2)第 4 步的时候会因为两个参数，等其他没有锁冲突的事务，一起刷盘，此时一起执行的事务拥有相同的 commit_id：binlog_group_commit_sync_delay、binlog_group_commit_sync_no_delay_count

开启并行复制后，事务是按照组来提交的，从库也是根据 commit_id 来回放，如果从库也开启 binlog 的话，那是不是存在主从的 binlog event 写入顺序不一致的情况呢？

是有可能 binlog event 写入顺序不同的。

logical_clock 执行顺序

双 1,配置为 logical_clock,假设有三个事务并发执行也已经执行完成(都处于 prepare 阶段) 1.三个事务把 redo log 从 redo log buffer 写到 fs page cache 中 2.把 binlog_cache flush 到 binlog 文件中,最先进入 flush 队列的为 leader, 其它两个事务为 follower.把组员编号以及组的编号写进 binlog 文件中(三个事务为同一组). 3.三个事务的 redo log 做 fsync,binlog 做 fsync.
4.dump 线程从 binlog 文件里把 binlog event 发送给从库
5.I/O 线程接收到 binlog event,写到 relay log 中
6.sql thread 读取 relay log,判断出这三个事务是处于同一个组, 则把这三个事务的 event 打包发送给三个空闲的 worker 线程(如果有)并执行。

配置为 writeset 的多线程复制流程: 1.三个事务把 redo log 从 redo log buffer 写到 fs page cache 中 2.把 binlog_cache flush 到 binlog 文件中,根据表名、主键和唯一键(如果有)生成 hash 值(writeset), 保存到 hash 表中 3.然后做 redo log 和 binlog 的 fsync

4.dump 线程从 binlog 文件里把 binlog event 发送给从库
5.I/O 线程接收到 binlog event,写到 relay log 中
6.sql thread 读取 relay log。判断这三个事务的 writeset 是否有冲突,如果没有冲突,则视为同组,反之,则视为不同组。如果是同一个组的事务,则把事务分配到不同的 worker 线程去应用 relay log. 关于 writeset 是否存在冲突是备库做的

多线程复制情况下，是怎么计算出 second_behind_master 的值？

这个测不准，后面有其他判断方法

多线程复制下,如果从库宕机了,是不是从库有一个记录表记录那些事务已经应用完成, 恢复的时候,只需要恢复未应用的事务.

是的,备库有记录，就是 show slave status 里面的 Relay_Log_File 和 Relay_Log_Pos 这两个值表示的

反向链接