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

  • A+
所属分类:MySQL
高性能企业级服务器首台5折

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

原因

  • 实例内存满足不了缓存数据或排序等需要,导致产生大量的物理 IO。
  • 查询执行效率低,扫描过多数据行。

解决方法

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

  • 生成实例当前诊断报告(推荐方式)。
  • 终止问题查询。
  • 通过 SQL 窗口优化功能来优化查询。

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

操作步骤

  1. 在 DMS 控制台上登录数据库
  2. 选择性能 > 诊断报告
  3. 单击发起诊断,即可创建一个针对当前实例运行情况的报告,如下图所示:实例诊断报告
  4. 单击查看报告,查看其中的 SQL 优化、会话列表、慢 SQL 汇总部分,如下列图片所示:
    • SQL 优化SQL 优化
    • 会话列表会话列表
    • 慢 SQL 汇总慢 SQL 汇总
  5. 根据 SQL 优化中的建议,做出相应优化。如上述步骤中的建议 
    1
    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. 选择性能 > 实例会话,显示结果如下图所示:DMS 查看执行的查询从上图可以看出,有 10 个会话在执行下面这个查询:
    1. 1
      <span class="kwd"><span class="hljs-keyword">select</span></span><span class="pln"> b</span><span class="pun">.*</span> <span class="kwd"><span class="hljs-keyword">from</span></span><span class="pln"> perf_test_no_idx_01 a</span><span class="pun">,</span><span class="pln"> perf_test_no_idx_02 b </span><span class="kwd"><span class="hljs-keyword">where</span></span><span class="pln"> a</span><span class="pun">.</span><span class="pln">created_on</span><span class="pun">&gt;=</span> <span class="str"><span class="hljs-string">'2015-01-01'</span></span> <span class="kwd"><span class="hljs-keyword">and</span></span><span class="pln"> a</span><span class="pun">.</span><span class="pln">detail</span><span class="pun">=</span><span class="pln"> b</span><span class="pun">.</span><span class="pln">detail</span><span class="pun">;</span>
  3. 单击 Kill 会话,来终止问题查询。

通过命令终止问题查询

操作步骤

  1. 通过 MySQL 命令行工具连接实例。
  2. 通过如下方式,查看会话情况,来确定问题会话。
    • 通过执行 
      1
      show processlist;

       命令查询,结果如下图所示:show processlist

    • 若当前执行会话比较多,通过执行 
      1
      show full processlist;

       命令来查询,结果如下图所示:show full processlist

  3. 执行 
    1
    kill &lt;Id&gt;

     命令,终止相关会话。参数说明:Id 是上述步骤查询结果中的 Id 列值。

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

操作步骤

  1. 在 DMS 控制台上登录数据库
  2. 选择 SQL 操作 > SQL 窗口
  3. 单击优化,获取诊断结果。
  4. 根据诊断结果中的建议,来优化查询,如下图所示:优化窗口

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: