问题描述

云数据库 MySQL 在使用过程中，会遇到 CPU 利用率过高甚至达到 100% 的情况。本文将介绍造成该状况的常见原因以及解决方法，并通过 CPU 利用率为 100% 的典型场景，来分析引起该状况的原因及其相应的解决方案。

常见原因

CPU 使用率高可能由多种因素造成，比如业务系统执行应用提交查询（包括数据修改操作）时需要大量的逻辑读（逻辑 IO，执行查询所需访问的表的数据行数），导致系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。

说明：

大量行锁冲突，行锁等待或后台任务也有可能会导致实例的 CPU 利用率过高，但这些情况出现的概率低，本文不做讨论。

下文通过一个简化过的模型来说明系统资源、语句执行成本以及 QPS（Query Per Second 每秒执行的查询数）之间的关系。

条件：应用模型恒定（应用没有修改）。

avg_lgc_io：执行每条查询需要的平均逻辑 IO。

total_lgc_io：实例的 CPU 资源在单位时间内能够处理的逻辑 IO 总量。

关系公式：total_lgc_io = avg_lgc_io x QPS （单位时间 CPU 资源 = 查询执行的平均成本 x 单位时间执行的查询数量）

避免出现 CPU 利用率达到 100% 的一般原则：

• 按时关注 CPU 利用率监控，实例 CPU 利用率保证一定的冗余度。
• 应用设计和开发过程中，要考虑查询的优化，遵守 MySQL 优化的一般优化原则，降低查询的逻辑 IO，提高应用可扩展性。
• 新功能、新模块上线前，要利用生产环境数据进行压力测试。

典型示例

以 CPU 利用率为 100% 的典型场景为例，本实例介绍了两个引起该状况的原因及其解决方案，即应用负载（QPS）高和查询执行成本（查询访问表数据行数 avg_lgc_io）高。其中，由于查询执行成本高（查询访问表数据行数多）而导致实例 CPU 利用率高是 MySQL 的常见问题。

应用负载（QPS）高

现象描述：

• 特征：实例的 QPS（每秒执行的查询次数）高，查询比较简单、执行效率高、优化余地小。
• 表现：没有出现慢查询（或者慢查询不是主要原因），且 QPS 和 CPU 利用率曲线变化吻合。
• 常见场景：该状况常见于应用优化过的在线事务交易系统（例如订单系统）、高读取率的热门 Web 网站应用、第三方压力工具测试（例如 Sysbench）等。

解决方案：

• 对于由应用负载高导致的 CPU 利用率高的状况，利用 SQL 查询进行优化的余地不大，建议您从应用架构、实例规格等方面来解决，例如：

  • 升级实例规格，增加 CPU 资源。
  • 增加只读实例，将对数据一致性不敏感的查询（例如商品种类查询、列车车次查询）转移到只读实例上，分担主实例压力。
  • 利用首云云数据库 Redis 产品，尽量从缓存中获取常用的查询结果，减轻云数据库 MySQL 实例的压力。

• 对于查询数据比较静态、查询重复度高、查询结果集小于 1MB 的应用，考虑开启查询缓存（Query Cache）。

  注意：

  能否从开启查询缓存（Query Cache）中获益需要经过测试，具体设置请参见 云数据库 MySQL 查询缓存（Query Cache）的设置和利用。

• 定期归档历史数据、采用分库分表或者分区的方式减小查询访问的数据量。

• 尽量优化查询，减少查询的执行成本（逻辑 IO，执行需要访问的表数据行数），提高应用可扩展性。

查询执行成本（查询访问表数据行数 avg_lgc_io）高

现象描述：

• 特征：实例的 QPS（每秒执行的查询次数）不高；查询执行效率低、执行时需要扫描大量表中数据、优化余地大。
• 表现：存在慢查询，QPS 和 CPU 利用率曲线变化不吻合。
• 原因分析：由于查询执行效率低，为获得预期的结果即需要访问大量的数据（平均逻辑 IO 高），在 QPS 并不高的情况下（例如网站访问量不大），就会导致实例的 CPU 利用率高。

解决方案：

解决该状况的原则是：定位效率低的查询、优化查询的执行效率、降低查询执行的成本。通过 show processlist; 或 show full processlist; 命令查看当前执行的查询。

对于查询时间长、运行状态（state 列）是 sending data、copying to tmp table、copying to tmp table on disk、sorting result、using filesort等都可能是有性能问题的查询（SQL）。

注意：

• 若在 QPS 高导致 CPU 利用率高的场景中，查询执行时间通常比较短，show processlist; 命令或实例会话中可能会不容易捕捉到当前执行的查询。您可以执行命令 explain 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 101031643; 的命令来终止长时间执行的会话，终止会话请参见 云数据库 MySQL 如何终止会话。关于长时间执行会话的管理，请参见 云数据库 MySQL 管理慢查询。

附录

云数据库 MySQL 查询缓存（Query Cache）的设置和利用

