全部产品
云市场

MySQL IOPS 使用率高的原因和解决方法

更新时间:2019-10-08 09:46:15

MySQL 实例在日常使用中会出现实例 IOPS 使用率高的情况,本文将介绍造成该状况的主要原因和解决方法。

原因

  • 实例内存满足不了缓存数据或排序等需要,导致产生大量的物理 IO。

  • 查询执行效率低,扫描过多数据行。

解决方法

您可以通过如下任意一种途径或结合使用两种途径来解决 IOPS 使用率高的的问题:

  • 生成实例当前诊断报告(推荐方式)。

  • 终止问题查询。

  • 通过 SQL 窗口优化功能来优化查询。

生成实例当前诊断报告(推荐方式)

操作步骤

  1. 在 DMS 控制台上登录数据库

  2. 选择性能 > 诊断报告

  3. 单击发起诊断,即可创建一个针对当前实例运行情况的报告,如下图所示。

    发起诊断

  4. 单击查看报告,查看其中的会话列表、慢SQL汇总部分,如下所示。

    SQL列表

  5. 根据 SQL 优化中的建议,做出相应优化。如上述步骤中的建议 ALTER TABLE large_tab_03 ADD INDEX IDX_COL01(COL01) ,即为在表 large_tab_03 字段 col01 上添加索引 idx_col01(col01)。

    注意:

    • 在 SQL 优化没有直接给出建议的情况下,需要结合会话列表和慢 SQL 汇总的信息进行查询优化。

    • 建议在业务低峰期进行添加索引操作。如果添加过程中出现等待表元数据锁的情况,请参见 RDS MySQL 表上 Metadata lock 的产生和处理

  6. 索引添加完毕后,查询执行就会快速完成,IOPS 使用率即会降低。

终止问题查询

您可以通过 DMS 控制台上的实例会话或命令查询和终止问题查询,建议您将物理读(Physical_sync_read 和 Physical_async_read)高的查询终止掉。

注意:

  • RDS 实例在连接数打满的情况下,无法通过 DMS 或者 MySQL 命令行工具连接登录实例。

  • 如果无法通过 DMS 或 MySQL 命令行工具连接,建议您先在 RDS 控制台的参数设置中将 wait_timeout 参数(单位秒)设置为比较小的值(比如 60),让 RDS 实例主动关闭空闲时间超过 60 秒的连接,以便稍后可以通过 DMS 或者 MySQL 命令行工具连接访问实例。

通过实例会话终止问题查询

在实例诊断报告不可用或者无法立刻使用其建议的情况下,可以先通过 DMS 控制台上的实例会话来终止问题查询。

注意:需要应用方面首先停止提交问题查询,否则会出现不断终止、不断出现的情况。

操作步骤
  1. 在 DMS 控制台上登录数据库

  2. 选择性能 > 实例会话,显示结果如下图所示。

    实例会话

    从上图可以看出,有 10 个会话在执行下面这个查询:

    1. select b.* from perf_test_no_idx_01 a, perf_test_no_idx_02 b where a.created_on>= ‘2015-01-01’ and a.detail= b.detail;
  3. 单击 Kill 会话,来终止问题查询。

通过命令终止问题查询

操作步骤

  1. 通过 MySQL 命令行工具连接实例。

  2. 通过如下方式,查看会话情况,来确定问题会话。

    • 通过执行 show processlist; 命令查询,结果如下图所示。

      show processlist

    • 若当前执行会话比较多,通过执行 show full processlist; 命令来查询,结果如下图所示。

      show full processlist

  3. 执行 kill <Id> 命令,终止相关会话。

    参数说明:Id 是上述步骤查询结果中的 Id 列值。

通过 SQL 窗口优化功能来优化查询

操作步骤

  1. 在 DMS 控制台上登录数据库

  2. 选择 SQL 操作 > SQL 窗口

  3. 单击SQL诊断,获取诊断结果。

  4. 根据诊断结果中的建议,来优化查询,如下图所示。

    SQL优化

若问题还未解决,请联系售后技术支持