今天在spotlight上看到磁盘IO过高的报警，登录服务器查看具体磁盘IO情况：

[root@hbwb-008 ~]# iostat -dxk 1

磁盘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  _x86_64_    (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显著降低，报警解除。

sync_binlog参数：
sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。