将 MySQL 8 主从复制延迟优化到极致

目录

一、网络资源不足引起的复制延迟

1. 执行监控确认延迟原因

2. 估算所需带宽

（1）基本公式

（2）实际测量方法

二、大事务或大查询引起的复制延迟

1. 主库大事务

2. 从库大查询

3. 估算所需 I/O 能力

（1）基本公式

（2）实际估算

三、高并发引起的复制延迟

1. 并发量估算

2. 数据库优化

（1）主库配置

（2）从库配置

---

        本篇文章详细分析了一个实际应用中对 MySQL 8 主从复制延迟进行优化的案例，MySQL 版本为 8.0.22，一主一从两个实例开启 GTID 并进行普通的异步复制，主库读写从库只读，主、从实例的基本配置如下：

binlog_format = ROW
transaction_isolation = READ-COMMITTED
bulk_insert_buffer_size = 1G
innodb_adaptive_hash_index = 0
log_slave_updates = 1
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
max_allowed_packet = 1G
explicit_defaults_for_timestamp = 0
log_timestamps = SYSTEM
binlog_expire_logs_seconds = 259200
innodb_buffer_pool_size = 80G
max_connections = 1000
default-time-zone = '+8:00'
skip-name-resolve 
innodb_print_all_deadlocks = 1
log_output = 'table'
slow_query_log = 1
long_query_time = 1
gtid-mode = on
enforce_gtid_consistency = true
local_infile = 1
skip_symbolic_links

        下面从可能引起复制延迟的三个方面进行分析。

一、网络资源不足引起的复制延迟

        我们可以将复制的时间分为两部分：一是事件从主库到从库的传输时间，二是事件在从库上的重放执行时间。事件在主库上记录 binlog 后到传递到从库的时间理论上非常快，因为它只取决于网络速度。MySQL binlog 的 dump 线程不是通过轮询方式请求事件，而是由主库来通知从库新的事件，因为前者低效且缓慢。从主库读取一个 binlog event 是一个阻塞型网络调用，当主库记录事件后，马上就开始发送。因此可以说，只要复制的 I/O 线程被唤醒并且能够通过网络传输数据，事件就会很快到达从库。但是，如果网络很慢或者 binlog event 很大，记录 binlog 和在从库上执行的延迟可能会非常明显。

1. 执行监控确认延迟原因

        监控脚本文件 get_Gtid_totable.sh 内容如下：

#!/bin/bashsource ~/.bash_profile# 获取主库 binlog 位点
a=`mysql -uroot -p123456 -h10.10.10.1 -P3306 -e "show master status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | sed 's/,//' | awk -F: '{print $2}' | awk -F"-" '{print $2}'`# 获取从库接收和执行 binlog 位点
b=`mysql -uroot -p123456 -h10.10.10.2 -e "show slave status\G" 2>/dev/null | egrep 'f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7' | egrep -v "Master_UUID" | sed -e '1s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-//g' | sed -e '2s/f8e0355d-9d6e-11ee-8dcd-e43d1a47c7b7:1-//g'`# 获取从库延迟秒数
c=`mysql -uroot -p123456 -h10.10.10.2 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | sed 's/Seconds_Behind_Master: //g'`
Seconds_Behind_Master=`echo $c`master_Executed_Gtid=`echo $a`
slave_Retrieved_Gtid=`echo $b | awk '{print $1}' | sed 's/.$//'`
slave_Executed_Gtid=`echo $b | awk '{print $2}' | sed 's/.$//'`# 入库
mysql -h10.10.10.3 -P3306 -uroot -p123456 -e "
insert into test.t_lag (master_Executed_Gtid, slave_Retrieved_Gtid, slave_Executed_Gtid,Seconds_Behind_Master)
values ("$master_Executed_Gtid","$slave_Retrieved_Gtid","$slave_Executed_Gtid","$Seconds_Behind_Master");"

        只监控业务高峰期，用 cron 调度执行：

* 0-3,19-23 * * * /home/mysql/get_Gtid_totable.sh

        test.t_lag 建表语句如下：

use test;
create table t_lag (ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,master_Executed_Gtid bigint(20) DEFAULT NULL,slave_Retrieved_Gtid bigint(20) DEFAULT NULL,slave_Executed_Gtid bigint(20) DEFAULT NULL,rlag int(11) DEFAULT (greatest((master_Executed_Gtid - slave_Retrieved_Gtid),0)),elag int(11) DEFAULT (greatest((slave_Retrieved_Gtid - slave_Executed_Gtid),0)),Seconds_Behind_Master int(11) DEFAULT NULL
);

        查询监控结果如下：

