Appearance
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_PosvsExec_Source_Log_Pos、Retrieved_Gtid_SetvsExecuted_Gtid_Set、线程状态),再按大事务/DDL/无主键/并行度/锁/资源/网络/架构分别处理;Seconds_Behind_Source不能单独信。分层要点:
- 先定义延迟:延迟是“主库已提交的事务,从库多久后可见/可读”。
Seconds_Behind_Source只是估算;更可靠的是pt-heartbeat、GTID 差集、WAIT_FOR_EXECUTED_GTID_SET、业务探针。延迟要看 P50/P99/最大值,不只看瞬时值。 - 链路分段定位:主库
Binlog Dump→ 网络 → 从库Replica IO写 relay log →Replica SQL/worker 回放。Read_Source_Log_Pos不前进,多半在主库/网络/IO 线程;Read_Source_Log_Pos前进但Exec_Source_Log_Pos落后,问题在 SQL/worker 回放。 - 主库侧原因:写入突增、大事务、大批量 DML、DDL、binlog 量大、组提交/锁竞争,都会让 binlog 生成速度超过从库回放。主库不是“没问题”,它可能是延迟的源头。
- 网络侧原因:带宽不足、跨地域 RTT 高、丢包、TLS、单事件过大,都会让 IO 线程拉取慢;
Relay_Log_Space不增、Last_IO_Error、IO 线程状态异常是信号。 - 从库回放侧原因:并行度低、大事务只能单 worker、DDL 串行、无主键/无唯一索引导致 ROW 回放扫全表、锁等待/长事务、从库 CPU/IO/BP 不足、备份/报表抢资源。这是最常见的“SQL 线程慢”。
- 并行复制调优:
replica_parallel_workers开 worker,LOGICAL_CLOCK按组提交并行,8.0WRITESET按写集冲突跟踪,replica_preserve_commit_order=ON保持提交顺序;但单事务、DDL、无主键仍是边界,不能指望加 worker 解决一切。 - 架构与业务手段:读写分离要把“读己之写”考虑进去(写后读主、粘性会话、
WAIT_FOR_EXECUTED_GTID_SET);报表/备份放独立从库;热点读加缓存;写入异步化/分片;延迟从库用于灾备而不是读扩展。 - 监控与预案:延迟告警(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 DDLtext
并行复制:
主库组提交: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 查找/回放)。
- MySQL 5.6:GTID、并行复制雏形;5.7:
延迟度量:
| 方法 | 原理 | 优点 | 局限 |
|---|---|---|---|
Seconds_Behind_Source | relay 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 停滞 | 拆小事务、控制批量、避免一次性大更新 |
| DDL | DDL 串行、阻塞后续 | 低峰、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_workers | worker 数量 | 按 CPU/IO/延迟调,不是越大越好 |
replica_parallel_type | LOGICAL_CLOCK/DATABASE | 8.0 推荐 LOGICAL_CLOCK |
replica_preserve_commit_order | 保持提交顺序 | 建议 ON,避免可见性/约束问题 |
binlog_transaction_dependency_tracking | COMMIT_ORDER/WRITESET/WRITESET_SESSION | WRITESET 提升非冲突事务并行 |
transaction_write_set_extraction | 写集提取 | XXHASH64 |
slave_rows_search_algorithms | ROW 回放查找 | 无主键时 HASH_SCAN 有帮助 |
log_replica_updates | 从库写 binlog | 级联复制需要 |
- 架构/业务手段:
| 手段 | 解决什么 | 代价/注意 |
|---|---|---|
| 写后读主 | 读己之写 | 主库读压力 |
| 粘性会话 | 同一用户读一致性 | 负载不均 |
WAIT_FOR_EXECUTED_GTID_SET | 等待从库追上 | 增加读延迟 |
| 半同步 | 减少丢失窗口 | 主库 RT 增加 |
| 独立报表/备份从库 | 资源隔离 | 成本 |
| 延迟从库 | 误操作恢复 | 不能用于读扩展 |
| 缓存 | 热点读 | 一致性/穿透 |
| 分库分表 | 降低单实例写入/binlog | 跨分片复杂度 |
| 异步化 | 削峰 | 业务改造 |
- 监控指标:
| 指标 | 作用 | 告警建议 |
|---|---|---|
Seconds_Behind_Source | 粗延迟 | 持续超阈值 |
pt-heartbeat 延迟 | 精确延迟 | P99/最大 |
| GTID 差集 | 事务级落后 | 持续增长 |
Relay_Log_Space | relay log 堆积 | 快速增长 |
Replica_IO/SQL_Running | 线程状态 | 非 Yes |
Last_IO_Error/Last_SQL_Error | 复制错误 | 立即告警 |
Threads_running | 从库负载 | 高水位 |
| 磁盘/IO/CPU/BP | 资源 | 容量预警 |
| 主库 binlog 生成速率 | 源头压力 | 突增 |
深挖点(面试官常追的 6 个):
Seconds_Behind_Source不可靠:它用 relay log 事件时间戳估算,SQL 线程空闲时可能是 0,大事务/时钟偏差/网络卡住时会跳变;用pt-heartbeat、GTID 差集、WAIT_FOR_EXECUTED_GTID_SET交叉验证。- 大事务是延迟的头号杀手:一个事务只能由一个 worker 回放,无法拆分;主库一个大批量更新可能让从库回放几分钟甚至几十分钟。解决是拆批、控制每批行数、避免一次性大 DDL/DML。
- 无主键/无唯一索引会让 ROW 回放退化:ROW 事件按行定位,没有主键/唯一索引时可能退化为全表扫描;
slave_rows_search_algorithms的HASH_SCAN能缓解,但补主键才是根治。 - 并行复制有边界:
LOGICAL_CLOCK按组提交并行,WRITESET按写集冲突跟踪,但单事务、DDL、无主键仍是单点;replica_preserve_commit_order=ON保证顺序,可能限制并行效果。 - 从库不是“只读就没事”:备份、报表、大查询、锁等待都会抢 CPU/IO/BP;读写分离下从库延迟会直接变成业务读不一致。独立从库、错峰、限流是常见解法。
- 读写分离的“读己之写”是业务问题:写主库后立刻读从库,可能读不到;写后读主、粘性会话、
WAIT_FOR_EXECUTED_GTID_SET、缓存都能缓解,但各有成本。延迟治理要落到业务一致性,而不是只看监控数字。
追问链:
- 主从延迟怎么定义? → 主库已提交事务到从库可见/可读的时间差;看 P50/P99/最大值。
Seconds_Behind_Source可靠吗? → 不可靠,受空闲、大事务、时钟、网络影响;要配合pt-heartbeat/GTID 差集。- 怎么准确测延迟? →
pt-heartbeat、WAIT_FOR_EXECUTED_GTID_SET、GTID 差集、业务探针。 - 延迟卡在哪一段? → 看
Read_Source_Log_Pos是否前进、Exec_Source_Log_Pos是否落后、IO/SQL 线程状态。 Read_Source_Log_Pos和Exec_Source_Log_Pos区别? → 前者 IO 线程读到的主库位点,后者 SQL 线程已执行的位点。Retrieved_Gtid_Set和Executed_Gtid_Set区别? → 已拉取 vs 已执行;差集就是待回放事务。- IO 线程慢还是 SQL 线程慢? → Read 不前进是 IO/网络/主库;Read 前进但 Exec 落后是 SQL/worker。
- 主库侧原因? → 写入突增、大事务、DDL、binlog 量大、锁竞争。
- 大事务为什么延迟? → 单事务只能单 worker 回放;拆批/控制批量。
- DDL 为什么延迟? → DDL 串行、可能阻塞后续;低峰/Online DDL/gh-ost。
- 无主键为什么延迟? → ROW 回放可能全表扫描;补主键/唯一索引、调
slave_rows_search_algorithms。 - 无索引为什么延迟? → 同上,ROW 查找/锁范围大;补索引。
- 并行复制怎么开? →
replica_parallel_workers+LOGICAL_CLOCK+replica_preserve_commit_order=ON。 LOGICAL_CLOCK原理? → 基于主库组提交逻辑时钟,同组事务可并行回放。WRITESET是什么? → 8.0 基于事务写集冲突的依赖跟踪,提升非冲突事务并行度。- worker 数量怎么定? → 按 CPU/IO/延迟压测调;太多会增加冲突/上下文切换。
preserve_commit_order作用? → 保持从库提交顺序与主库一致;建议 ON。- 从库资源不足怎么办? → 扩容、独立从库、错峰备份/报表、优化大查询。
- 从库锁等待怎么查? →
INNODB_TRX、data_lock_waits、sys.innodb_lock_waits。 - 从库备份/报表影响? → 抢 IO/CPU/BP;独立从库、限流、错峰。
- 网络影响? → 带宽/RTT/丢包/TLS;专线、压缩、就近部署。
- binlog 太大? → 大事务/批量 DML;拆批、
binlog_row_image、压缩。 - 复制过滤能用吗? → 谨慎;容易漏数据/切换困难,优先分库/分实例。
- 半同步影响延迟吗? → 影响主库 RT,不直接改善从库回放延迟;超时退化。
- 读写分离延迟怎么处理? → 写后读主、粘性会话、GTID wait、缓存、半同步。
- 读己之写怎么保证? → 会话粘主、
WAIT_FOR_EXECUTED_GTID_SET、业务补偿。 WAIT_FOR_EXECUTED_GTID_SET怎么用? → 8.0 在从库等待指定 GTID 执行完;注意超时和读延迟。- 延迟告警怎么设? → 秒级/事务级、P99/最大、持续增长;结合业务 SLO。
- 大事务怎么拦截? → SQL 审核、批量大小限制、
binlog_rows_query_log_events/监控、拆批。 - 拆批怎么做? → 按主键范围/时间分页、每批固定行数、批间休眠、避免长事务。
- 延迟导致主从切换? → 选 GTID 最全/延迟最小的从库;fencing 旧主;校验数据。
- 延迟会导致数据不一致吗? → 延迟本身不是不一致,但切换/读从库可能读到旧数据;errant transaction 才是真不一致。
pt-heartbeat原理? → 主库定时写心跳,从库读并计算时间差。pt-table-checksum用途? → 校验主从数据一致性,不是测延迟。- 多线程消费? → MySQL 是并行回放,不是业务多线程消费;业务可用 MQ 削峰。
- 分库分表有帮助吗? → 降低单实例写入/binlog/回放压力,但跨分片复杂度上升。
- 缓存有帮助吗? → 降低读从库压力,但不解决写复制延迟。
- 异步化有帮助吗? → 削峰、降低主库写入突增,但业务要改造。
- 云 RDS 怎么排查? → 看只读实例延迟指标、复制状态、网络、规格;用厂商诊断工具。
- 预案怎么写? → 延迟阈值、告警、降级(读主/缓存)、扩容、切换、复盘。
生产案例/取舍(示例,替换成你的项目):
- 批量更新百万行导致从库延迟 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/无主键 → 架构手段(读写分离/独立从库/缓存/分片)→ 监控/预案 → 生产案例与取舍。