实例中的 CPU 利用率高可能是由于各种原因造成的,例如工作负载增加、事务量过多、查询速度慢和事务运行时间过长。
预配不足的实例 Recommender 会分析 CPU 利用率。如果 CPU 利用率水平在过去 30 天内达到或超过 95%,Recommender 会提醒您并提供额外的数据分析来帮助您解决问题。
本文档介绍在 Cloud SQL for MySQL 实例被预配不足的实例 Recommender 标识为具有高 CPU 利用率时,如何查看和优化该实例。
推荐
CPU 利用率随工作负载成比例增加。如需降低 CPU 利用率,请检查正在运行的查询并对其进行优化。以下是检查 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 = <mysql thread id> ORDER BY event_id;
使用慢查询日志
调试时,您可以将耗时超过
N
秒的所有查询捕获到慢查询日志中。 如需启用慢查询日志,您可以在 Google Cloud 控制台或gcloud CLI
的实例页面上修改实例设置,然后在 Google Cloud 控制台或gloud CLI
中使用日志查看器查看日志。
检查信号量争用
在并发环境中,共享资源的互斥和读写锁存器可能是争用点,这会降低服务器的性能。此外,如果信号量等待时间超过 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 <the query>;
请参阅 MySQL 文档,了解如何解读输出和评估查询效率。
- 使用正确的索引
检查键列以查看是否使用了预期的索引。如果没有,请更新索引统计信息:
mysql> analyze table <table_name>
增加用于计算索引统计信息的示例页面的数量。如需了解详情,请参阅 MySQL 文档。
- 充分利用索引
使用多列索引时,请检查
key_len
列,查看索引是否已被充分利用来过滤记录。最左侧的列需要进行相等的比较,并且索引可用于第一个范围条件(包括第一个条件)。- 使用优化器提示
避免“读取已提交”的长历史记录列表
历史记录列表是撤消表空间中未清除的事务列表。事务的默认隔离级别是 REPEATABLE READ,它需要事务在其整个时间段内读取相同的快照。因此,
SELECT
查询会阻止完全清除自查询(或事务)以来发出的撤消日志记录。因此,历史记录长列表会降低查询性能。避免构建较长历史记录列表的一种方法是将事务隔离级别更改为“读取已提交”。使用“读取已提交”后,不再需要保留历史记录列表以获得一致的读取视图。您可以为所有会话、单个会话或下一个单个事务全局更改事务隔离级别。如需了解详情,请参阅 MySQL 文档。调整服务器配置
关于服务器配置,有太多话要说。虽然完整案例超出了本文档的讨论范围,但值得一提的是,服务器还会报告各种状态变量,以提供与相关配置相关的提示。例如:
- 如果
Threads_created/Connections
很大,请调整thread_cache_size
。适当的线程缓存可以减少线程创建时间并帮助高度并发的工作负载。 - 如果
Table_open_cache_misses/Table_open_cache_hits
非常重要,请调整table_open_cache
。将表放入表缓存可以节省查询执行时间,并且在高度并发的环境中可能产生影响。
- 如果
终止不需要的连接
如果查询无效或不再需要该查询,您可以停止查询。如需了解如何识别和结束 MySQL 线程,请参阅管理数据库连接。
最后,如果 CPU 使用率仍然很高,并且查询会形成必要的流量,请考虑增加实例中的 CPU 资源,以避免数据库崩溃或停机。