mysql -h172.18.16.156 -P3306 -uroot -p123456 -Dtest -e "
select ts \"时间\",rlag \"接收binlog落后事务数\",elag \"执行binlog落后事务数\",Seconds_Behind_Master \"延迟秒数\"from t_lag where date(ts)='2024-04-25'order by Seconds_Behind_Master desc limit 10;" 2>/dev/null
+---------------------+---------------------------+----------------------------+--------------+
| 时间                 | 接收binlog落后事务数        | 执行binlog落后事务数         | 延迟秒数       |
+---------------------+---------------------------+----------------------------+--------------+
| 2024-04-25 22:43:01 |                    144230 |                        112 |          119 |
| 2024-04-25 22:46:02 |                    137649 |                        981 |          118 |
| 2024-04-25 22:25:02 |                    146937 |                        186 |          115 |
| 2024-04-25 22:44:01 |                    135479 |                        898 |          114 |
| 2024-04-25 22:24:01 |                    140064 |                        258 |          112 |
| 2024-04-25 22:49:01 |                    146843 |                        228 |          111 |
| 2024-04-25 22:53:02 |                    146033 |                       2032 |          110 |
| 2024-04-25 22:26:02 |                    140634 |                          9 |          110 |
| 2024-04-25 22:23:01 |                    137269 |                        747 |          109 |
| 2024-04-25 22:40:02 |                    127012 |                        350 |          108 |
+---------------------+---------------------------+----------------------------+--------------+

        从查询结果可以看到，接收 binlog 落后事务数很大，执行 binlog 落后事务数很小，说明引起复制延迟的原因是主库 binlog 无法及时传输到从库。经过排查发生复制延迟时主从之间的网络带宽被打满，这就是瓶颈所在。

2. 估算所需带宽

（1）基本公式

        确认带宽不足后，下个要解决的问题是量化评估所需带宽。从 MySQL 主从复制的原理可知，网络上传输的就是 binlog 文件，因此可以基于 binlog 大小计算基本复制流量：

所需带宽 ≈ (每日 binlog 生成量 * 复制因子) / 86400 秒

        其中“复制因子”指的是需要接收复制数据的从库实例的（Slave）数量。

（2）实际测量方法

        确认 binlog 所在目录和文件大小：

mysql> show variables like 'innodb_log_group_home_dir';
+---------------------------+------------------+
| Variable_name             | Value            |
+---------------------------+------------------+
| innodb_log_group_home_dir | /data/3306/dblog |
+---------------------------+------------------+
1 row in set (0.01 sec)mysql> show variables like 'innodb_log_file_size';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| innodb_log_file_size | 1073741824 |
+----------------------+------------+
1 row in set (0.01 sec)

        计算一天的 binlog 大小，例如 4 月 21 日的 binlog 文件总大小：

