一条 380 万行的 UPDATE 把 binlog 单文件撑到 2.6GB：大事务的切分策略与主从延迟治理

作者陈景行-142

MySQLbinlog大事务主从延迟DBA

一、现象：主从延迟 47 秒，从库告警炸了

上个月给一套库存系统做巡检。核心表 t_inventory_log（42 亿行、单表 1.8TB、InnoDB、MySQL 8.0.32、binlog=row）。业务方一条批量回刷脚本：

UPDATE t_inventory_log
   SET warehouse_code = 'WH-SH-01', updated_at = NOW()
 WHERE warehouse_code = 'LEGACY-00'
   AND created_at < '2024-06-01';

命中 380 万行。执行完，主库正常，但从库 Seconds_Behind_Master 直接飙到 47 秒，Canal 订阅端 3 秒内积压了 260 万条事件，消费端 OOM 重启 2 次。业务方问："binlog_max_size=1GB 不是配了吗？怎么单文件还能撑到 2.6GB？"

影响面：从库延迟 47 秒期间，3 个走从库的报表接口全部超时（P99 从 80ms 飙到 5.2s）；Canal OOM 后 12 秒内丢失 4 万条增量，需要人工对齐。

二、原理：`binlog_max_size` 切不了「单事务」

MySQL 的 binlog 切分规则是在事务边界处切，而不是按字节数硬切：

binlog_max_size（旧名 max_binlog_size）只决定「当前事务写完、且还没到边界时，如果文件已超阈值，就切新文件」；
单个事务再大，也绝不跨文件——因为 binlog 是 append-only，事务的 BEGIN/COMMIT 必须落在同一个文件里，否则崩溃恢复无法还原原子性；
row-based 格式下，380 万行 × 每行 ~700 字节（含 before/after image + event header）≈ 2.6GB，这一个事务就顶穿了 1GB 的阈值。

从库为什么慢？从库是单线程回放（即使开了 replica_parallel_workers=128，同一事务的所有行也必须串行回放——MTS 按 GTID 并行，不拆事务）。主库 380 万行的 UPDATE 用了 42 秒写 redo + binlog，从库要按行重放这 380 万行的 apply：redo 重放 + 二级索引维护 + 可能的页分裂，380 万行的回放时间在 40–60 秒量级。

三、做法：按 PK 范围切片，每片 ≤ 5 万行

核心原则：把「一个 380 万行事务」拆成「76 个 5 万行事务」。 5 万行的 binlog 约 35MB，远低于 1GB 阈值，从库回放时间 < 1 秒，MTS 也能真正并行。

-- 1. 先摸清 PK 分布（假设 PK 是自增 id）
SELECT MIN(id), MAX(id), COUNT(*)
  FROM t_inventory_log
 WHERE warehouse_code = 'LEGACY-00' AND created_at < '2024-06-01';
-- 假设 MIN=102348821, MAX=141022938, COUNT=3800000

-- 2. 按 5 万行一批切片（用 id 范围，不用 LIMIT 偏移）
-- 在应用层或脚本里循环：
UPDATE t_inventory_log
   SET warehouse_code = 'WH-SH-01', updated_at = NOW()
 WHERE id BETWEEN :lo AND :hi
   AND warehouse_code = 'LEGACY-00'
   AND created_at < '2024-06-01';

-- 3. 每批之间 sleep 200ms，给从库回放窗口
SLEEP(0.2);

为什么用 id BETWEEN 而不是 LIMIT 50000 OFFSET n：OFFSET 会扫描并丢弃前 n 行，380 万行时 OFFSET 到后面就是 O(n²) 的灾难（最后一批 OFFSET 375 万行）；id BETWEEN 走 PK 范围扫描，每批都是等量的 B+ 树区间查找，O(log N)。

切片步长怎么定：5 万行 × ~700B ≈ 35MB/批，从库回放 < 1 秒，主从延迟峰值控制在 2–3 秒。如果业务对延迟更敏感，可以缩到 2 万行/批（14MB/批）。

用 pt-archiver 更稳：

