MySQL主从延迟分三段定位：网络、回放与大事务

  1.  Home
  2. Text  
    3. Share to：

为什么主从延迟要先分成三段看

很多站长第一次遇到从库延迟，都是从一句「从库数据晚了几十秒」开始的。真正的难点不是发现延迟，而是判断延迟发生在链路的哪一段，因为不同段的处理方式完全不同，改错地方会越改越糟。

一条 INSERT 从主库到从库可见，要经过三段：第一段是主库提交并写 binlog，第二段是 binlog 通过网络传到从库并被 IO 线程落成 relay log，第三段是 SQL 线程（或多线程复制下的 worker）回放 relay log。延迟可能卡在三段中的任何一段，而 Seconds_Behind_Master（8.0 之前）或 Replica_SQL_Running_State 给出的信息非常粗糙，经常掩盖真相。

举个例子：从库 IO 线程因为网络抖动落后了 300 秒，但 SQL 线程一直在追，此时 Seconds_Behind_Master 会显示一个不断跳动的数字，你以为是回放慢，其实回放很快，是网络堵了。反过来，如果网络很好、relay log 堆积如山，SQL 线程单线程回放跟不上主库并发，那才是真正的回放瓶颈。

下面给出我在几台生产机上用过的分三段判定方法，全部基于 MySQL 8.0 自带视图，不需要额外装工具。

第一段：确认延迟是「真延迟」还是「假延迟」

先别急着改配置，先排掉三种典型的假延迟。

时钟不同步造成的假延迟。Seconds_Behind_Master 是用从库当前时间和事件里的时间戳相减得到的。如果主从时钟差了 30 秒，就会凭空多出 30 秒延迟。先在两台机器上各跑一次 timedatectl status，看 NTP 是否 synchronized，再看 date +%s.%N 两边差多少。差超过 1 秒就先修时钟，再谈延迟。

监控口径造成的假延迟。有人用 SHOW SLAVE STATUS 里 SQL 线程回放到的 binlog position 和主库 SHOW MASTER STATUS 的 position 对比，算出「差了 200MB 就是延迟」。这是字节差，不是时间差，遇到一小时内全是小事务和全是批量更新，同样的字节差代表的延迟完全不同。判断时间延迟应该用 mysqlbinlog 看最后一个回放事件的时间戳，或者直接看 performance_schema.replication_applier_status_by_worker。

大事务造成的假延迟。一个 UPDATE ... WHERE created_at < '2020-01-01' 改了 200 万行，在从库上作为一个事务回放，回放期间 Seconds_Behind_Master 会一路飙升，回放完瞬间归零。这种不是配置问题，是事务设计问题，改配置无用。

-- 主库：确认当前正在执行的大事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_rows_modified, trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_rows_modified DESC LIMIT 5;

-- 从库：看回放线程状态与最后一个事务的耗时
SELECT WORKER_ID, SERVICE_STATE, LAST_ERROR_MESSAGE,
       APPLYING_TRANSACTION, LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

如果 LAST_ERROR_MESSAGE 非空，说明复制已经断了不是慢，那是另一个问题（多半是唯一键冲突或主从数据不一致），别混在延迟里排。

第二段：判断卡在网络还是卡在回放

这一步的核心是看 relay log 的堆积速度。方法很简单：连续三次采样从库的 Retrieved_Gtid_Set 与 Executed_Gtid_Set，两者之差就是「已收到但还没回放」的事务量；同时看 executed 的增量速度。

-- 每 10 秒执行一次，连看三次
SELECT
  @@hostname AS host,
  (SELECT COUNT(*) FROM performance_schema.replication_applier_status_by_worker
    WHERE SERVICE_STATE = 'ON') AS workers_on,
  VARIABLE_VALUE AS gtid_executed
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Executed_gtid_set';   -- 8.0 用 gtid_executed 变量

SHOW REPLICA STATUS\G

