MySQL 5.0 群組提交失效與 fsync 偽裝：效能暴跌 10 倍的根因深度剖析與解決方案

前言：升級 5.0 後效能崩塌的神秘案例

多位用戶反映升級 MySQL 5.0 後效能下降 10 倍，且硬體相關、OS 版本相關、甚至 --log-bin 開關相關。本文深度剖析雙重根因：Group Commit 失效 與 偽 fsync，並給出生產級解決方案。

根因一：Group Commit (群組提交) 在 Binlog 啟用時失效

機制背景

• Group Commit： 多個事務提交時，合併一次 fsync() 將 Redo Log 刷盤，大幅降低 I/O 放大。
• XA (Two-Phase Commit)： 為支援 Binlog 與 InnoDB 一致性，MySQL 5.0 引入 XA，準備階段寫 Prepare、提交階段寫 Commit。

失效鏈路

1. 啟用 --log-bin → 自動啟用 XA 支援。
2. XA 要求嚴格的 Prepare/Commit 順序，破壞了原有 Group Commit 合併邏輯。
3. 新版 Group Commit 代碼從未實現 (Bug #13669)，導致每個事務提交都獨立 fsync()。
4. 結果：寫入吞吐量下降 10 倍，特別是高併發小事務場景。

驗證方法

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
-- fsync 次數 ≈ Commit 次數，說明未合併

解決方案

• 方案 A：關閉 Binlog (從庫/非 PITR 場景)

  # my.cnf
  skip-log-bin
  # 或啟動參數 --skip-log-bin

  禁用 XA，舊 Group Commit 代碼恢復生效。
• 方案 B：升級至 5.1+ / 5.5+ / 5.6+ / 8.0
  • 5.1：部分修復，但仍不完美。
  • 5.5/5.6：真正實現 Group Commit (Binlog Group Commit)，sync_binlog=1 時合併 fsync。
  • 8.0：Binary Log Group Commit (BLGC) 成熟，支援多階段流水線合併，效能最強。

根因二：作業系統/硬體「偽 fsync」掩蓋真實問題

現象

• 部分 OS/磁碟控制器/RAID 卡對 fsync() 不真正刷入物理介質，僅寫入控制器快取即返回成功。
• 真實 fsync()：受限磁碟轉速，HDD 約 80-200 次/秒，SSD 約 5k-20k 次/秒。
• 偽 fsync()：可達 1000-10000+ 次/秒，掉電數據丟失風險極高 (違反 ACID)。

檢測工具

# SysBench 測試真實 fsync 效能
sysbench --test=fileio --file-fsync-freq=1 --file-num=1 --file-total-size=16384 --file-test-mode=rndwr run
# 觀察 requests/sec：真實 fsync 應在磁碟理論 IOPS 附近

# 專用工具
diskTest (https://www.faemalia.net/mysqlutils/)  # 含 fsync 正確性驗證

解決方案

• 方案 A：啟用帶電池備援快取 (BBWC/FBWC) 的 RAID 卡
  • 寫入快取 → 電池保護 → 掉電不丟失，等效「安全的偽 fsync」。
  • 確保磁碟自身快取 關閉 (或由 RAID 卡統一管理)。
  • 成本已大幅下降，強烈推薦生產環境標配。
• 方案 B：使用 NVMe SSD + 關閉磁碟快取
  • NVMe 延遲極低 (μs 級)，真實 fsync 效能足夠支撐高併發。
  • hdparm -W 0 /dev/nvme0n1 關閉易失性快取。
• 方案 C：調整 innodb_flush_log_at_trx_commit = 2 (風險可控場景)
  • 每秒 fsync 一次，效能提升巨大，但掉電丟 1 秒資料。
  • 需配合應用層冪等/重試機制。

綜合建議：從根治到緩解的分層策略

結語

MySQL 5.0 的 Group Commit 缺失是經典架構缺陷，結合硬體層的「偽 fsync」掩蓋，造就了「升級即災難」的慘劇。現代版本 (5.6+/8.0) 已徹底修復 Group Commit，配合 BBWC RAID 或 NVMe SSD，能在完全 ACID 合規前提下榨乾硬體效能。請將 fsync 效能驗證 納入新硬體驗收標準，拒絕「偽 fsync」偽裝的高效能陷阱。