磁碟util%使用率100%。首先懷疑是慢查詢語句導致，檢視MYSQL慢查詢情況：
mysql> show processlist;
+-------+-------------+---------------------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+---------------------+------+---------+--------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 674754 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 5504 | root | 192.168.5.161:38765 | wbdb | Sleep | 1 | | NULL |
| 5746 | root | 192.168.5.161:40800 | wbdb | Sleep | 1 | | NULL |
| 5891 | root | 192.168.5.161:41970 | wbdb | Sleep | 1 | | NULL |
| 7398 | root | 192.168.5.161:46172 | wbdb | Sleep | 1 |
並未發現有明顯慢查詢語句，繼續往下分析，我的伺服器是64G記憶體，用系統命令 free 看了下，發現大部分都在cached，而free的也不多。觀察InnoDB相關的配置以及status，看能不能找到端倪：
mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+ |
| innodb_buffer_pool_size | 48169484288 |
+-------------------------------------+----------------+
1 row in set (0.00 sec)
mysql> show global status like '%innodb_log%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 68744008 |
| Innodb_log_writes | 72154542 |
+---------------------------+----------+
3 rows in set (0.00 sec)
mysql> show global status like '%innodb_buffer_pool_wait%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+-------+
1 row in set (0.00 sec)
並未發現日誌相關等待時間，檢視了下show engine innodb status的報告也未發現有任何異常，top看系統負載並不高，唯獨磁碟IO高，並且mysql記憶體分配也沒有異常，努力回憶了一下mysql與磁碟IO相關的引數，想起來有兩個引數innodb_flush_log_at_trx_commit 和sync_binlog，看下這兩個引數的值：
mysql> show variables like '%sync_bin%';
+--------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%innodb_flush%';
+--------------------------------+----------+
| Variable_name | Value |
+--------------------------------+----------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+----------+
5 rows in set (0.00 sec)
到這裡問題基本上就已經知道大概了，sync_binlog=1表示每次事務提交後MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟，頻繁的寫盤導致磁碟IO居高不下，將sync_binlog調整為500
mysql> set global sync_binlog=500;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 500 |
+---------------+-------+
1 row in set (0.00 sec)
再次觀察磁碟IO使用情況：
[root@hbwb-008 ~]# iostat -dxk 1
Linux 2.6.32-431.el6.x86_64 (hbwb-008) 03/29/2017 _x8664 (32 CPU)
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.01 38.92 0.42 35.17 6.43 300.15 17.23 0.06 1.63 4.36 15.52
sdb 0.00 51.45 0.33 33.19 26.97 338.58 21.81 0.01 0.27 0.07 0.23
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 28.00 0.00 13.00 0.00 188.00 28.92 0.08 6.15 6.15 8.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 19.00 0.00 7.00 0.00 104.00 29.71 0.04 5.00 4.43 3.10
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 25.00 0.00 3.00 0.00 112.00 74.67 0.01 5.00 5.00 1.50
sdb 0.00 1.00 0.00 2.00 0.00 12.00 12.00 0.00 0.00 0.00 0.00
可以看到磁碟IO顯著降低，報警解除。
本作品採用《CC 協議》，轉載必須註明作者和本文連結