判读规则：

  • 如果 Retrieved 一直在涨、Executed 涨得很慢，说明网络没问题，回放跟不上，去看第三段。
  • 如果 Retrieved 停滞不动而主库 binlog 在涨，说明 IO 线程拉不动。常见原因是网络带宽被打满或主库上 binlog_group_commit_sync_delay 之外还有磁盘压力。在从库上 iftop -n -i eth0 看实时流量，再在主库上确认 SHOW MASTER STATUS 的 file 是否在推进。
  • 如果 Retrieved 和 Executed 都在正常涨，但业务读到的数据还是旧，那可能是应用连接了错误的实例，或者读请求被读写分离中间件发了出去，问题不在复制。

还有一种情况常被忽略：从库磁盘 io 被打满导致 relay log 写入变慢。前面写过用 iostat 看 w_await 和 aqu-sz，这里同样适用——如果 %util 不高但 w_await 超过 20ms，云盘突发额度耗尽的可能性很大，因为 relay log 写入和 binlog 写入都在抢同一个盘。

第三段：回放慢的四个真实原因

原因一：单线程回放遇到高并发主库。MySQL 5.6 之前只有单线程 SQL 线程，5.7 起有多线程复制（MTS），8.0 默认 replica_parallel_workers=4。如果主库峰值有几十个并发写事务，4 个 worker 远远不够。把 worker 数调到和主库 CPU 核数接近是常见做法，但要注意 replica_parallel_type=LOGICAL_CLOCK（8.0.27 起默认）才允许同一组提交内并行，如果还是 DATABASE 模式，只有跨库事务能并行，单库应用几乎等于单线程。

SHOW VARIABLES LIKE 'replica_parallel%';
-- replica_parallel_type = LOGICAL_CLOCK
-- replica_parallel_workers = 8
-- replica_preserve_commit_order = ON（保证从库提交顺序与主库一致）

replica_preserve_commit_order=ON 一定要开。关掉它吞吐会高一点，但会导致从库上出现「后提交的事务先可见」，如果业务依赖自增 id 顺序或有外键逻辑，会出现诡异的数据错乱。

原因二：无主键表的行锁回放。这是延迟最隐蔽的元凶。从库回放 UPDATE/DELETE 时如果没有主键，只能做全表扫描逐行比对，一张 200 万行的表一条 UPDATE 就可能耗时几秒。在主库上这种表感觉不出来，因为主库走的是执行计划；在从库上放大十倍不止。查一遍没有主键的表：

SELECT t.table_schema, t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
  AND c.constraint_name IS NULL;

查到就先给这些表加主键或唯一索引。实在不能改表结构的，至少确认这些表的写入频率不高。

原因三：从库上有额外的写负载。很多站长把备份、统计报表、甚至另一个应用放在从库上。备份跑 mysqldump --single-transaction 时会产生一致性快照，虽然不锁表但会持有历史版本，配合长事务会把 undo 撑大，回放变慢。统计报表的 SELECT 如果读的是刚写入的区域，还会和回放线程抢行锁。检查从库上有没有长查询：

SELECT id, user, host, db, command, time, state,
       LEFT(info, 80) AS sql_head
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

原因四：innodb_flush_log_at_trx_commit 与双写盘。从库回放每个事务也要写 redo 并刷盘。如果从库用的是机械盘或者廉价云盘，innodb_flush_log_at_trx_commit=1 加上 sync_binlog=1，每事务两次 fsync，回放吞吐会被 IO 限死。从库可以适当放宽：innodb_flush_log_at_trx_commit=2、sync_binlog=0（如果从库不承担切换主库的角色）。代价是从库宕机可能丢最后 1 秒的已回放事务，但因为可以从主库重新拉取，风险可控。这一点必须在切换演练前改回来。

半同步与延迟的关系

有个误区值得单独说：开了半同步复制（rpl_semi_sync_source_enabled）之后，主库提交要等至少一个从库 ACK。这会让主库变慢，但不会让从库延迟变大——反而是从库延迟会让主库写变慢。如果观察到主库 QPS 下降同时从库延迟上升，多半是半同步的超时参数设置过短：rpl_semi_sync_source_timeout=10000（10 秒），网络一抖动就退化成异步，退化成异步后延迟开始累积，等网络恢复又切回半同步，但积压的 relay log 还在慢慢追。这种「反复切换」在监控上表现为锯齿状延迟曲线，一看就能认出来。

