Skip to content

4.1-14 主从同步延迟的成因与解决 ​

所属:04 数据存储 / 4.1 MySQL 分级:B(按 A 类模板展开) 状态:◐ 初稿 关联:4.1-2、4.1-10、4.1-11、4.1-12、4.1-13、4.1-15、4.1-16

  • 题型:排查 / 调优 / 场景

  • 一句话结论:主从延迟不是单一“从库慢”,而是链路分段问题:主库生成 binlog → 网络传输 → 从库 IO 写 relay log → SQL/worker 回放 → 从库资源/锁等待。先定位卡在哪一段(Read_Source_Log_Pos vs Exec_Source_Log_Pos、Retrieved_Gtid_Set vs Executed_Gtid_Set、线程状态),再按大事务/DDL/无主键/并行度/锁/资源/网络/架构分别处理;Seconds_Behind_Source 不能单独信。

  • 分层要点:

    1. 先定义延迟:延迟是“主库已提交的事务,从库多久后可见/可读”。Seconds_Behind_Source 只是估算;更可靠的是 pt-heartbeat、GTID 差集、WAIT_FOR_EXECUTED_GTID_SET、业务探针。延迟要看 P50/P99/最大值,不只看瞬时值。
    2. 链路分段定位:主库 Binlog Dump → 网络 → 从库 Replica IO 写 relay log → Replica SQL/worker 回放。Read_Source_Log_Pos 不前进,多半在主库/网络/IO 线程;Read_Source_Log_Pos 前进但 Exec_Source_Log_Pos 落后,问题在 SQL/worker 回放。
    3. 主库侧原因:写入突增、大事务、大批量 DML、DDL、binlog 量大、组提交/锁竞争,都会让 binlog 生成速度超过从库回放。主库不是“没问题”,它可能是延迟的源头。
    4. 网络侧原因:带宽不足、跨地域 RTT 高、丢包、TLS、单事件过大,都会让 IO 线程拉取慢;Relay_Log_Space 不增、Last_IO_Error、IO 线程状态异常是信号。
    5. 从库回放侧原因:并行度低、大事务只能单 worker、DDL 串行、无主键/无唯一索引导致 ROW 回放扫全表、锁等待/长事务、从库 CPU/IO/BP 不足、备份/报表抢资源。这是最常见的“SQL 线程慢”。
    6. 并行复制调优:replica_parallel_workers 开 worker,LOGICAL_CLOCK 按组提交并行,8.0 WRITESET 按写集冲突跟踪,replica_preserve_commit_order=ON 保持提交顺序;但单事务、DDL、无主键仍是边界,不能指望加 worker 解决一切。
    7. 架构与业务手段:读写分离要把“读己之写”考虑进去(写后读主、粘性会话、WAIT_FOR_EXECUTED_GTID_SET);报表/备份放独立从库;热点读加缓存;写入异步化/分片;延迟从库用于灾备而不是读扩展。
    8. 监控与预案:延迟告警(P99/最大值)、GTID 差集、pt-heartbeat、大事务拦截、DDL 规范、从库资源监控、主从切换预案。延迟不是“调个参数”就结束,要有容量规划和降级方案。
  • 图/流程:

text
复制链路分段:
  主库写入 → binlog → Binlog Dump
    → 网络 → 从库 Replica IO → relay log
    → Replica SQL/worker → 从库数据
  任一段慢,都会表现为“延迟”
text
定位决策:
  延迟高
    ├─ Read_Source_Log_Pos 不前进?
    │    ├─ 主库写入/大事务/DDL → 看主库
    │    ├─ 网络/带宽/IO 线程 → 看 Last_IO_Error、网络
    │    └─ 从库 IO 线程卡住 → 看 relay log/磁盘
    └─ Read 前进、Exec 落后?
         ├─ 单线程/并行度低 → 加 worker/LOGICAL_CLOCK
         ├─ 大事务/DDL → 拆批/低峰/Online DDL
         ├─ 无主键/无索引 → 补主键/调 ROW 查找算法
         ├─ 锁等待/长事务 → 查 INNODB_TRX/data_lock_waits
         └─ 从库资源不足 → 扩容/分流/错峰
