关系数据库MySQL版解决I/O高问题

本文介绍了InnoDB I/O系统，MySQL实例I/O高的一些原因以及对应的解决方案。

MySQL的I/O性能受实例硬件层存储介质(普通IO，高IO，超高IO云盘，本地SSD等)、和业务层具体SQL语句（扫描或修改数据量）的影响。

InnoDB I/O系统介绍

为了管理磁盘文件的读写操作，InnoDB设计了一套文件IO操作接口，提供了同步IO和异步IO两种文件读写方式，通过一套独立的I/O系统来处理数据页的读取和写入，如果SQL请求的数据页不在Buffer Pool中，会产生物理I/O，需要读写文件系统存储的数据：

• 读数据页操作

  通常用户线程触发的数据块请求读是同步读，通过同步I/O实现，同步I/O调用底层的读接口。

• 写数据页操作

  写数据页默认受double write buffer保护，因此对double write buffer的写磁盘为同步写，而对数据文件的写入为异步写。通过异步I/O实现，例如后台线程刷新脏页，后台I/O线程会异步的将脏页刷到磁盘。

除了对普通数据文件的读写I/O操作，写Redo日志、写Undo日志、写Binlog日志、排序磁盘临时表、重建DDL表空间等也会造成大量I/O。

高吞吐导致实例I/O高

• 现象

如果业务并发大，TPS/QPS很高，表中又有很多索引或大字段，频繁地更新、删除、插入，读取数据和刷新脏页时会有大量的I/O。

您可以在实例监控指标页面查看读写负载情况。

• 解决方案

建议修改当前实例参数降低磁盘读写频率、优化刷新脏页相关的参数或升级实例规格来解决高吞吐问题。MySQL刷新脏页策略主要由以下等参数控制：

innodb_max_dirty_pages_pct：缓冲池中允许的脏页百分比，默认值是75，当脏页所占百分比超过这个值，innoDB就会进行写操作以把页中的已更新数据写入到磁盘文件中，增加innodb_max_dirty_pages_pct会降低写操作，节省部分IO。

innodb_io_capacity：设置InnoDB后台任务每秒执行的I/O操作数的上限，影响刷新脏页和写入缓冲池的速率，在磁盘I/O性能不足时，如果参数值过大就会造成I/O阻塞，并且造成InnoDB引擎性能较大的降低，不同磁盘类型默认值不同。

innodb_io_capacity_max：如果刷脏操作过于落后，超过缓冲池中允许的脏页最大百分比，InnoDB可以超过innodb_io_capacity的限制进行刷新，但是不能超过本参数的值。

如果调节后，查看监控项IO使用率任然保持较高，建议扩容实例内存或者升级实例为高IOPS磁盘类型。

临时表导致实例I/O高

• 现象

通常情况下MySQL产生的临时文件很小，同时也会及时释放，但是一些异常情况下，比如慢查询，会导致短时间产生大量的临时文件。如果临时目录很大，可能存在慢SQL排序、去重等操作导致创建很大的临时表。临时表写入也会造成I/O增加。您可以在实例监控指标页面查看临时表创建情况。

• 解决方案

在控制台的慢日志页面，下载并查看执行缓慢的SQL，通过分析慢SQL的执行耗时和执行计划，优化对应的SQL。

读取冷数据导致实例I/O高

• 现象

  关系型数据库MySQL版采用的是buffer pool缓存架构，SQL访问或者修改的数据行，不在buffer pool中命中，就需要到文件系统中读取物理文件，这时会产生大量的读IO，如果buffer pool空闲页不够，还需要交换部分脏页数据到磁盘，同时又会产生大量的写IO。您可以在实例监控指标页面查看缓冲池命中率。

• 解决方案

  根据业务场景重新设计业务缓存策略，或者升级实例规格。

大事务写Binlog导致实例I/O高

• 现象

  关系数据库MySQL版在事务提交时候需要写binlog，默认binlog日志为行格式，当出现大事务时候，比如一个大表一次delete大量数据，这时候会产生大量的binlog日志，可能达到几十G甚至更多，binlog文件需要刷盘，造成突然很高的磁盘IO使用率。

• 解决方案

  建议尽量将大事务拆分为小事务，避免大事务和降低刷新磁盘频率。