目錄
一、線上告警
二、問題診斷
1.?系統層面排查
2. 數據庫層面分析
三、參數調優
1. sync_binlog 參數優化
2. innodb_flush_log_at_trx_commit 參數調整
四、其他優化建議
1.?日志文件位置調整
2. 生產環境核心參數配置模板
3. 突發 IO 高負載應急響應方案
五、風險提示
六、總結
一、線上告警
????????某一天，生產環境監控系統突然報警：MySQL 磁盤 IOPS 持續超過 15000，平均響應時間突破 500ms，慢查詢數量大量增加。登錄數據庫服務器發現：
- 磁盤利用率長期維持在 98% 以上
iostat -x 1顯示%util持續 100%- MySQL 進程 CPU 使用率達 90%，但大部分時間處于
iowait狀態
二、問題診斷
1.?系統層面排查
# 查看系統整體IO情況
$ iostat -x 1 10
Linux 5.4.0-150-generic (mysql-prod-01) 03/22/2025 _x86_64_ (32 CPU)avg-cpu: %user %nice %system %iowait %steal %idle7.2 0.00 4.5 12.3 0.0 76.0Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
sda 0.0 0.0 0.0 14828.0 0.0 245440.0 33.1 7.5 0.5 0.0 0.5 0.0 99.8
sdb 0.0 4567.0 230.0 2800.0 18400.0 224000.0 152.8 12.5 4.1 2.8 4.2 0.3 99.9# 監控MySQL進程IO情況
$ pidstat -d 1
Linux 5.4.0-150-generic (mysql-prod-01) 03/22/2025 _x86_64_ (32 CPU)15:30:01 UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
15:30:02 0 12345 0.00 245760.00 0.00 mysqld# 分析IO請求分布
$ iotop -o
Total DISK READ : 0.00 B/s | Total DISK WRITE : 240.00 M/s
Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 240.00 M/sTID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
12345 be/4 mysql 0.00 B/s 240.00 M/s 0.00 % 99.99 % mysqld --defaults-file=/etc/mysql/my.cnf通過上述命令發現：
- MySQL 進程（PID 12345）占用了 92% 的磁盤寫 IO
- 大部分 IO 集中在
/var/lib/mysql/ib_logfile0和/var/lib/mysql/mysql-bin.000001文件
2. 數據庫層面分析
-- 查看InnoDB日志等待情況
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************Type: InnoDBName:
Status:
=====================================
2025-03-22 15:35:23 0x7f8a1c000700 INNODB MONITOR OUTPUT
=====================================
[...]
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
288334 OS file reads, 1234567 OS file writes, 876543 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 245.00 writes/s, 120.00 fsyncs/s
[...]-- 分析慢查詢日志
$ pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt關鍵發現：
- InnoDB 日志等待事件占比達 68%
- 大量簡單 INSERT 語句執行時間超過 200ms
- binlog 寫入等待成為性能瓶頸
三、參數調優
1. sync_binlog 參數優化
原配置：
mysql> SHOW VARIABLES LIKE '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)? ? sync_binlog=1?意味著每次事務提交都會強制將 binlog 寫入磁盤，這是導致高 IO 的主要原因。在高并發寫入場景下，這種配置會嚴重影響性能。
優化措施：
-- 臨時調整（立即生效）
mysql> SET GLOBAL sync_binlog=1000;
Query OK, 0 rows affected (0.00 sec)-- 驗證修改結果
mysql> SHOW VARIABLES LIKE '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1000 |
+---------------+-------+
1 row in set (0.00 sec)-- 持久化配置（修改my.cnf）
$ sudo vi /etc/mysql/my.cnf
[mysqld]
sync_binlog=1000-- 重啟MySQL服務使配置永久生效
$ sudo systemctl restart mysql調整后效果：
- 磁盤 IOPS 從 15000 降至 8000
- 寫入事務平均響應時間從 520ms 降至 85ms
2. innodb_flush_log_at_trx_commit 參數調整
原配置：
mysql> SHOW VARIABLES LIKE '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)? ? innodb_flush_log_at_trx_commit=1?表示每次事務提交都要將日志寫入磁盤，這進一步加重了 IO 負擔。
優化措施：
-- 臨時調整
mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)-- 驗證修改結果
mysql> SHOW VARIABLES LIKE '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
+--------------------------------+-------+
2 rows in set (0.00 sec)-- 持久化配置
$ sudo vi /etc/mysql/my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=2-- 重啟MySQL服務
$ sudo systemctl restart mysql調整后效果：
- 磁盤 IOPS 進一步降至 5500
- 寫入吞吐量提升 42%
四、其他優化建議
1.?日志文件位置調整
將 InnoDB 日志文件和 binlog 文件移動到專用 SSD 磁盤：
# 創建新的日志目錄
$ sudo mkdir -p /data/mysql/logs /data/mysql/binlog
$ sudo chown -R mysql:mysql /data/mysql# 修改my.cnf
$ sudo vi /etc/mysql/my.cnf
[mysqld]
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /data/mysql/logs
log-bin = /data/mysql/binlog/mysql-bin# 停止MySQL服務
$ sudo systemctl stop mysql# 復制現有日志文件
$ sudo cp -a /var/lib/mysql/ib_logfile* /data/mysql/logs/
$ sudo cp -a /var/lib/mysql/mysql-bin.* /data/mysql/binlog/# 修改文件權限
$ sudo chown -R mysql:mysql /data/mysql/logs
$ sudo chown -R mysql:mysql /data/mysql/binlog# 啟動MySQL服務
$ sudo systemctl start mysql# 驗證日志文件位置
$ sudo lsof -p $(pgrep mysqld) | grep -E 'ib_logfile|mysql-bin'
mysqld 12345 mysql mem REG 8,17 536870912 123456789 /data/mysql/logs/ib_logfile0
mysqld 12345 mysql mem REG 8,17 536870912 123456790 /data/mysql/logs/ib_logfile1
mysqld 12345 mysql 4u REG 8,17 12345 123456791 /data/mysql/binlog/mysql-bin.0000012. 生產環境核心參數配置模板
[mysqld]
# 事務日志同步策略
sync_binlog = 1000 # 每1000次提交刷盤（平衡性能與可靠性）
innodb_flush_log_at_trx_commit = 2 # 每秒刷盤一次（減少redo日志IO）# 內存與日志配置
innodb_buffer_pool_size = 8G # 緩沖池大小（建議為物理內存50-70%）
innodb_log_file_size = 512M # 單個日志文件大小（根據寫入量調整）
innodb_log_files_in_group = 2 # 日志文件數量
innodb_io_capacity = 2000 # IO能力上限（SSD建議2000-5000）
innodb_write_io_threads = 16 # 異步寫線程數
innodb_read_io_threads = 16 # 異步讀線程數3. 突發 IO 高負載應急響應方案
-- 突發IO高負載時臨時降低同步頻率
SET GLOBAL sync_binlog = 10000;
SET GLOBAL innodb_flush_log_at_trx_commit = 0;-- 查看當前線程狀態
SHOW FULL PROCESSLIST;-- 終止長時間運行的查詢
KILL 12345;五、風險提示
sync_binlog=1000意味著可能丟失最多 1000 個事務的 binlog 數據innodb_flush_log_at_trx_commit=2可能導致系統崩潰時丟失 1 秒內的事務- 建議在實施前進行壓測驗證，確保業務可接受數據丟失風險
六、總結
????????MySQL 磁盤 IO 高負載是生產環境常見問題，通過合理調整sync_binlog和innodb_flush_log_at_trx_commit參數，結合架構優化措施，可以顯著提升數據庫性能。本次優化實踐證明：
- 合理的參數調優可帶來 60% 以上的 IO 性能提升
- 批量操作優化能有效減少日志寫入次數
)