处置办法是看 SHOW STATUS LIKE 'Rpl_semi_sync_source_status'，如果 no_tx 计数在快速增长，说明频繁退化。要么把 timeout 放宽到 30000，要么干脆只用异步复制 + 应用层容忍。个人站点的从库多数只做备份和读扩展，异步加监控告警比半同步省心得多。

从库延迟告警怎么写才不误报

告警不能只看秒数，因为批量导入时明明正常也会报。我的写法是三个条件同时满足才告警：延迟值超过 60 秒、持续 3 个采样周期、当前没有正在执行的大事务。脚本用 Python 连库查一次，写进监控系统：

#!/usr/bin/env python3
import subprocess, re
def q(sql):
    out = subprocess.run(
        ['mysql','-uroot','-pPASS','-N','-B','-e',sql],
        capture_output=True, text=True, check=True).stdout
    return out.strip().split('\t')

rows = q("SELECT CHANNEL_NAME, SQL_DELAY, WORKER_STATE "
         "FROM performance_schema.replication_applier_status "
         "WHERE CHANNEL_NAME=''")
delay = 0
# 8.0 用 SHOW REPLICA STATUS 的 Seconds_Behind_Source 更直接
st = subprocess.run(['mysql','-uroot','-pPASS','-e','SHOW REPLICA STATUS\\G'],
                    capture_output=True, text=True).stdout
m = re.search(r'Seconds_Behind_Source:\s*(\d+)', st)
if m: delay = int(m.group(1))
big = q("SELECT COUNT(*) FROM information_schema.innodb_trx "
        "WHERE trx_rows_modified > 50000")[0]
print(f"delay={delay} big_trx={big}")
raise SystemExit(1 if (delay > 60 and int(big) == 0) else 0)

把这个脚本接入你已经在用的告警通道（邮件、企业微信、Telegram 都行），每两分钟跑一次。判断条件里带上 big_trx 是关键：没有大事务时持续 60 秒延迟是真问题，有大事务时先等着。

延迟已经几百秒时怎么救

先别 STOP REPLICA。停了之后主库 binlog 会继续增长，如果你没设置过期时间，磁盘会被撑爆。正确顺序是：先确认从库磁盘和 relay log 空间充足，再看是不是某个大事务正在回放（replication_applier_status_by_worker 里的 APPLYING_TRANSACTION 能看到 GTID，用 mysqlbinlog 解出这个事务大小）。

如果是单一大事务，唯一的选择是等；KILL 掉 worker 会让事务回滚，回滚耗时通常比正向执行还长。如果是持续堆积，可以临时把 innodb_flush_log_at_trx_commit 调到 2、把 worker 数加一倍（需要 STOP REPLICA; SET GLOBAL replica_parallel_workers=8; START REPLICA;），追平后再改回来。

还有一种彻底的办法是重建从库：在业务低峰把主库备份恢复成一个新的从库，然后 CHANGE REPLICATION SOURCE TO 指向主库。这比死等快得多，代价是需要额外的磁盘空间和停机。对个人站长来说，如果从库只是备份用途，重建远比重启复制更快。

最后：把延迟纳入日常水位观测

复制延迟和磁盘 IO 一样，是持续性指标。建议每天固定时间采样一次，存成 CSV，一个月后就能看出这台机器的「正常水位」。真出问题时，你手里有基线数据，判断会比「现在延迟 90 秒是不是很严重」这种凭感觉的判断可靠得多。

同时留下一个反例给自己：如果某天从库延迟突然归零并且 GTID 不再增长，不要高兴，先查 SHOW REPLICA STATUS 的 Last_IO_Error，很可能是复制已经断了，而你的监控只看延迟值，所以显示「零延迟」——这是延迟监控最常见的盲区。

Last modification：October 21st, 2026 at 10:26 pm
© 允许规范转载

相关文章

Leave a Comment