排查 Azure Database for MySQL 灵活服务器中的 CPU 利用率过高问题

适用于：Azure Database for MySQL - 单一服务器 Azure Database for MySQL - 灵活服务器

Azure Database for MySQL 灵活服务器提供一系列指标，你可以使用这些指标来识别服务器上的资源瓶颈和性能问题。 若要确定服务器的 CPU 使用率是否过高，请监视“主机 CPU 百分比”、“总连接数”、“主机内存百分比”和“IO 百分比”等指标。 有时，查看这些指标的组合可以深入了解可能导致 Azure Database for MySQL 灵活服务器实例上 CPU 利用率增长的原因。

例如，考虑连接突然激增，引起启动数据库查询激增，从而导致 CPU 使用率上升。

除了捕获指标外，还必须跟踪工作负荷，以了解一个或多个查询是否导致 CPU 使用率激增。

CPU 使用率高的原因

CPU 峰值的出现原因多种多样，其多是在连接达到峰值和 SQL 查询编写不当，或这两者的共同作用下出现：

连接达到峰值

连接增加会导致线程增加，这反过来又会导致 CPU 使用率提高，因为它必须管理这些连接及其查询和资源。 若要排查连接达到峰值问题，应检查连接总数指标，并参阅下一部分，详细了解这些连接。 可以使用 performance_schema 并通过以下命令，识别当前连接到服务器的主机和用户：

当前连接的主机

   select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
   where CURRENT_CONNECTIONS > 0
   and host not in ('NULL','localhost');

当前连接的用户

   select USER,CURRENT_CONNECTIONS from performance_schema.users
   where CURRENT_CONNECTIONS >0
   and USER not in ('NULL','azure_superuser');

编写不当的 SQL 查询

在没有索引的情况下执行和扫描大量行的查询成本高昂，或者那些与其他低效计划一起执行临时排序的查询，都可能导致 CPU 峰值。 虽然某些查询可能在单个会话中快速执行；但当在多个会话中运行时，它们可能会导致 CPU 峰值。 因此，请务必始终解释从 show processlist 命令中捕获的查询，并确保其执行计划的高效。 确保通过使用筛选器/ where 子句扫描最少数量的行，利用索引并避免与其他不良执行计划一起使用大型临时排序，这样即可确保实现此目的。 有关执行计划的详细信息，请参阅 EXPLAIN 输出格式。

捕获当前工作负荷的详细信息

SHOW (FULL) PROCESSLIST 命令显示当前连接到 Azure Database for MySQL 灵活服务器实例的所有用户会话的列表。 它还提供有关每个会话的当前状态和活动的详细信息。

此命令仅生成当前会话状态的快照，并且不提供有关历史会话活动的信息。

让我们看看运行此命令的示例输出。

mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id    | User             | Host               | db            | Command     | Time   | State                       | Info                                     |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
|     1 | event_scheduler  | localhost          | NULL          | Daemon      |     13 | Waiting for next activation | NULL                                     |
|     6 | azure_superuser  | 127.0.0.1:33571    | NULL          | Sleep       |    115 |                             | NULL                                     
|
| 24835 | adminuser        | 10.1.1.4:39296     | classicmodels | Query       |      7 | Sending data                | select * from classicmodels.orderdetails;|
| 24837 | adminuser        | 10.1.1.4:38208     | NULL          | Query       |      0 | starting                    | SHOW FULL PROCESSLIST                    |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)

请注意，客户所有的用户“adminuser”拥有两个会话，这两个会话都来自同一 IP 地址：

• 会话 24835 在过去 7 秒内一直在执行 SELECT 语句。
• 会话 24837 正在执行“显示完整进程列表”语句。

如有必要，可能需要终止查询，例如导致生产工作负荷 CPU 使用率激增的报告或 HTAP 查询。 但是，在尝试减少 CPU 使用率之前，请始终考虑终止查询的潜在后果。 如果发现任何长时间运行的查询导致 CPU 峰值，请优化这些查询，以便优化资源。

详细的当前工作负荷分析

你需要使用至少两个信息源来获取有关会话、事务和查询状态的准确信息：

• 来自 INFORMATION_SCHEMA.PROCESSLIST 表的服务器进程列表，也可以通过运行 SHOW [FULL] PROCESSLIST 命令进行访问。
• 来自 INFORMATION_SCHEMA.INNODB_TRX 表的 InnoDB 事务元数据。

只有其中一个源的信息，无法描述连接和事务状态。 例如，进程列表不会通知你是否有与任何会话关联的开放事务。 另一方面，事务元数据不显示会话状态和在该状态的时间。

以下示例查询将进程列表信息与 InnoDB 事务元数据的某些重要部分相结合：

mysql> select    p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state,    substring(p.info, 1, 50) as info,    t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level   from information_schema.processlist p    left join information_schema.innodb_trx t    on p.id = t.trx_mysql_thread_id \G

以下示例显示此查询的输出：