text
大事务/DDL 时间线:
  主库:一个大事务提交 → 一个巨大 binlog 事件
  从库:一个 worker 从头到尾回放 → 其他事务等待
  解决:拆小事务、控制批量、低峰 DDL、gh-ost/Online DDL
text
并行复制:
  主库组提交:T1,T2,T3 同组 → 可并行
  从库:worker1=T1, worker2=T2, worker3=T3
  replica_preserve_commit_order=ON → 按原顺序提交
  单事务/DDL/无主键 → 仍可能单 worker 瓶颈
text
读写分离的“读己之写”:
  用户写主库 → 立即读从库 → 从库还没回放 → 读不到
  解决:写后读主、粘性会话、WAIT_FOR_EXECUTED_GTID_SET、缓存
  • 版本/源码锚点:

    • MySQL 5.6:GTID、并行复制雏形;5.7:LOGICAL_CLOCK 并行复制、AFTER_SYNC 半同步、多源复制。
    • MySQL 8.0:WRITESET 依赖跟踪、WAIT_FOR_EXECUTED_GTID_SET、CHANGE REPLICATION SOURCE TO/SHOW REPLICA STATUS 新术语、replica_parallel_workers、replica_preserve_commit_order。
    • 关键参数:replica_parallel_workers、replica_parallel_type=LOGICAL_CLOCK、replica_preserve_commit_order=ON、binlog_transaction_dependency_tracking=WRITESET、transaction_write_set_extraction=XXHASH64、slave_rows_search_algorithms、rpl_semi_sync_source_timeout。
    • 源码/实现:sql/rpl_replica.cc(IO/SQL 线程)、sql/rpl_parallel.cc(并行回放)、sql/log_event.cc(事件应用)、sql/rpl_gtid.cc(GTID)、storage/innobase/row/row0sel.cc(ROW 查找/回放)。
  • 延迟度量:

方法原理优点局限
Seconds_Behind_Sourcerelay log 事件时间戳估算简单易失真、空闲/大事务/时钟影响
pt-heartbeat主库写心跳,从库读时间差更准确、可告警需部署工具/表
GTID 差集Retrieved_Gtid_Set - Executed_Gtid_Set精确到事务需理解 GTID
WAIT_FOR_EXECUTED_GTID_SET等待指定 GTID 在从库执行业务级读己之写8.0、有等待成本
业务探针写入标记再读从库贴近业务需埋点
监控指标Seconds_Behind_Source、Relay_Log_Space趋势/告警需组合判断
  • 链路/状态字段:
字段/线程位置看什么判断
Binlog Dump主库发送是否阻塞主库/网络
Replica IO从库拉取 binlog网络/主库
Replica SQL/worker从库回放 relay log回放瓶颈
Read_Source_Log_Pos从库IO 线程读到的位点不前进 → 上游/网络
Exec_Source_Log_Pos从库SQL 线程执行的位点落后 → 回放
Relay_Log_Space从库relay log 堆积大 → SQL 慢
Retrieved_Gtid_Set从库已拉取 GTID与 Executed 比较
Executed_Gtid_Set从库已执行 GTID延迟精确差集
Last_IO_Error从库IO 线程错误网络/权限/主库
Last_SQL_Error从库SQL 线程错误数据冲突/约束
  • 原因 → 信号 → 处理:
