优化实例中的高 CPU 用量

实例中的 CPU 利用率高可能是由多种原因造成的，例如工作负载增加、事务繁重、查询速度缓慢和事务运行时间过长。

预配不足的实例 Recommender 会分析 CPU 利用率。如果 CPU 利用率在过去 30 天内有大量时间达到或超过 95%，Recommender 会向您发出提醒，并提供更多数据洞见来帮助您解决问题。

本文档介绍了如何在 Cloud SQL for MySQL 实例被预配不足的实例 Recommender 识别为 CPU 利用率高时查看和优化该实例。

使用 Query Insights 来识别 CPU 耗用量较高的查询

Query Insights 可帮助您检测、诊断和避免可能导致 Cloud SQL 数据库 CPU 消耗过高的查询性能问题。

使用 MySQL 数据库审核

使用 MySQL 数据库审核查看实例的内存和消耗情况。

建议

CPU 利用率会随着工作负载的增加而成比例提高。如需降低 CPU 利用率，请检查正在运行的查询并对其进行优化。以下是检查 CPU 使用情况的几个步骤。

  1. 勾选 Threads_running 和 Threads_connected

    使用以下查询查看活跃线程数：

    > SHOW STATUS like 'Threads_%';

    Threads_running 是 Threads_connected 的子集。其余线程为空闲状态。Threads_running 的增加会导致 CPU 利用率提高。建议您检查这些线程上正在运行的内容。

  2. 检查查询状态

    运行 SHOW PROCESSLIST 命令可查看正在进行的查询。该命令会按顺序返回所有已连接的线程以及它们正在运行的 SQL 语句。

    mysql> SHOW [FULL] PROCESSLIST;

    注意状态列和时长列。检查是否有许多查询卡在同一个状态。

    • 如果许多线程显示 Updating，则可能发生了记录锁争用。请参阅下一步。
    • 如果许多线程显示表元数据锁为 Waiting，请检查查询以了解是什么表，然后查找可能占用元数据锁的 DDL（例如 ALTER TABLE）。如果较早的查询（例如长时间运行的 SELECT query）占用表元数据锁，DDL 也可能会等待表元数据锁。

  3. 检查是否存在记录锁争用

    当事务持有热门索引记录锁时，它们会屏蔽请求相同锁的其他事务。这可能会形成连锁反应，导致一些请求卡住，并导致 Threads_running 的值增加。如需诊断锁争用情况，请使用 information_schema.innodb_lock_waits 表。

    以下查询列出了每个屏蔽事务以及关联的已屏蔽事务的数量。

    SELECT
  t.trx_id,
  t.trx_state,
  t.trx_started,
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM
  information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

    单一大型 DML 和多个并发小型 DML 都可能会导致行锁争用。 您可以按照以下步骤从应用端对此进行优化：

    • 避免长事务，因为在事务结束之前，行锁会一直被占用。
    • 将单个大型 DML 拆分为微型 DML。
    • 将单个行 DML 批处理为小块。
    • 尽可能减少线程之间的争用；例如，如果应用代码使用连接池，请为同一线程分配 ID 范围。

  4. 查找长时间运行的事务

    • 使用 SHOW ENGINE INNODB STATUS

      在事务部分中，您可以查看按最早到最旧顺序排列的所有未结事务。

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      从最早的事务开始，寻找以下问题的答案：

      • 这些事务运行了多长时间？
      • 有多少个锁结构体和行锁？
      • 有多少条撤消日志条目？
      • 连接的主机和用户是谁？
      • 持续运行的 SQL 语句是什么？

    • 使用 information_schema.innodb_trx

      如果 SHOW ENGINE INNODB STATUS 被截断，则检查所有未结事务的另一种方法是使用 information_schema.innodb_trx 表：

      SELECT
 trx_id, trx_state,
 timestampdiff(second, trx_started, now()) AS active_secs,
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked,
 trx_lock_structs,
 trx_rows_locked,
 trx_rows_modified,
 trx_query
