MySQL 8.0主从延时问题处理｜ 基于 WRITESET 的并行复制

问题现象

线上 Mysql 集群有台从库 mysql-slave01 在 7 月 22 号开始就偶尔出现同步延迟，持续时间也不长（大概 半小时以内），但从 7 月 25 号开始延迟加剧，持续时间集中在凌晨到白天中午，Lag 最高到 6k 多。如下图，主要出现在 mysql01这个主库同步 (下图黄色线)，如下图。

线上 mysql 版本是 8.0.22，mysql-slave01 这个数据库它是同步4个 mysql 主库。从监控看 nvme3n1这块磁盘 IO util 很高，这块设备是对应复制延迟的数据库 mysql01的数据目录 ，主从同步延迟时间段也大致能对应上。

另外这台数据库还作为主库，他有一个从库，top 如下图。另外负载这块还好，内存使用率88%左右，cpu 使用率在15%左右。

mysql 版本

admin@localhost [(none)] 03:46: >select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql-slave01 重要参数配置，没有开启 Mysql 双 1 配置

sync_binlog = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120

问题分析

经过查资料得知可以开启并行复制来优化主从同步延迟的问题，参考文章面试官：关于 MySQL 主从延时问题，我们来好好沟通下？，本次我是选用 MySQL 8.0 基于 WRITESET 的并行复制方案，进行以下参数调整。

# mysql-slave01 从库上配置
sync_binlog=1 -> sync_binlog=1000
slave_parallel_workers = 0 -> slave_parallel_workers = 8
slave_parallel_type = DATABASE -> slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 0 -> slave_preserve_commit_order = 1

# mysql01 主库上配置
binlog_transaction_dependency_tracking = COMMIT_ORDER -> 
binlog_transaction_dependency_tracking = WRITESET_SESSION

并行复制方案简介

MySQL官方先后提出了多个不同的并行复制方案，具体如下。

1. MySQL 5.6 基于库级别的并行复制方案。
2. MySQL 5.7 基于组提交的并行复制方案。
3. MySQL 8.0 基于 WRITESET 的并行复制方案。

这里不过多介绍上面三种方案，有兴趣的可以看下上面的参考文章，

参数解释

sync_binlog

• 当sync_binlog=1时，MySQL会在每次事务提交后，将Log Buffer中的数据更新到磁盘上，此时MySQL安全性较高，但是IO消耗也较高。
• 当sync_binlog=0时，MySQL会在每次事务提交后将binlog_cache中的数据更新至文件系统缓冲区，但不会进行持久化，而是依赖操作系统来调度数据刷入磁盘。
• 当sync_binlog=N时，MySQL会在每N组事务提交后将数据更新到磁盘中。通过这种方式，可以在一定程度上平衡MySQL的性能和数据的安全性。如果N设置得比较大，可以提高系统的性能，但会降低数据的安全性。

slave_parallel_type 设置从库并行复制的类型。该参数有以下取值：

• DATABASE：基于库级别的并行复制。MySQL 8.0.27 之前的默认值
• LOGICAL_CLOCK：基于组提交的并行复制

slave_parallel_workers 设置 Worker 线程的数量。开启了多线程复制，原来的 SQL 线程将演变为 1 个 Coordinator 线程和多个 Worker 线程。

slave_preserve_commit_order 事务在从库上的提交顺序是否与主库保持一致，建议开启。 需要注意的是，调整这三个参数中的slave_parallel_type 和 slave_preserve_commit_order，提示需要重启复制才能生效。

binlog_transaction_dependency_tracking 指定基于何种方案决定事务的依赖关系。因为我是采用mysql 8 WRITESET 的并行复制方案，这里由原来的COMMIT_ORDER 改成 WRITESET_SESSION

修改 mysql 参数和 my.cnf 配置

-- 在主库上设置 binlog_transaction_dependency_tracking
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET_SESSION';

-- 在从库上设置 slave_parallel_type 和 slave_preserve_commit_order
STOP SLAVE SQL_THREAD FOR CHANNEL 'xxx-mysql01';
STOP SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql01';
STOP SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql02';
STOP SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql03';

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_preserve_commit_order = 1;
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL sync_binlog = 1000;

START SLAVE SQL_THREAD FOR CHANNEL 'xxx-mysql01';
START SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql01';
START SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql02';
START SLAVE SQL_THREAD FOR CHANNEL 'xxxxxx-mysql03';

my.cnf 修改（略）

修改后，mysql-slave01 nvme3n1这块设备 IO 就降下来了。

截止目前，mysql-slave01主从同步也没有发生延迟了，问题解决了(≧ω≦)。