MySQL 磁盘 IO 过高

磁盘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 协议》，转载必须注明作者和本文链接