*************************** 1. row *************************** 
        session_id: 11 
               user: adminuser 
               host: 172.31.19.159:53624 
                 db: NULL 
            command: Sleep 
               time: 636 
              state: cleaned up 
               info: NULL 
        trx_started: 2019-08-01 15:25:07 
    trx_age_seconds: 2908 
  trx_rows_modified: 17825792 
trx_isolation_level: REPEATABLE READ 
*************************** 2. row *************************** 
         session_id: 12 
               user: adminuser 
               host: 172.31.19.159:53622 
                 db: NULL 
            command: Query 
               time: 15 
              state: executing 
               info: select * from classicmodels.orders 
        trx_started: NULL 
    trx_age_seconds: NULL 
  trx_rows_modified: NULL 
trx_isolation_level: NULL

下表列出了对此信息的分析（按会话）。

请注意，如果会话报告为空闲状态，则不再执行任何语句。 此时，会话已完成任何以前的工作，并正在等待来自客户端的新语句。 但是，空闲会话仍会导致一些 CPU 消耗和内存使用。

列出打开的事务

以下查询的输出提供了当前针对数据库服务器运行的所有事务的列表（按事务开始时间的顺序排列），以便可以轻松识别是否存在任何超出预期运行时间的事务和正在阻塞的事务。

SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;

了解线程状态

在执行期间导致 CPU 使用率较高的事务可能具有各种状态的线程，如以下部分所述。 使用此信息可以更好地了解查询生命周期和各种线程状态。

检查权限/打开表

此状态通常意味着打开表操作需要很长时间。 通常，可以增加表缓存大小以改善问题。 但是，表打开缓慢也可能指示其他问题，例如在同一数据库中具有过多的表。

发送数据

虽然此状态可能意味着线程正在通过网络发送数据，但它也可以指示查询正在从磁盘或内存中读取数据。 此状态可能是由顺序表扫描引起的。 应检查 innodb_buffer_pool_reads 和 innodb_buffer_pool_read_requests 的值，以确定是否有大量页面从磁盘进入内存中。 有关详细信息，请参阅排查 Azure Database for MySQL 灵活服务器中的内存不足问题。

更新

此状态通常意味着线程正在执行写入操作。 检查性能监视器中的 IO 相关指标，以便更好地了解当前会话执行的操作。

等待 <lock_type> 锁定

此状态指示线程正在等待第二个锁定。 在大多数情况下，可能是元数据锁。 应查看所有其他线程，并查看谁正在锁定。

了解和分析等待事件

了解 MySQL 引擎中的基础等待事件非常重要，因为数据库中长时间等待或大量等待可能会导致 CPU 使用率增加。 以下示例显示了相应的命令和示例输出。

SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
 Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event                           | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog              |            7090 | 255.54 s        | 36.04 ms      |
| wait/io/file/innodb/innodb_log_file  |           17798 | 55.43 s         | 3.11 ms       |
| wait/io/file/innodb/innodb_data_file |          260227 | 39.67 s         | 0.15 ms       |
| wait/io/table/sql/handler            |         5548985 | 11.73 s         | 0.00 ms       |
| wait/io/file/sql/FRM                 |            1237 | 7.61 s          | 6.15 ms       |
| wait/io/file/sql/dbopt               |              28 | 1.89 s          | 67.38 ms      |
| wait/io/file/myisam/kfile            |              92 | 0.76 s          | 8.30 ms       |
| wait/io/file/myisam/dfile            |             271 | 0.53 s          | 1.95 ms       |
| wait/io/file/sql/file_parser         |              18 | 0.32 s          | 17.75 ms      |
| wait/io/file/sql/slow_log            |               2 | 0.05 s          | 25.79 ms      |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)

限制 SELECT 语句执行时间

如果不知道涉及 SELECT 查询的数据库操作的执行成本和执行时间，任何长期 SELECT 都可能导致数据库服务器的不可预测性或波动性。 根据基础数据集的增长，语句和事务的大小以及关联的资源利用率继续增长。 由于这种无限的增长，最终用户语句和事务需要更长的时间，消耗的资源越来越多，直到它们压倒数据库服务器。 使用无限制的 SELECT 查询时，建议配置 max_execution_time 参数，以便中止超出此持续时间的任何查询。

建议

• 确保数据库分配了足够的资源来运行查询。 有时，可能需要纵向扩展实例大小，以获得更多的 CPU 核心以适应工作负荷。
• 通过将大型或长时间运行的事务分解为较小的事务来避免这些事务。
• 尽可能在只读副本服务器上运行 SELECT 语句。
• 使用“主机 CPU 百分比”上的警报，以便在系统超过任何指定阈值时收到通知。
• 使用 Query Performance Insight 或 Azure 工作簿来识别任何有问题或运行缓慢的查询，然后对其进行优化。
• 对于生产数据库服务器，请定期收集诊断信息，以确保一切顺利运行。 如果不是，请排查并解决你识别的任何问题。

后续步骤

若要查找同行对你的最重要问题的回答，或者要发布或回答问题，请访问 Stack Overflow。