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

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

原因

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

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

解决方法

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

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

终止问题查询。

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

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

操作步骤

登录DMS控制台后,在界面上单击登录数据库按钮,界面将弹出如下窗口。

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

选择性能 > 诊断报告。

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

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

单击查看报告,查看其中的 SQL 优化、会话列表、慢 SQL 汇总部分,如下列图片所示:

SQL 优化

会话列表

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

慢 SQL 汇总

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

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

注意:

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

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

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

终止问题查询

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

注意:

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

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

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

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

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

操作步骤

在 DMS 控制台上登录数据库。

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

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

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

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;

单击 Kill 会话,来终止问题查询。

通过命令终止问题查询

操作步骤

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

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

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

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

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

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

执行 kill  命令,终止相关会话。

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

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

操作步骤

在 DMS 控制台上登录数据库。

选择 SQL 操作 > SQL 窗口。

单击优化,获取诊断结果。

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

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

文章转载自:http://yun.jinre.com/newsinfo/781533.html

上一篇:MySQL CPU 使用率高的原因及解决方法
下一篇:MySQL CPU 使用率高的原因和解决方法