主从延迟的原因分析与排查方法

概述

主从延迟（Replication Lag）是指从库落后主库的时间差，通常用 Seconds_Behind_Master 衡量。延迟是主从复制中最常见也最棘手的问题之一，严重影响读写分离场景下的数据一致性。

延迟的度量

-- 查看延迟
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 120
-- 表示从库大约落后主库 120 秒

Seconds_Behind_Master 的局限：
– 它是近似值（主库时间 – 从库最近执行事务的时间戳）
– 如果主从时钟不一致，延迟值不准
– 如果 I/O 线程停止，它不增长
– 如果 SQL 线程出现问题，它可能显示为 0

更好的度量方式：

-- 对比主库 Binlog 位置和从库执行的位置
-- 主库
SHOW MASTER STATUS;
-- +------------------+-----------+
-- | File             | Position  |
-- +------------------+-----------+
-- | mysql-bin.000010 | 123456789 |
-- +------------------+-----------+

-- 从库
SHOW SLAVE STATUS\G
-- Master_Log_File: mysql-bin.000010
-- Read_Master_Log_Pos: 100000000   ← 已拉取的位置
-- Exec_Master_Log_Pos: 80000000    ← 已执行的位置
-- 落后字节数 = 123456789 - 80000000

延迟的六大原因

1. 从库硬件性能不足（最常见）

主库配置：32 核 CPU, 512G 内存, NVMe SSD
从库配置：4 核 CPU, 32G 内存, 普通 SATA SSD
                              ↑ 配置差太多

原因：从库的处理能力远低于主库。
解决：升级从库配置，至少与主库同级。

2. 从库有慢查询

从库通常用于读查询，如果大查询（如复杂的报表统计）与 SQL 线程竞争资源：

-- 这类查询会占用大量 CPU/IO，拖慢 SQL 线程
SELECT COUNT(*), SUM(amount), AVG(price)
FROM orders
WHERE YEAR(create_time) = 2026;

解决：

-- 使用 pt-query-digest 找出从库的慢查询
-- 优化慢查询或将大查询移至专用的分析库

3. 单线程 SQL 回放瓶颈（高并发场景）

主库 TPS = 10,000，但单线程 SQL 线程最多处理约 3,000 TPS。

解决：启用并行复制

slave_parallel_workers = 8
slave_parallel_type = LOGICAL_CLOCK

4. 大事务

-- 一个 UPDATE 影响 1000 万行
UPDATE users SET is_vip = 1 WHERE last_login > '2025-01-01';

-- 其他小事务全部被堵在后面

解决：
– 将大事务拆分为小批次
– 设置 binlog_row_image = minimal
– 控制单次操作的数据量

5. 主库频繁的 DDL 操作

-- ALTER TABLE 不仅占用主库资源
ALTER TABLE users ADD INDEX idx_email(email);

-- 从库的 SQL 线程在执行 ALTER 时也会卡住
-- ALTER 执行期间，后续所有事务排队等待

解决：使用 pt-online-schema-change 或 gh-ost 进行在线 DDL。

6. 网络延迟

主从之间的网络延迟高：

# 测试网络延迟
ping slave_host
# 64 bytes: icmp_seq=1 ttl=64 time=50 ms  ← 50ms 延迟
# 64 bytes: icmp_seq=2 ttl=64 time=100 ms ← 抖动大

影响：I/O 线程拉取 Binlog 变慢，间接导致延迟。

解决：
– 主从尽量部署在同一机房或同一可用区
– 跨机房场景需接受一定延迟
– 考虑使用半同步复制减少丢失风险

延迟排查流程

查出延迟（Seconds_Behind_Master > 0）
    │
    ├── I/O 线程是否正常？
    │   ├── Slave_IO_Running: No → 网络问题或主库挂了
    │   └── Slave_IO_Running: Yes → 继续
    │
    ├── SQL 线程是否正常？
    │   ├── Slave_SQL_Running: No → SQL 错误（如主键冲突）
    │   └── Slave_SQL_Running: Yes → 继续
    │
    ├── 检查 Exec_Master_Log_Pos 是否在增长？
    │   ├── 不增长 → SQL 线程被卡住（可能是大事务或 DDL）
    │   └── 在增长 → 继续
    │
    ├── 看 Read_Master_Log_Pos 和 Exec_Master_Log_Pos 差距
    │   ├── 差距大 → SQL 线程太慢（配置不足/未用并行复制）
    │   └── 差距小 → I/O 线程拉取慢（网络延迟）
    │
    └── 检查从库资源（CPU、IO、磁盘）

排查命令汇总

# 1. 查看复制状态
mysql -e "SHOW SLAVE STATUS\G" | grep -E "(Seconds_Behind|Running|_Log_Pos|_Log_File)"

# 2. 查看从库负载
top -bn1 | head -20
iostat -x 1 3
df -h

# 3. 查看从库正在执行的查询
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

# 4. 查看主库 Binlog 生成速度
# 查看 Binlog 文件大小变化

面试要点

延迟的根本原因：从库处理能力跟不上主库的变更速度
最常见原因：从库配置低、慢查询争抢资源、单线程回放瓶颈
大事务是延迟的放大器：一个更新 1000 万行的 SQL 就能让延迟飙升
DDL 会卡住 SQL 线程——ALTER TABLE 期间其他事务无法执行
排查关键指标：Seconds_Behind_Master、Exec_Master_Log_Pos、从库 CPU/IO
延迟≠必然丢数据：延迟是时间差，不是数据丢失

文章版权归作者所有，未经允许请勿转载。

THE END

Agent 智能体开发

主从延迟的原因分析与排查方法

主从延迟的原因分析与排查方法

概述

延迟的度量

延迟的六大原因

1. 从库硬件性能不足（最常见）

2. 从库有慢查询

3. 单线程 SQL 回放瓶颈（高并发场景）

4. 大事务

5. 主库频繁的 DDL 操作

6. 网络延迟

延迟排查流程

排查命令汇总

面试要点

请登录后发表评论