原因信号处理
主库写入突增主库 QPS/binlog 量上升,IO 线程追不上限流、分片、异步化、扩容
大事务一个事务长时间回放,Exec 停滞拆小事务、控制批量、避免一次性大更新
DDLDDL 串行、阻塞后续低峰、Online DDL、gh-ost/pt-osc
无主键/无唯一索引ROW 回放扫全表、CPU 高补主键/唯一索引、slave_rows_search_algorithms
并行度低SQL 线程单线程、worker 空闲开 LOGICAL_CLOCK、加 worker
从库锁等待INNODB_TRX、data_lock_waits查长事务、优化 SQL、减少冲突
从库资源不足CPU/IO/BP 打满扩容、独立从库、错峰
从库备份/报表备份/查询期间延迟升高独立备份/报表从库、限流
网络IO 线程慢、Last_IO_Error带宽、专线、压缩、就近部署
复制过滤过滤规则复杂/漏数据谨慎使用,优先分库/分实例
半同步退化Rpl_semi_sync_source_status=OFF查从库/网络,调超时/数量
  • 并行复制参数:
参数作用建议/注意
replica_parallel_workersworker 数量按 CPU/IO/延迟调,不是越大越好
replica_parallel_typeLOGICAL_CLOCK/DATABASE8.0 推荐 LOGICAL_CLOCK
replica_preserve_commit_order保持提交顺序建议 ON,避免可见性/约束问题
binlog_transaction_dependency_trackingCOMMIT_ORDER/WRITESET/WRITESET_SESSIONWRITESET 提升非冲突事务并行
transaction_write_set_extraction写集提取XXHASH64
slave_rows_search_algorithmsROW 回放查找无主键时 HASH_SCAN 有帮助
log_replica_updates从库写 binlog级联复制需要
  • 架构/业务手段:
手段解决什么代价/注意
写后读主读己之写主库读压力
粘性会话同一用户读一致性负载不均
WAIT_FOR_EXECUTED_GTID_SET等待从库追上增加读延迟
半同步减少丢失窗口主库 RT 增加
独立报表/备份从库资源隔离成本
延迟从库误操作恢复不能用于读扩展
缓存热点读一致性/穿透
分库分表降低单实例写入/binlog跨分片复杂度
异步化削峰业务改造
  • 监控指标:
