优化实例中的高 CPU 利用率

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

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

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

建议

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 秒的所有查询捕获到慢速查询日志中。如需启用慢速查询日志，您可以在 Google Cloud 控制台或 gcloud CLI 的实例页面上修改实例设置，然后在 Google 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 上，自适应哈希编入索引可能就是争用问题的根源。请尝试使用 Google 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 资源，以避免数据库崩溃或停机。