innodb_flush_log_at_trx_commit 与 sync_binlog 组合选型：一次断电丢了多少事务的数据

一、现象：一次机房断电，订单表少了 412 条

去年 11 月，一家电商客户机房突然断电（UPS 顶了 90 秒，但一台主机因内存吃紧提前 OOM 重启）。恢复后业务方发现：断电前 1 分钟内的下单里有 412 条「客户端收到 200、但订单表查不到」。DBA 值班拉配置一看：innodb_flush_log_at_trx_commit=2、sync_binlog=10000、innodb_flush_method=O_DIRECT。业务方问：能不能保证「客户端收到 200 就一定落盘」？我答：不能，这个组合下宕机窗口大约 3–7 秒的数据会丢，具体取决于当时的写入压力。

影响面：412 条订单 × 客单价 180 元 ≈ 7.4 万元直接损失，加上客服补偿与法务成本，一次事故的账比一年省下的机器预算还高。

二、原理：fsync 到底卡在哪一层

MySQL 8.0 的写路径里，一个事务要「安全」至少要过三道门：

1. InnoDB redo log：由 innodb_flush_log_at_trx_commit 控制
   • 1：每次 commit 都 write() + fsync()，redo 落物理盘才算提交；
   • 2：每次 commit 只 write() 到 OS page cache，fsync 由后台线程每秒批量做；
   • 0：write 与 fsync 都交给后台，每秒一次。
2. Binlog：sync_binlog=N 表示每 N 个事务 fsync 一次 binlog 文件。
3. Data file 脏页：由 innodb_io_capacity / innodb_max_dirty_pages_pct 控制刷脏节奏，与「提交」本身无关。

关键机制：崩溃恢复时 InnoDB 会从最近的 checkpoint 开始重放 redo log；但 redo 只有已落盘的部分才存在。=2 意味着「commit 返回 200」时，redo 可能还压在 OS page cache 里——断电瞬间 page cache 蒸发，这段窗口内的事务全部丢失。

sync_binlog=10000 同理：binlog 是 append-only，未 fsync 的字节同样可能丢。更隐蔽的是它会制造主库 redo 有、binlog 没有的错位，从库 / Canal / Debezium 订阅端会看到「主从不一致」，且很难事后对齐。

三、做法：三档组合的实测矩阵

硬件：4C8G、NVMe（fio 4K 随机写 ~220K IOPS）、MySQL 8.0.36、innodb_buffer_pool_size=4G、innodb_flush_method=O_DIRECT、innodb_doublewrite=ON。压测用 sysbench oltp_write_only，16 线程，10 分钟。

| 组合 | 平均 TPS | P99 延迟 | 断电后丢失窗口 | |---|---|---|---| | 1 / sync_binlog=1 | 18,532 | 11ms | 0（事务级持久） | | 2 / sync_binlog=1 | 32,847 | 6ms | 3–7 秒 | | 2 / sync_binlog=100 | 35,213 | 6ms | 3–7 秒 + 主从不一致 | | 0 / sync_binlog=100 | 38,906 | 5ms | 5–12 秒 |

几点观察：

• 1→2 吞吐提升约 77%，不是 3 倍——group commit 已经把 fsync 合并了，=2 只是省掉「每事务一次 fsync」的等待；
• 2/1 → 2/100 只再涨 7%——binlog 的 fsync 早已被 batch，sync_binlog 从 1 调到 100 收益极小，但主从不一致风险被放大 100 倍；
• =0 再涨 10%，崩溃窗口直接翻倍，性价比最差。

推荐基线（订单 / 金融类）：

innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1
binlog_group_commit_sync_delay = 0   # 8.0 默认就是 0
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = ON

高吞吐非核心库（埋点、日志、缓存回写）：

innodb_flush_log_at_trx_commit = 2
sync_binlog                    = 1     # 保主从一致

四、踩过的坑

1. binlog_group_commit_sync_delay 是「凑批」不是「异步」：设成 100 微秒后吞吐能再涨 5–8%，但 P99 会稳定抬升几十微秒。有人误以为这是「异步提交」，其实还是同步的，只是主动凑批。
2. O_DIRECT + =2 组合断电丢失窗口比 fsync 模式还长：我们一次实测 O_DIRECT 掉电丢 7.2 秒，改成 fsync 掉电丢 4.8 秒（同样 =2）——page cache 里可能积压了更久的 redo。
3. innodb_doublewrite 不是给「提交」用的，是给「崩溃时半页恢复」用的。关掉它确实能涨 3–5% 吞吐，但一次断电可能让你整表损坏，DBA 视角这笔账永远不划算。
4. 别拿 mysqldump --single-transaction 当一致性快照兜底——它依赖 InnoDB MVCC，如果 binlog 已经因 sync_binlog 丢了段，dump 出来的表和下游订阅对不上。

五、结论与可复用清单

一句话：「客户端收到 200」≠「数据已落盘」，除非 innodb_flush_log_at_trx_commit=1 且 sync_binlog=1。

可复用清单：

1. 上线前跑一次 SHOW VARIABLES LIKE 'innodb_flush_log%' + SHOW VARIABLES LIKE 'sync_binlog'，两个都是 1 才算「事务级持久」；
2. 每半年做一次断电演练（reboot -f 或拔电），统计丢失事务数并写进 SLO；
3. 主从复制链路必须监控 binlog 位点差，sync_binlog>1 时把「位点差 > 1000」告警阈值设上；
4. 别为了 10% 的吞吐去动 =0，那是拿「数据完整性」换「延迟数字」，DBA 视角没有性价比；
5. 埋点 / 日志类库可以放宽到 =2，但 sync_binlog 保持 1，主从一致不能丢。

写到这里，回到开头那 412 条订单：后来我们把配置拉回 1/1，吞吐掉了 43%，机器成本一年涨了 6 万——但那 7.4 万元的一次性损失，加上客服、法务、客诉，总账算下来，这笔「保守」的配置其实是最便宜的。