ls -l /data/18251/dblog/* | grep "Apr 21" | wc -l
108

        每个文件大小 1GB，一天生成 108 个文件，总大小为 108GB。本案例是一主一从，因此：

平均每秒数据量 ≈ 108 * 1024 MB / 86400 秒 ≈ 1.28 MB/秒

        转换为带宽（Mbps）：

1 Byte = 8 bits
1.28 MB/秒 = 1.28 * 8 = 10.24 Mbps

        因此，理论最小带宽需求为 10.24 Mbps（匀速传输）。MySQL 的写入通常有波动（如业务高峰时 binlog 生成更快），建议预留 2-5 倍 的带宽，因此实际带宽需求（考虑峰值）推荐为：

10.24 Mbps * 3 ≈ 30 Mbps

        在本例的场景中，一共有 8 对主从复制，理论上需要 240 Mbps 的带宽，但实际上每个实例的业务量并不平均。上面的计算是基于业务量最大的一个实例（也正是日常有复制延迟的实例），其它的实例所产生的 binlog 小得多。经过不断尝试，当带宽增加到 100 Mbps 时，复制延迟的次数和延迟秒数都少了很多。

二、大事务或大查询引起的复制延迟

        如果查询需要执行很长时间而网络很快，通常可以认为重放时间占据了更多的复制时间开销，其中主库大事务或从库大查询引起的复制延迟是常见的情况。

1. 主库大事务

        主库上执行的大事务会通过 binlog 传输到从库重放。在从库上执行 show slave status\G 命令，如果在输出中看到：

• Retrieved_Gtid_Set 不断增长
• Executed_Gtid_Set 长时间不增长
• Seconds_Behind_Master 持续增加
• Slave_SQL_Running_State 长时间为 System lock

        可以依此判断出从库仍在不断接收主库发送的 binlog，但因为正在重放一个大事务，在这个大事务提交前，从库的 GTID 不变，所以造成了复制延迟。

2. 从库大查询

        为了保证主从数据一致性，通常将从库设置为只读（read_only、super_read_only），这意味着不会有在从库发起的事务，但从库上可以执行查询。如果在从库上执行一个大查询，可能将 I/O 资源占满，产生 I/O 等待，从而产生复制延迟。执行类似 iostat -dmx 2 100 的操作系统命令，其输出中的 %util 列接近或达到 100% 就是这种情况。

3. 估算所需 I/O 能力

        无论是主库大事务，还是从库大查询，都会产生大量的磁盘 I/O，以至于 binlog 因为 I/O 等待无法及时重放，使得复制延迟。如果只是偶发情况，只要耐心等待大事务或大查询执行完成，之后通常从库的复制能尽快追上主库，不用人为干涉。如果是日常情况，就需要考虑增加 I/O 子系统的能力了。

（1）基本公式

        以下是估算 I/O 能力的基本公式：

从库所需 IOPS ≈ 主库写入 IOPS * (1 + 安全系数)

        通常安全系数建议为 0.2-0.5（20%-50%）。

（2）实际估算

        还是从 binlog 入手进行估算，前面已经得到：

平均每秒数据量 ≈ 108 * 1024 MB / 86400 秒 ≈ 1.28 MB/秒

        假设平均 I/O 大小为 8KB，这是一个经验值。

1. InnoDB 的默认页（Page）大小为 16KB，这是磁盘 I/O 的最小单位。但实际 I/O 操作可能涉及：部分页写入（如只修改了页的一部分，但通常仍以页为单位写入）；随机 I/O（如索引查询可能只读取部分数据）。因此，实际平均 I/O 大小通常小于 16KB，8KB 是一个常见的折中值。
2. 文件系统（如 ext4、XFS）和磁盘控制器可能会合并 I/O 请求。但随机读写场景下，I/O 可能无法完全合并，因此 8KB 能更好地反映实际负载。
3. MySQL 的 binlog 和 InnoDB 的 redo log（事务日志）通常是顺序写入，但每次写入的大小取决于事务量：小事务（如单行更新）可能产生 几百字节~几KB 的日志；大事务（如批量插入）可能触发更大的 I/O（16KB 或更多）。8KB 是对这种混合负载的合理估算。

主库写入 IOPS = 1.28MB/s / 8KB ≈ 164 IOPS
从库所需IOPS ≈ 164 * 1.3 ≈ 213 IOPS

        另外，如果从库还处理读请求，需要额外 I/O 能力。通常 HDD 只能提供 100-200 IOPS，而 SSD/NVMe 通常可提供数千至数十万 IOPS，因此使用 SSD/NVMe 存储。本案例中存储使用的是 SSD，除了偶尔的大事务或大查询会把 I/O 跑满，其它大多数情况下，I/O 能力都是充足的。

三、高并发引起的复制延迟

        在带宽没跑满，I/O 使用率很低的情况下，实际还有长时间复制延迟，其原因就是主库高并发的小事务。解决这个问题的思路就是采取各种手段提高 MySQL 主从两端的并行度，到这就该进行数据库层面的优化了。

1. 并发量估算

        因为 binlog 中只会记录与行更新有关的操作，所以与复制相关的并发量应该基于主库的每秒事务数，即 TPS 进行估算。具体的计算方法有两种，一是定期（如每分钟）执行状态采集，例如：

# 每 10 秒采样一次，显示相对值（差值）
mysqladmin -uroot -p123456 -h10.10.10.1 extended-status -r -i 10 | egrep 'Com_insert|Com_update|Com_delete'

        然后按下面的公式计算一次采样的 TPS：

TPS = (Com_delete + Com_delete_multi + Com_insert + Com_insert_select + Com_update + Com_update_multi) / 10

        这里有几点需要注意：

• 在业务峰值时间段采样。
• 忽略第一个采样值，因为第一次没有相对值。
• 因为采集的就是差值，所以直接相加返回的状态值。
• 多次采样计算 TPS 平均值。

        第二种计算方法更直接：基于 GTID 数计算。例如利用前面 test.t_lag 表的 master_Executed_Gtid 字段，执行下面的查询即可得出 TPS：

select ts, master_executed_gtid_diff / time_diff_sec as tpsfrom (select ts, master_executed_gtid,master_executed_gtid - lag(master_executed_gtid) over (order by ts) as master_executed_gtid_diff,timestampdiff(second, lag(ts) over (order by ts), ts) as time_diff_secfrom test.t_lag) as diffswhere master_executed_gtid_diff is not null and time_diff_sec > 0order by ts;

2. 数据库优化

（1）主库配置

# 二进制日志组提交优化（极限值为 5000、100）
binlog_group_commit_sync_delay = 2000         # 2 毫秒延迟（最大建议值）
binlog_group_commit_sync_no_delay_count = 50  # 50 个事务强制提交

        对于超高频系统（>10万TPS），可调整为：
binlog_group_commit_sync_delay = 1000    # 1ms
binlog_group_commit_sync_no_delay_count = 100

        对于普通高并发（1-5万TPS）保持当前值即可。

# 配套参数优化
sync_binlog = 1000                            # 每 1000 次写入同步一次
innodb_flush_log_at_trx_commit = 2            # 每秒刷新日志（牺牲部分持久性）
binlog_order_commits = OFF                    # 提高并行度

        风险提示：

• innodb_flush_log_at_trx_commit = 2 可能导致最多 1 秒的数据丢失。我们是互联网业务，没有那么强的数据安全性要求。如果是金融级应用必须设为 1。
• sync_binlog = 1000 在崩溃时可能丢失最多 999 个未同步的 binlog 事件。折中方案可以设置为 100。

# 事务日志优化
innodb_log_file_size = 1G                     # 大事务日志文件
innodb_log_buffer_size = 64M                  # 大日志缓冲区# 刷盘方法优化
innodb_flush_method = O_DIRECT                # InnoDB 直接写盘，避免双重缓存

        最佳实践：

• 对于 Linux + SSD/NVMe 环境保持 O_DIRECT。
• Windows 环境使用 async_unbuffered
• 传统硬盘可测试 O_DSYNC

        innodb_flush_method 为只读参数，需要重启 MySQL 实例才能生效。

# 基于 WRITESET 的复制优化
binlog_transaction_dependency_tracking = WRITESET        # 基于事务修改的数据行判断依赖
transaction_write_set_extraction = XXHASH64              # 使用 64 位 XXHASH 算法

        当这两个参数配合使用时：

• 主库为每个事务计算其修改行的 XXHASH64 哈希值。
• 将这些哈希值（称为 writeset）记录到 binlog。
• 从库通过比较不同事务的 writeset 判断是否可以并行执行。

（2）从库配置

slave_pending_jobs_size_max = 2G          # 大内存缓冲
slave_compressed_protocol = ON            # 启用压缩传输
sync_binlog = 0                           # 刷新 binlog 完全依赖操作系统
innodb_flush_log_at_trx_commit = 0        # 刷新日志完全依赖操作系统
innodb_flush_method=O_DIRECT              # InnoDB 直接写盘，避免双重缓存
log_slave_updates=0                       # 关闭从库更新 binlog

        sync_binlog 与 innodb_flush_log_at_trx_commit 双 0 组合使用的效果：

• 最高性能模式，最低安全性。
• 完全禁用持久性保证，崩溃可能导致数据丢失。
• 仅适用于可容忍数据丢失的非关键从库。
• 比安全配置（sync_binlog、innodb_flush_log_at_trx_commit 双 1）吞吐量高 5-10 倍。

slave_parallel_type = LOGICAL_CLOCK       # 从库并行复制的依赖检测方式
slave_parallel_workers = 32               # 32 线程并行复制
slave_preserve_commit_order = 0           # 不保持事务的原始提交顺序

        建议调整：

• slave_parallel_type = LOGICAL_CLOCK 使用主库 binlog 中的逻辑时间戳判断事务依赖关系，需要主库启用 binlog_group_commit_sync_delay 才能产生足够的并行机会。相比 DATABASE 并行度更高。
• 对于高并发从库，slave_parallel_workers 设为 CPU 核心数的 50-75%。
• slave_preserve_commit_order 设置为 0 延迟更低，并行度更高，但可能暂时性与主库数据不一致。关键业务设为 1 保持顺序；非关键业务/分析从库可设为 0 以提高性能；级联复制的中间从库可设为 0，末端从库设为 1。

# 从库只读
read_only
super_read_only

        这里每个配置参数只做了简明扼要的说明，因为要讲透需要很多知识点和篇幅，有兴趣的请自行脑补。本案例采用以上主从配置，解决了高负载 MySQL 实例的复制延迟问题。

        注意：这个案例中的从库是一个用于大数据分析的从库，数据安全性和一致性要求并不高，所以以上配置是以消除复制延迟为最高优先级考虑的，牺牲了一定的数据一致性和数据安全性，是否适用于特定应用场景还要具体问题具体分析。