1. 功能和适用范围

   功能：

   • 降低 CPU 利用率。
   • 降低 IOPS 使用率（某些情况下）。
   • 减少查询响应时间，提高系统的吞吐量。

   使用范围：

   • 表数据修改不频繁、数据较静态。
   • 查询（Select）重复度高。
   • 查询结果集小于 1 MB。

   说明：

   查询缓存并不一定带来性能上的提升，在某些情况下（比如查询数量大，但重复的查询很少）开启查询缓存会带来性能的下降。

2. 原理

   RDS for MySQL对来自客户端的查询（Select）进行Hash计算得到该查询的Hash值，通过该Hash值到查询缓存中匹配该查询的结果。

   如果匹配（命中），则将查询的结果集直接返回给客户端，不必再解析、执行查询（当开启了缓存查询才会涉及此条目，涉及两个参数分别是 query_cache_size 和 query_cache_type）。

   如果没有匹配（命中），则将Hash值和结果集保存在查询缓存中，以便以后使用。

   查询涉及的任何一个表中数据发生变化，RDS for MySQL将查询缓存中所有与该表相关的查询结果集全部释放（删除）。

3. 限制

   • 查询必须严格一致（大小写、空格、使用的数据库、协议版本、字符集等必须一致）才可以命中，否则视为不同查询。
   • 不缓存查询中的子查询结果集，仅缓存查询最终结果集。
   • 不缓存存储函数（Stored Function）、存储过程（Stored Procedure）、触发器（Trigger）、事件（Event）中的查询。
   • 不缓存含有每次执行结果变化的函数的查询，比如now()、curdate()、last_insert_id()、rand()等。
   • 不缓存对mysql、information_schema、performance_schema系统数据库表的查询。
   • 不缓存使用临时表的查询。
   • 不缓存产生告警（Warnings）的查询。
   • 不缓存Select … lock in share mode、Select … for update、 Select * from … where autoincrement_col is NULL类型的查询。
   • 不缓存使用用户定义变量的查询。
   • 不缓存使用 Hint - SQL_NO_CACHE 的查询。

4. 设置

   参数设置

   控制台参数设置如下。

   query_cache_type：是否开启查询缓存功能。

   取值为 0 ：关闭查询功能。

   取值为 1 ：开启查询缓存功能，但不缓存 Select SQL_NO_CACHE 开头的查询。

   取值为 2 ：开启查询缓存功能，但仅缓存 Select SQL_CACHE 开头的查询。

   

   说明：

   修改 query_cache_type 需要重启实例（修改后实例会自动重启）。

5. 验证效果

   • 控制台中查看CPU使用率

   • SQL 命令

     可以通过如下命令来获取查询缓存的使用状态。

     show global status like ‘Qca%’;
     

     • Qcache_hits：查询缓存命中次数。
     • Qcache_inserts：将查询和结果集写入到查询缓存中的次数。
     • Qcache_not_cached：不可以缓存的查询次数。
     • Qcache_queries_in_cache：查询缓存中缓存的查询量。

     查询结果如下：

     +-------------------------+------------+
     | Variable_name           | Value      |
     +-------------------------+------------+
     | Qcache_free_blocks      | 2763       |
     | Qcache_free_memory      | 10115160   |
     | Qcache_hits             | 365589713  |
     | Qcache_inserts          | 612280336  |
     | Qcache_lowmen_prunes    | 1257059    |
     | Qcache_not_cached       | 1805250864 |
     | Qcache_queries_in_cache | 9180       |
     | Qcache_total_blocks     | 22409      |
     +-------------------------+------------+
     

云数据库 MySQL 如何终止会话

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

   提示：

   云数据库 MySQL 实例在连接数已满的情况下，是无法通过 MySQL 命令行工具连接实例的。如果无法通过 MySQL 命令行工具连接，建议先在控制台的参数设置中将 wait_timeout 参数（单位秒）设置为比较小的值（比如 60），让云数据库 MySQL 实例主动关闭空闲时间超过 60 秒的连接，以便稍后可以通过 MySQL 命令行工具连接访问实例。

2. 通过如下命令查看当前会话情况，记录想要结束的会话 id。

   show processlist;
   

   系统显示类似如下:

   

3. 执行如下命令，结束会话。

   Kill [$ID];
   

   注意：

   [$ID] 为上一步记录的 id。

   系统显示类似如下：

   > kill 241;
   

云数据库 MySQL 管理慢查询

出现原因：

在使用云数据库 MySQL 的过程中，由于某些原因，例如被 SQL 注入、SQL 执行效率较差、DDL 语句引起表元数据锁等待等等，会出现运行时间很长的查询。

• 由于 SQL 执行效率差而导致的长时间查询。
• 由于被 SQL 注入而导致的长时间查询。

长时间执行的查询带来的问题：

通常来说，除非是 BI/报表类查询，否则长时间执行的查询对于应用缺乏意义，而且会消耗系统资源，比如大量长时间查询可能会引起 CPU、IOPS 和连接数过高等问题，导致系统不稳定。

如何避免长时间执行的查询

• 应用方面应注意增加防止 SQL 注入的保护措施。

• 在新功能模块上线前，进行压力测试，避免执行效率很差的 SQL 大量执行。

• 尽量在业务低峰期进行索引创建删除、表结构修改、表维护和表删除操作。