pt-archiver \
  --source h=主库,u=replicator,p=*** \
  --table t_inventory_log \
  --where "warehouse_code='LEGACY-00' AND created_at<'2024-06-01'" \
  --set "warehouse_code='WH-SH-01',updated_at=NOW()" \
  --limit 50000 \
  --sleep 0.2 \
  --commit-each \
  --no-check-charset

--commit-each 是关键字：每 5 万行一个事务，而不是攒到最后一次性 commit。不加这个参数，pt-archiver 默认行为是整个 --where 范围内一个事务，等于没拆。

四、踩过的坑与反直觉

坑 1：binlog_max_size 不是「文件最大 1GB」。 它的真实语义是「建议切分阈值，单事务例外」。很多团队把它当硬上限配，结果一个大事务照样能把文件撑到 10GB+。8.0.34+ 可以用 SHOW BINARY LOG STATUS 看当前文件大小，但切不切是引擎决定的，你控制不了。

坑 2：innodb_lock_wait_timeout 默认 50 秒，大事务持锁时间远超这个值。 380 万行的 UPDATE 在主库执行 42 秒，期间所有要更新这 380 万行的其他事务全部排队，超过 50 秒的直接 Lock wait timeout exceeded (try again later)。业务侧表现是「随机 50 秒后报错」，报错栈里看不到这条大事务的影子，排查极难。解法：拆分后单事务持锁 < 1 秒，锁等待自然消失。

坑 3：row-based binlog 的「写放大」被严重低估。 statement-based 下，380 万行的 UPDATE 在 binlog 里只占几 KB（一条 SQL 文本）；row-based 下，每行都要写 before-image + after-image，380 万行 × 1.4KB ≈ 5.2GB（含 event header 与 GTID 事件）。如果你的主从链路带宽只有 1Gbps（~125MB/s），光传输这 2.6GB 就要 21 秒——这是从库延迟的硬下限，拆分也救不了带宽，只能加带宽或在中间件层压缩（MySQL 8.0 原生不支持 binlog 压缩）。

坑 4：replica_parallel_workers=128 对大事务毫无帮助。 MTS 是按 GTID 并行，同一个事务的所有行必须串行回放。380 万行的 UPDATE 是一个事务，128 个 worker 也救不了它。只有拆成 76 个独立事务，MTS 才能真正并行回放——这是我们实测验证的：拆分后 replica_parallel_workers=16 的回放时间比不拆分 + workers=128 还快 3 倍。

五、结论与可复用清单

核心认知：binlog 的切分单位是「事务」，不是「字节数」。大事务是 binlog 膨胀、主从延迟、锁等待的三合一放大器。

可复用清单（直接抄）：

任何批量 UPDATE/DELETE，单事务行数 ≤ 5 万（row-based 下 binlog < 35MB，从库回放 < 1 秒）；
切片用 PK 范围（id BETWEEN），不用 LIMIT OFFSET（O(n²) 灾难）；
每批之间 SLEEP(0.2) 或 pt-archiver --sleep 0.2，给从库回放窗口；
pt-archiver --commit-each 或应用层每批 COMMIT 一次，不要攒到最后；
监控 Seconds_Behind_Master 阈值设 5 秒，超过就查 SHOW BINARY LOG STATUS 看当前 binlog 文件大小；
大事务期间监控 performance_schema.events_waits_summary_global_by_event_name 中 wait/lock/table/innodb/base，锁等待 > 100 次/分钟就该报警；
binlog 链路带宽 < 200MB/s 时，大事务的 binlog 体积就是主从延迟的硬下限，拆分救不了带宽，要么加带宽要么评估改 statement-based（需从库一致性审计）。

成本与收益权衡：拆分脚本多写 20 行代码、执行时间从 42 秒变成 42 秒 + 75×0.2 秒 ≈ 57 秒（多了 15 秒），换来主从延迟从 47 秒降到 2 秒、锁等待从 380 万行降到 0、Canal 不再 OOM。15 秒的额外执行时间，买断一次 P0 故障的复盘时间，这笔账怎么算都划算。

赞 0 · 收藏 0 · 评论 0 · 浏览 0