RDS MySQL的I/O性能受硬件层存储介质、软件层数据库内核架构和具体SQL语句(扫描或修改数据量)的影响。本文介绍实例I/O高的原因和解决方案。

存储类型

RDS MySQL的存储类型有2种:

  • 本地SSD盘

    本地SSD盘拥有最低的I/O延迟,但是本地SSD盘的存储大小有限,如果数据增多,本地空间不够时,需要迁移数据到其他的主机,时间较长且切换时会有闪断。

  • 云盘(分布式存储)

    云盘包括SSD云盘和ESSD云盘,云盘拥有更高的性价比,支持更大的存储空间,扩容速度快且不需要迁移数据。

存储类型的更多信息,请参见存储类型

高吞吐导致实例I/O高

  • 现象

    如果表上有很多索引或大字段,频繁地更新、删除、插入,读取数据和刷新脏页时会有大量的I/O。

    您可以在控制台的自治服务 > 性能趋势页面,单击性能趋势页签,查看读写负载情况。

    读写吞吐量Row
  • 解决方案

    建议降低读写频率或升级实例规格、优化刷新脏页相关的参数来解决高吞吐问题。和刷新脏页相关的参数如下:

    • innodb_max_dirty_pages_pct:缓冲池中允许的脏页百分比,默认值为75。
    • innodb_max_dirty_pages_pct_lwm:脏页比例的低水位线。当缓冲池里的脏页比例超过这个低水位线时,能够触发脏页预刷功能,逐步控制脏页比例。默认值为0,表示禁用该功能。
      说明 innodb_max_dirty_pages_pct_lwm的值不能大于innodb_max_dirty_pages_pct的值,否则会强制修改为与innodb_max_dirty_pages_pct相同。
    • innodb_io_capacity:设置InnoDB后台任务每秒执行的I/O操作数的上限,影响刷新脏页和写入缓冲池的速率。默认值为20000。
    • innodb_io_capacity_max:如果刷新操作过于落后,InnoDB可以超过innodb_io_capacity的限制进行刷新,但是不能超过本参数的值。默认值为40000。

临时表导致实例I/O高

  • 现象

    如果临时目录很大,可能存在慢SQL排序、去重等操作导致创建很大的临时表。临时表写入也会造成I/O增加。

    您可以在控制台的自治服务 > 性能趋势页面,单击性能趋势页签,查看tmp或other目录大小。

    tmp
  • 解决方案

    建议进行SQL优化,避免慢SQL。数据库自治服务DAS提供自助SQL优化功能,具体操作,请参见SQL优化

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

  • 现象

    如果SQL查询或修改的数据不在缓冲池(Buffer Pool),则需要从存储中读取,可能会产生大量的I/O吞吐。

    您可以在控制台的自治服务 > 性能趋势页面,单击性能趋势页签,查看Buffer Pool命中率。

    Buff Pool
  • 解决方案

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

DDL语句导致实例I/O高

  • 现象

    DDL语句可能会重建表空间,期间会扫描全表数据、创建索引排序、刷新新表产生的脏页,这些都会导致大量的I/O吞吐。另外一种场景是删除大表造成的I/O抖动。

    您可以在控制台的监控与报警页面,单击标准监控页签内的资源监控,可以查看实例的磁盘空间和IOPS信息。

    磁盘空间IOPS
  • 解决方案

    可以使用阿里云自研内核AliSQL提供的异步删除大文件功能解决问题,更多信息,请参见Purge Large File Asynchronously

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

  • 现象

    事务只有在提交时才会写Binlog文件,如果存在大事务,例如一条Delete语句删除大量的行,可能会产生几十GB的Binlog文件,Binlog文件刷新到磁盘时,会造成很高的I/O吞吐。

  • 解决方案

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

附:InnoDB I/O系统介绍

InnoDB通过一套独立的I/O系统来处理数据页的读取和写入,如果SQL请求的数据页不在Buffer Pool中,会产生物理I/O,需要读写底层存储的数据:

  • 读数据页操作

    通过同步I/O实现,同步I/O调用底层的读接口。

  • 写数据页操作

    通过异步I/O实现,例如后台线程刷新脏页,后台I/O线程会异步的将脏页刷到磁盘。

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