指标作用告警建议
Seconds_Behind_Source粗延迟持续超阈值
pt-heartbeat 延迟精确延迟P99/最大
GTID 差集事务级落后持续增长
Relay_Log_Spacerelay log 堆积快速增长
Replica_IO/SQL_Running线程状态非 Yes
Last_IO_Error/Last_SQL_Error复制错误立即告警
Threads_running从库负载高水位
磁盘/IO/CPU/BP资源容量预警
主库 binlog 生成速率源头压力突增
  • 深挖点(面试官常追的 6 个):

    1. Seconds_Behind_Source 不可靠:它用 relay log 事件时间戳估算,SQL 线程空闲时可能是 0,大事务/时钟偏差/网络卡住时会跳变;用 pt-heartbeat、GTID 差集、WAIT_FOR_EXECUTED_GTID_SET 交叉验证。
    2. 大事务是延迟的头号杀手:一个事务只能由一个 worker 回放,无法拆分;主库一个大批量更新可能让从库回放几分钟甚至几十分钟。解决是拆批、控制每批行数、避免一次性大 DDL/DML。
    3. 无主键/无唯一索引会让 ROW 回放退化:ROW 事件按行定位,没有主键/唯一索引时可能退化为全表扫描;slave_rows_search_algorithms 的 HASH_SCAN 能缓解,但补主键才是根治。
    4. 并行复制有边界:LOGICAL_CLOCK 按组提交并行,WRITESET 按写集冲突跟踪,但单事务、DDL、无主键仍是单点;replica_preserve_commit_order=ON 保证顺序,可能限制并行效果。
    5. 从库不是“只读就没事”:备份、报表、大查询、锁等待都会抢 CPU/IO/BP;读写分离下从库延迟会直接变成业务读不一致。独立从库、错峰、限流是常见解法。
    6. 读写分离的“读己之写”是业务问题:写主库后立刻读从库,可能读不到;写后读主、粘性会话、WAIT_FOR_EXECUTED_GTID_SET、缓存都能缓解,但各有成本。延迟治理要落到业务一致性,而不是只看监控数字。
  • 追问链:

    1. 主从延迟怎么定义? → 主库已提交事务到从库可见/可读的时间差;看 P50/P99/最大值。
    2. Seconds_Behind_Source 可靠吗? → 不可靠,受空闲、大事务、时钟、网络影响;要配合 pt-heartbeat/GTID 差集。
    3. 怎么准确测延迟? → pt-heartbeat、WAIT_FOR_EXECUTED_GTID_SET、GTID 差集、业务探针。
    4. 延迟卡在哪一段? → 看 Read_Source_Log_Pos 是否前进、Exec_Source_Log_Pos 是否落后、IO/SQL 线程状态。
    5. Read_Source_Log_Pos 和 Exec_Source_Log_Pos 区别? → 前者 IO 线程读到的主库位点,后者 SQL 线程已执行的位点。
    6. Retrieved_Gtid_Set 和 Executed_Gtid_Set 区别? → 已拉取 vs 已执行;差集就是待回放事务。
    7. IO 线程慢还是 SQL 线程慢? → Read 不前进是 IO/网络/主库;Read 前进但 Exec 落后是 SQL/worker。
    8. 主库侧原因? → 写入突增、大事务、DDL、binlog 量大、锁竞争。
    9. 大事务为什么延迟? → 单事务只能单 worker 回放;拆批/控制批量。
    10. DDL 为什么延迟? → DDL 串行、可能阻塞后续;低峰/Online DDL/gh-ost。
    11. 无主键为什么延迟? → ROW 回放可能全表扫描;补主键/唯一索引、调 slave_rows_search_algorithms。
    12. 无索引为什么延迟? → 同上,ROW 查找/锁范围大;补索引。
    13. 并行复制怎么开? → replica_parallel_workers + LOGICAL_CLOCK + replica_preserve_commit_order=ON。
    14. LOGICAL_CLOCK 原理? → 基于主库组提交逻辑时钟,同组事务可并行回放。
    15. WRITESET 是什么? → 8.0 基于事务写集冲突的依赖跟踪,提升非冲突事务并行度。
    16. worker 数量怎么定? → 按 CPU/IO/延迟压测调;太多会增加冲突/上下文切换。
    17. preserve_commit_order 作用? → 保持从库提交顺序与主库一致;建议 ON。
    18. 从库资源不足怎么办? → 扩容、独立从库、错峰备份/报表、优化大查询。
    19. 从库锁等待怎么查? → INNODB_TRX、data_lock_waits、sys.innodb_lock_waits。
    20. 从库备份/报表影响? → 抢 IO/CPU/BP;独立从库、限流、错峰。
    21. 网络影响? → 带宽/RTT/丢包/TLS;专线、压缩、就近部署。
    22. binlog 太大? → 大事务/批量 DML;拆批、binlog_row_image、压缩。
    23. 复制过滤能用吗? → 谨慎;容易漏数据/切换困难,优先分库/分实例。
    24. 半同步影响延迟吗? → 影响主库 RT,不直接改善从库回放延迟;超时退化。
    25. 读写分离延迟怎么处理? → 写后读主、粘性会话、GTID wait、缓存、半同步。
    26. 读己之写怎么保证? → 会话粘主、WAIT_FOR_EXECUTED_GTID_SET、业务补偿。
    27. WAIT_FOR_EXECUTED_GTID_SET 怎么用? → 8.0 在从库等待指定 GTID 执行完;注意超时和读延迟。
    28. 延迟告警怎么设? → 秒级/事务级、P99/最大、持续增长;结合业务 SLO。
    29. 大事务怎么拦截? → SQL 审核、批量大小限制、binlog_rows_query_log_events/监控、拆批。
    30. 拆批怎么做? → 按主键范围/时间分页、每批固定行数、批间休眠、避免长事务。
    31. 延迟导致主从切换? → 选 GTID 最全/延迟最小的从库;fencing 旧主;校验数据。
    32. 延迟会导致数据不一致吗? → 延迟本身不是不一致,但切换/读从库可能读到旧数据;errant transaction 才是真不一致。
    33. pt-heartbeat 原理? → 主库定时写心跳,从库读并计算时间差。
    34. pt-table-checksum 用途? → 校验主从数据一致性,不是测延迟。
    35. 多线程消费? → MySQL 是并行回放,不是业务多线程消费;业务可用 MQ 削峰。
    36. 分库分表有帮助吗? → 降低单实例写入/binlog/回放压力,但跨分片复杂度上升。
    37. 缓存有帮助吗? → 降低读从库压力,但不解决写复制延迟。
    38. 异步化有帮助吗? → 削峰、降低主库写入突增,但业务要改造。
    39. 云 RDS 怎么排查? → 看只读实例延迟指标、复制状态、网络、规格;用厂商诊断工具。
    40. 预案怎么写? → 延迟阈值、告警、降级(读主/缓存)、扩容、切换、复盘。
  • 生产案例/取舍(示例,替换成你的项目):

    • 批量更新百万行导致从库延迟 30 分钟;拆成每批 1000 行 + 批间休眠后恢复。
    • DDL 在从库串行执行导致延迟;低峰 + gh-ost/Online DDL。
    • 无主键表 ROW 回放慢;补主键后延迟下降。
    • 并行度不足,开 LOGICAL_CLOCK + 4 worker,延迟下降。
    • 从库跑报表导致延迟;独立报表从库 + 限流。
    • 跨地域网络 RTT 高,IO 线程慢;专线/就近部署/压缩。
    • 从库锁等待导致 SQL 线程卡住;kill 长事务、优化 SQL。
    • 读写分离下用户写完读不到;写后读主 + WAIT_FOR_EXECUTED_GTID_SET。
    • 半同步从库慢导致主库 RT 升高;调超时/降 wait_for_replica_count/换从库。
    • 延迟告警触发降级,读流量切主/缓存,避免业务雪崩。
    • 取舍:加 worker 换并行,代价是冲突/顺序;拆批换低延迟,代价是业务改造;独立从库换隔离,代价是成本;读主换一致性,代价是主库压力。
  • 易错点:

    • 只看 Seconds_Behind_Source 判断延迟。
    • 认为延迟一定是 SQL 线程慢。
    • 认为加 worker 能解决所有延迟。
    • 认为大事务可以并行回放。
    • 认为 DDL 可以并行。
    • 认为无主键不影响 ROW 回放。
    • 忽略从库锁等待/长事务。
    • 忽略从库备份/报表抢资源。
    • 忽略网络/带宽/RTT。
    • 忽略主库写入突增。
    • 认为半同步能降低从库回放延迟。
    • 认为读写分离没有一致性风险。
    • 认为 WAIT_FOR_EXECUTED_GTID_SET 没有成本。
    • 认为延迟只影响报表。
    • 认为从库只读就没有写入/锁。
    • 忽略 relay log/binlog 膨胀。
    • 认为复制过滤可以随便用。
    • 忽略版本/参数差异。
    • 不做延迟告警/预案。
    • 认为扩容能永久解决延迟。
  • 30 秒口述稿:

主从延迟要分段看:主库 binlog 生成、网络、从库 IO 写 relay log、SQL/worker 回放。先看 Read_Source_Log_Pos 和 Exec_Source_Log_Pos:前者不前进是主库/网络/IO,后者落后是回放。常见原因是主库写入突增、大事务、DDL、无主键、并行度低、从库锁/资源/备份、网络。解决靠拆批、低峰 DDL、补主键、开 LOGICAL_CLOCK/WRITESET、独立从库、限流和读写分离一致性方案;Seconds_Behind_Source 不能单独信,要用 pt-heartbeat/GTID 差集验证。

  • 3 分钟展开顺序:结论(链路分段)→ 延迟度量(Seconds_Behind_Source/pt-heartbeat/GTID)→ 定位(Read/Exec/线程)→ 主库/网络/从库原因 → 并行复制参数与边界 → 大事务/DDL/无主键 → 架构手段(读写分离/独立从库/缓存/分片)→ 监控/预案 → 生产案例与取舍。