MySQL主从复制延迟如何排查？

Question

主从复制延迟如何排查？一个常见问题是：为何从库的SQL线程执行速度远慢于主库，导致Seconds_Behind_Master持续升高？可能原因包括从库硬件性能不足、单线程回放无法充分利用多核CPU、大事务或长查询阻塞回放、网络延迟高，或主从间存在锁竞争。需结合SHOW SLAVE STATUS、性能监控指标及慢查询日志综合分析。

Answer 1

主从复制延迟的深度排查与性能优化

1. 初步诊断：通过SHOW SLAVE STATUS定位基础问题

在MySQL主从复制架构中，SHOW SLAVE STATUS是排查延迟的第一步。重点关注以下字段：

• Slave_IO_Running：确认I/O线程是否正常运行。
• Slave_SQL_Running：SQL线程是否在执行。
• Seconds_Behind_Master：当前延迟秒数。
• Read_Master_Log_Pos 与 Exec_Master_Log_Pos：差值反映未执行的中继日志量。
• Last_Error：最近发生的错误信息。

若Seconds_Behind_Master持续上升，而Slave_SQL_Running为Yes，则说明SQL线程处理速度慢于接收速度。

2. 分层排查：从网络到存储的全链路分析

层级	检查项	工具/命令
网络	主从间延迟、带宽占用	ping, traceroute, iftop
IO线程	是否积压日志	SHOW SLAVE STATUS
SQL线程	回放速度、阻塞情况	SHOW PROCESSLIST, performance_schema
磁盘IO	写入瓶颈	iostat, vmstat
CPU	负载过高或利用率不足	top, htop
锁竞争	表锁、行锁等待	INFORMATION_SCHEMA.INNODB_LOCKS
大事务	长时间未提交事务	slow query log, general log
从库配置	sync_binlog, innodb_flush_log_at_trx_commit	my.cnf
硬件差异	CPU、内存、SSD性能不匹配	基准测试对比
并行复制	是否启用MTS（Multi-Threaded Slave）	slave_parallel_workers

3. 深度剖析：SQL线程为何执行缓慢？

尽管主库能快速执行事务，但从库的SQL线程可能因以下原因变慢：

1. 单线程回放限制：传统MySQL从库默认使用单SQL线程回放事务，无法利用多核CPU优势。
2. 大事务回放：主库上一个大事务（如批量DELETE）会在从库串行重放，导致显著延迟。
3. 长查询阻塞：从库上运行的SELECT查询若未使用索引，可能持有MDL锁，阻塞SQL线程。
4. 磁盘IO瓶颈：从库磁盘写入速度慢，尤其是刷redo log和数据页时。
5. 锁竞争：主从表结构不同或外键约束可能导致锁等待。
6. 网络抖动：虽I/O线程能缓存，但高延迟会影响心跳和ACK确认。
7. 从库负载过高：额外的备份、报表查询占用资源。
8. 参数配置不当：如innodb_flush_log_at_trx_commit=1增加持久化开销。
9. Row模式下的大字段更新：每行变更都记录，日志量剧增。
10. DDL操作传播延迟：ALTER TABLE等操作在从库顺序执行，耗时更长。

4. 监控与日志分析：结合性能指标定位根因

使用以下手段进行综合分析：

-- 查看从库线程状态
SHOW PROCESSLIST;

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查询performance_schema中的等待事件
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%sql%' OR EVENT_NAME LIKE '%innodb%';
    

重点观察是否存在wait/synch/innodb/innodb_mutex或wait/io/table/sql/handler类等待。

5. 架构优化：提升从库回放能力的可行方案

为解决SQL线程瓶颈，可采用以下策略：

• 启用多线程复制（MTS）：
  SET GLOBAL slave_parallel_workers = 8;
  配合slave_parallel_type=LOGICAL_CLOCK实现基于组提交的并行回放。
• 使用增强半同步复制（semi-sync）减少网络不可靠影响。
• 部署中间件代理（如MHA、MaxScale）实现自动故障转移与负载均衡。
• 对从库进行读写分离，避免后台任务干扰复制线程。
• 定期分析慢查询日志，优化引起阻塞的SQL。

6. 可视化流程：主从延迟排查决策树

graph TD
            A[Seconds_Behind_Master升高] --> B{Slave_IO_Running?}
            B -- No --> C[检查网络、主库binlog权限]
            B -- Yes --> D{Slave_SQL_Running?}
            D -- No --> E[查看Last_Error，修复SQL异常]
            D -- Yes --> F[检查Exec vs Read日志位置差]
            F --> G{差值增大?}
            G -- Yes --> H[SQL线程处理慢]
            G -- No --> I[网络或IO问题]
            H --> J[分析慢查询、锁、磁盘IO]
            J --> K[启用MTS或优化硬件]

7. 实战建议：建立长效监控机制

为预防延迟累积，建议：

• 部署Prometheus + Grafana监控Seconds_Behind_Master趋势。
• 设置告警阈值（如>60s）触发通知。
• 定期执行pt-table-checksum验证数据一致性。
• 使用pt-slave-delay模拟延迟场景进行压测。
• 对大事务拆分，避免在业务高峰期执行DDL。