FROM information_schema.innodb_trx

    如果事务显示当前的长时间运行的语句，您可以决定停止这些事务以减轻服务器的压力，也可以等待关键事务完成。如果较早的事务未显示任何活动，请执行下一步来查找事务历史记录。

  5. 检查长时间运行的事务的 SQL 语句

    • 使用 performance_schema

      如需使用 performance_schema，您必须先将其开启。此更改需要重启实例。 开启 performance_schema 后，检查以确保插桩和使用方已启用：

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      如果尚未启用，请将它们启用：

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      默认情况下，每个线程都会保留 performance_schema_events_statements_history_size 定义的最后 10 个事件。 这些信息通常足以帮助您在应用代码中找到相应事务。此参数不是动态的。

      使用 mysql thread id（即 processlist_id）查询历史记录事件：

      SELECT
 t.thread_id,
 event_name,
 sql_text,
 rows_affected,
 rows_examined,
 processlist_id,
 processlist_time,
 processlist_state
FROM events_statements_history h
INNER JOIN threads t
ON h.thread_id = t.thread_id
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • 使用慢速查询日志

      为了进行调试，您可以将耗时超过 N 秒的所有查询捕获到慢查询日志中。如需启用慢速查询日志，您可以在Trusted Cloud 控制台的实例页面上或使用 gcloud CLI 修改实例设置，然后使用Trusted Cloud 控制台中的日志查看器或 gloud CLI 来查看日志。

  6. 检查信号量争用

    在并发环境中，共享资源上的互斥量和读写锁可能会成为争用点，从而降低服务器性能。此外，如果信号量等待时间超过 600 秒，系统可能会崩溃以摆脱停滞状态。

    如需查看信号量争用，请使用以下命令：

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    在每次等待信号量时，第一行会显示正在等待的线程、特定信号量及其等待的时长。如果在反复运行 SHOW ENGINE INNODB STATUS 时出现频繁的信号量等待，尤其是超过几秒的等待，则表示系统遇到了并发瓶颈。

    不同的工作负载和配置中有不同的争用点。

    如果信号量经常在 btr0sea.c 上，那么自适应哈希索引可能是争用的源头。请尝试使用 Trusted Cloud 控制台或 gcloud CLI 将其停用。

  7. 优化长 SELECT 查询

    首先，查看查询。识别查询的目标以及获取结果的最佳方式。最佳的查询计划应该能够尽可能减少数据访问。

    • 检查查询执行计划：
    mysql> EXPLAIN <the query>;

    请参阅 MySQL 文档，了解如何解析输出和评估查询效率。

    • 使用正确的索引

    检查键列，看看是否使用了预期的索引。如果没有，请更新索引统计信息：

    mysql> analyze table <table_name>

    增加用于计算索引统计信息的示例页面的数量。如需了解详情，请参阅 MySQL 文档。

    • 充分利用索引

    使用多列索引时，请检查 key_len 列，以查看是否已充分利用索引来过滤记录。最左侧的列需要进行相等比较，并且索引可用于第一个范围条件（包含第一个范围条件）。

    • 使用优化器提示

    确保使用正确索引的另一种选项是应用索引提示和表联接顺序提示。

  8. 使用 READ COMMITTED 避免长历史记录列表

    历史记录列表是指撤消表空间中未清除的事务列表。 事务的默认隔离级别为 REPEATABLE READ，这要求事务在其整个存续周期内读取同一快照。因此，SELECT 查询会阻止清除自查询（或事务）开始以来生成的撤消日志记录。因此，长历史记录列表会降低查询性能。避免构建长历史记录列表的一种方法是将事务隔离级别更改为 READ COMMITTED。使用 READ COMMITTED 时，无需再保留历史记录列表以保持一致的读取视图。您可以全局更改所有会话、单个会话或下一个单个事务的事务隔离级别。如需了解详情，请参阅 MySQL 文档。

  9. 调整服务器配置

    关于服务器配置有很多话要说。虽然完整案例超出了本文档的范围，但值得注意的是，服务器还会报告各种状态变量，这些状态变量可提示相关配置的运行状况。例如：

    • 如果 Threads_created/Connections 较大，请调整 thread_cache_size。适当的线程缓存可缩短线程创建时间，并有利于高度并发的工作负载。
    • 如果 Table_open_cache_misses/Table_open_cache_hits 不繁琐，请调整 table_open_cache。将表放在表缓存中可节省查询执行时间，并且在高度并发的环境中尤为重要。

  10. 终止不需要的连接

    如果查询似乎无效或不再需要，您可以停止查询。如需了解如何识别和结束 MySQL 线程，请参阅管理数据库连接。

最后，如果 CPU 用量仍然较高，并且这些查询是必要的流量，则考虑增加实例中的 CPU 资源，以避免数据库崩溃或停机。

后续步骤