主库写了备库查不到？主从延迟四步定位法

十五年数据库相关经验做过 DBA、架构师、技术顾问。不求颠覆只求靠谱。主从同步这个事看起来简单。主库写、备库读binlog 传过去、relay log 回放完齐活。但真出问题时能要人命。上个月接到一个告警业务方反馈刚下的订单查不到。排查下来是主从延迟 30 秒——用户刚在主库写完备库还没同步到。30 秒在互联网业务里就是数据丢了。干了这么多年 DBA主从延迟是最容易被低估、也最容易背锅的问题。今天把排查方法论整理出来从发现延迟到定位根因到解决每一步都说清楚。有好消息也有踩坑的地方。各位耐心看完。01 延迟从哪来先搞清主从同步的完整链路排查延迟之前先搞清楚延迟可能出现在哪个环节。主从同步不是主库写完备库立刻就有它是一整条链路第一步主库写 binlog。主库执行写操作后把变更记录写入 binlog 文件。这一步通常很快毫秒级。第二步binlog 传输到备库。备库的 I/O 线程连接主库拉取 binlog写入备库的 relay log。这一步的延迟取决于网络带宽和 binlog 产生速度。第三步备库回放 relay log。备库的 SQL 线程或者多线程复制的工作线程读取 relay log把变更重放到备库数据上。这一步是延迟的重灾区。第四步备库数据可见。回放完成后新数据才能在备库上被查询到。关键认知主从延迟不是一个数字是整条链路累积的结果。排查时要逐段确认延迟出在哪个环节。02 怎么发现延迟别等业务方来报主从延迟最可怕的不是有延迟是有延迟但没人知道。等业务方反馈刚写的数据查不到问题已经发生很久了。DBA 应该在业务感知之前就发现并处理。监控指标看这三个数就够了MySQL 8.0查询performance_schema.replication_connection_status和replication_applier_status关注LAST_HEARTBEAT_TIMESTAMP最后一次心跳时间和SERVICE_STATE复制状态。MySQL 5.7执行SHOW SLAVE STATUS关注Seconds_Behind_Master延迟秒数和Slave_IO_Running/Slave_SQL_Running复制线程状态。PostgreSQL查询pg_stat_replication关注write_lag、flush_lag、replay_lag分别对应写入、刷盘、回放延迟。三个阈值分三级告警延迟级别阈值响应动作正常 1 秒无需处理常规监控警告1-10 秒关注趋势准备介入危险 10 秒立即排查必要时切主我的习惯告警不要只设一个阈值。设三级让团队知道现在是什么状态。只设一个延迟 10 秒告警等告警响了再查黄花菜都凉了。03 排查流程——四步定位法发现延迟后按这个流程走90% 的主从延迟都能定位到根因。第一步确认延迟在主库还是备库怎么看在主库执行一个写入操作记录时间戳。然后在备库查询这条数据记录看到的时间戳。两者之差就是端到端延迟。如果延迟很大但主库的 binlog 写入很快查 binlog 文件大小增长速度说明问题不在主库在传输或回放环节。第二步确认是传输慢还是回放慢怎么看MySQL 执行SHOW SLAVE STATUS对比两个值Read_Master_Log_PosI/O 线程读到的主库 binlog 位置Exec_Master_Log_PosSQL 线程回放到的位置如果Read_Master_Log_Pos落后Master_Log_Pos主库当前位置很多说明传输慢——网络带宽不够或者主库 binlog 产生太快。如果Read_Master_Log_Pos跟得上但Exec_Master_Log_Pos落后很多说明回放慢——备库 SQL 线程处理不过来。第三步定位回放慢的具体原因回放慢是最常见的延迟原因。具体又分几种情况情况 A大事务回放。主库执行了一个大事务比如批量更新了 100 万行备库回放这个事务时SQL 线程被占住其他小事务都得排队等。怎么看查SHOW PROCESSLIST看备库 SQL 线程正在执行什么 SQL。如果是一条执行了几十秒还没完的 UPDATE 或 DELETE基本就是大事务了。情况 B锁冲突。备库回放时遇到锁冲突SQL 线程被阻塞。怎么看查备库的information_schema.innodb_trx和data_lock_waits看有没有锁等待。情况 C备库硬件跟不上。主库用 SSD备库用的是机械盘。同样的写入量备库回放速度天然就慢。怎么看对比主备两端的磁盘 I/O 指标iostat 看 iowait 和吞吐量。如果备库磁盘利用率持续 100%就是硬件瓶颈。第四步验证根因定位到可能的根因后验证一下如果是大事务在测试环境模拟同样规模的事务看备库回放耗时。如果是锁冲突分析锁等待链确认阻塞源。如果是硬件瓶颈做磁盘 I/O 压测确认备库磁盘确实扛不住。经验不要猜根因要验证。我见过太多 DBA 凭经验觉得是大事务导致的结果查半天发现是备库磁盘满了。04 解决思路——对症下药定位到根因后解决思路就清晰了。方案 A大事务 → 拆分事务 并行复制大事务是主从延迟的头号杀手。一个事务更新了 100 万行备库 SQL 线程要一条一条回放可能花几十分钟。治标把大事务拆成小事务。原来一个 UPDATE 更新 100 万行改成每次更新 1 万行分 100 次执行。每次持锁时间短备库回放也快。治本开启并行复制。MySQL 5.7 支持多线程复制MTS备库可以用多个线程并行回放不同库或不同事务的变更。-- MySQL 开启并行复制基于库的并行SETGLOBALslave_parallel_workers4;-- MySQL 5.7 基于逻辑时钟的并行复制推荐SETGLOBALslave_parallel_typeLOGICAL_CLOCK;SETGLOBALslave_parallel_workers8;注意并行复制不是线程越多越好。线程数超过 CPU 核心数后收益递减。一般设成 CPU 核心数的 1-2 倍。方案 B网络传输慢 → 压缩 带宽扩容如果确认是 binlog 传输慢两个方向解决开启 binlog 压缩MySQL 5.7 支持 binlog 传输压缩在网络带宽紧张时能显著降低传输量。-- 主库开启 binlog 压缩SETGLOBALbinlog_transaction_dependency_trackingWRITESET;扩容网络带宽如果主备跨机房部署网络带宽是硬瓶颈。该升级就升级别省这个钱。方案 C备库硬件瓶颈 → 升级存储 读写分离如果备库硬件确实扛不住两条路短期把备库的读流量切一部分走主库降低备库负载。长期升级备库存储。把机械盘换成 SSD或者上 NVMe。硬件升级的钱比延迟导致业务损失的钱少多了。对比三种复制方案的延迟表现把上面的内容做个对比方便选型方案延迟范围适用场景局限性单线程复制秒级到分钟级小数据量、低写入频率大事务必延迟基于库的并行复制亚秒级到秒级多库部署、写入分散单库大事务仍然慢基于逻辑时钟的并行复制亚秒级生产环境推荐需要 MySQL 5.7半同步复制毫秒级但有写入延迟数据零丢失场景主库写入变慢选型建议生产环境优先用基于逻辑时钟的并行复制MTS。数据安全性要求极高的场景用半同步复制但接受主库写入性能的轻微下降。延迟容忍度的判断框架不是所有场景都需要零延迟。按业务容忍度分三档容忍度高延迟 10 秒可接受后台报表查询数据分析任务非实时用户查询方案标准异步复制 常规监控容忍度中延迟 3 秒可接受用户个人中心查询商品详情查询订单状态查询方案并行复制 三级告警 自动切换容忍度低延迟 1 秒可接受账户余额查询实时库存查询支付状态确认方案半同步复制 强制走主库查询 秒级监控我的经验不要一刀切要求主从零延迟。不同业务场景对延迟的容忍度不同。按场景分级治理比统一高标准更务实。从主从延迟是玄学到延迟是可管理的说实话干 DBA 前五年我对主从延迟的态度是能忍就忍。延迟几秒嘛业务又不会挂。后来接了几个金融项目才明白延迟不是能不能忍的问题是业务允不允许的问题。证券交易系统延迟 1 秒就是数据不一致合规上过不了关。这个认知转变让我重新审视主从延迟的排查方法。以前是延迟大了再看现在是持续监控、分级治理、提前预警。主从延迟不是玄学是可测量、可定位、可优化的工程问题。深度分析为什么并行复制不能解决所有延迟问题很多人以为开了并行复制就万事大吉。实际不是这样。并行复制解决的是多个小事务排队等的问题。但如果来了一个大事务比如批量删除 1000 万条过期日志再多的并行线程也得等这个事务回放完。根因在于大事务本身是一个不可分割的原子操作。备库不能把一个事务拆成两半来回放——那会破坏事务的原子性。所以并行复制有天花板。它能解决并发小事务的延迟解决不了单一大事务的延迟。真正的解法是从源头控制事务大小。应用层做批量操作时分批提交。每批 1000-5000 行不要一个事务搞定。这个道理和 DBA 的日常建议一致事务尽量短。不仅是为了减少锁等待也是为了减少主从延迟。主从延迟巡检清单日常巡检建议每周执行一次1. 延迟趋势检查查看过去一周的延迟曲线确认没有持续增长趋势如果延迟峰值在上升说明复制能力在下降需要排查2. 复制线程状态确认 I/O 线程和 SQL 线程都在运行如果有线程停过查错误日志看原因3. 大事务监控查看主库 binlog 中事务大小的分布如果出现异常大的事务通知应用团队优化4. 硬件资源检查备库磁盘 I/O 利用率是否接近上限备库 CPU 和内存使用是否正常5. 告警规则验证确认三级告警阈值配置正确测试告警通道是否正常发一条测试告警确认能收到总结主从延迟排查就一套流程看监控 → 分段定位 → 验证根因 → 对症下药。核心认知延迟不是一个数字是整条链路累积的结果。传输慢和回放慢是两回事不能混为一谈。预防延迟的关键事务要短、复制要并行、监控要分级。处理过几十次主从延迟问题每次回到这套流程问题都能解决。不需要什么高级工具耐心和方法论就够了。后续我会继续分享数据库参数调优实战、容量规划方法论这些话题跟着我一篇篇学数据库这块就没问题了。有问题评论区见。十五年数据库领域老炮。关注我一起把数据库这件事搞明白。