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

问题描述

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

问题原因

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

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

解决方案

阿里云提醒您：

• 如果您对实例或数据有修改、变更等风险操作，务必注意实例的容灾、容错能力，确保数据安全。
• 如果您对实例（包括但不限于ECS、RDS）等进行配置与数据修改，建议提前创建快照或开启RDS日志备份等功能。
• 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息，建议您及时修改。

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

• 生成实例当前诊断报告，推荐使用该方式。

• 终止问题查询。

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

生成实例当前诊断报告（推荐方式）

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

2. 选择性能>诊断报告。

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

   

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

   • SQL优化

     

   • 会话列表

     

   • 慢SQL汇总

     

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

   说明：

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

   • 建议在业务低峰期进行添加索引操作。如果添加过程中出现等待表元数据锁的情况，请参见如何解决MDL锁导致无法操作数据库的问题。

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个会话在执行以下SQL查询语句。

   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 full processlist;SQL语句来查询，结果如下图所示。

     

3. 执行kill [$ID]命令，终止相关会话。其中[$ID]为上述步骤查询结果中的ID列值。

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

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

2. 选择SQL 操作>SQL窗口。

3. 单击优化，获取诊断结果。

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

   

适用于

• 云数据库RDS MySQL版