mysql磁盘io高是什么造成_Mysql 磁盘IO过高

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

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

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同步到磁盘中去。