优化MySQL中的高 CPU 利用率

勾选 Threads_running 和 Threads_connected

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

> SHOW STATUS like 'Threads_%';


Threads_running 是 Threads_connected 的子集。其余线程处于空闲状态。Threads_running 增加会导致 CPU 使用率增加。最好检查这些线程上运行的内容。

检查查询状态

运行 SHOW PROCESSLIST 命令可查看正在进行的查询。它按顺序返回所有连接的线程及其当前运行的 SQL 语句。

mysql> SHOW [FULL] PROCESSLIST;


注意状态列和时长列。检查是否有许多查询处于同一状态。

• 如果许多线程显示 Updating，则可能存在记录锁争用。请参阅下一步。
• 如果许多线程针对表元数据锁定显示 Waiting，请检查查询以了解表，然后查找可能持有元数据锁的 DDL（例如 ALTER TABLE）。如果早期查询（例如长时间运行的 SELECT query）持有 DDL，则 DDL 也可能会等待表元数据锁定。

检查记录锁定争用

当事务持有热门索引记录锁时，它们会屏蔽请求相同锁的其他事务。这可能会形成链式影响，并导致许多请求卡住，并增加 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 范围。

查找长时间运行的事务

• 使用 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


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

检查长时间运行的事务的 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 =
ORDER BY event_id;


• 使用慢查询日志

  调试时，您可以将耗时超过 N 秒的所有查询捕获到慢查询日志中。

检查信号量争用

在并发环境中，共享资源的互斥和读写锁存器可能是争用点，这会降低服务器的性能。此外，如果信号量等待时间超过 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 将其停用。

优化长 SELECT 查询

首先，查看查询。确定查询的目标以及获取结果的最佳方法。最佳查询计划是最大限度地减少数据访问的计划。

• 检查查询执行计划：

mysql> EXPLAIN ;


• 使用正确的索引

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

mysql> analyze table


增加用于计算索引统计信息的示例页面的数量。

• 充分利用索引

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

• 使用优化器提示

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

避免“读取已提交”的长历史记录列表

历史记录列表是撤消表空间中未清除的事务列表。事务的默认隔离级别是 REPEATABLE READ，它需要事务在其整个时间段内读取相同的快照。因此，SELECT 查询会阻止完全清除自查询（或事务）以来发出的撤消日志记录。因此，历史记录长列表会降低查询性能。避免构建较长历史记录列表的一种方法是将事务隔离级别更改为“读取已提交”。使用“读取已提交”后，不再需要保留历史记录列表以获得一致的读取视图。您可以为所有会话、单个会话或下一个单个事务全局更改事务隔离级别。调整服务器配置

关于服务器配置，有太多话要说。虽然完整案例超出了本文档的讨论范围，但值得一提的是，服务器还会报告各种状态变量，以提供与相关配置相关的提示。例如：

• 如果 Threads_created/Connections 很大，请调整 thread_cache_size。适当的线程缓存可以减少线程创建时间并帮助高度并发的工作负载。
• 如果 Table_open_cache_misses/Table_open_cache_hits 非常重要，请调整 table_open_cache。将表放入表缓存可以节省查询执行时间，并且在高度并发的环境中可能产生影响。

终止不需要的连接

如果查询无效或不再需要该查询，您可以停止查询。