外观
24 - CPU 100% 但磁盘不忙:数据库性能瓶颈怎么判断
摘要与阅读定位
接口变慢时,看到数据库 CPU 100%、磁盘却不忙,应该先查什么?先定位请求在哪个环节执行或等待,再把同一时间段的流量、资源和 SQL 证据对上。CPU、磁盘读写(I/O)、内存和连接反映不同层面的状态,单个百分比不足以决定是否扩容。本文讨论如何在数据库实例层面缩小故障范围;定位到具体语句后,再按 25验证改写方案。
- 前置:02 缓冲与日志路径、14 运行证据、19 锁等待。
- 横向:15 临时资源、23 连接耗尽、26 诊断命令。
- 数据库基线为 MySQL 8.0.18+ / 8.4,存储引擎为 InnoDB。使用诊断字段前,先确认具体小版本、账号权限和事件采集配置(instrument/consumer,第 3 节说明)。遵循 A,现场 CPU、等待和处置效果应使用实际证据。
阅读入口:先找请求在哪里停,再解释资源指标
初读按第 1 节现象 → 第 2 节同窗口观察 → 第 5 节证据分支 → 第 6、7 节止损与验收推进。第 3 节全量摘要采集需要权限和开销预算,第 4 节合成统计只是计算练习,不要求先搭压测环境。
先问三个简单问题:请求主要在等连接、等数据库结果,还是处理已返回结果?同一窗口实际发送、完成和失败了多少?资源升高与哪段等待或工作量同时出现?这只能缩小假设,不能从同时出现直接推断因果。例如连接池排队时数据库 CPU 可能很低;CPU 下降也可能只是大量请求失败、再也没到数据库。
对本篇而言,瓶颈是正在限制目标吞吐或响应时间的环节,不是所有看起来很高的指标。先定义业务可接受的响应时间与错误率,再判断需要减少输入、消除等待、缩短连接持有还是补资源。
一次数据库访问可以先按下面的路径理解:
text
应用等待池连接 → 借到连接 → 数据库执行语句并等待所需资源 → 接收、处理结果 → 归还连接连接池负责让请求重复借用数据库连接;连接持有时间是从借到连接到归还的时长,可能包含 SQL 执行、锁等待和结果消费。这里划分的是观察边界,不是可直接相加的计时公式:执行与结果传输可能重叠,显式事务还涉及提交或回滚。CPU 忙说明有执行工作,队列变长说明任务不能及时推进,两者需要分别取证。
1. 现象:CPU 高与锁等待可同时发生,但没有自动因果
一条慢 SQL 在等锁,不代表 CPU 都被它消耗。等待线程可能主要处于睡眠状态,而数据库同时在处理高频扫描、后台刷新或其他租户的请求。锁竞争中的重试、自旋(反复检查条件而不睡眠)和上下文切换(切换正在执行的线程)也可能增加 CPU 开销。因此,既不能把锁等待都算成 CPU 工作,也不能完全排除锁竞争的 CPU 成本;需要把 CPU 样本与具体执行活动关联起来。
类似地,总语句耗时包含等待与执行,而且并发语句的时间会重叠。SUM_TIMER_WAIT 是累计语句耗时,按它排序可以找出累计耗时贡献较大的语句类别,却不能得到 CPU 消耗排名。连接数高也不等于所有连接正在执行:空闲连接和等锁连接都可能计入连接数。
2. 原理:USE + 等待 + 流量,使用同一时间窗
USE 方法从三个角度观察资源:利用率(Utilization)看资源有多忙,饱和度(Saturation)看是否已有任务排队,错误(Errors)看请求或资源操作是否失败。例如 CPU 利用率高但队列、响应时间正常,与 CPU 高且调度等待不断增长,是不同的状态。下面将这个思路扩展到会话和锁,并列出需要联合观察的请求指标;超时本身不一定能证明对应资源饱和。
| 资源 | 利用率(U) | 饱和/排队(S) | 错误(E) |
|---|---|---|---|
| CPU | 数据库进程、主机与容器分配口径 | 运行队列、调度等待、容器节流 | 被限流、超时及失败请求 |
| 设备 I/O | 读写吞吐、忙碌时间 | 延迟分布、排队深度、日志同步 | I/O 错误、空间不足 |
| 内存 | 进程驻留物理内存(RSS)、缓存、执行算子与容器用量 | 回收压力、换页、临时数据溢写到文件 | 内存不足(OOM)、分配失败 |
| 会话/池 | 使用中(active)、空闲(idle)、已连接(connected) | 等待获取连接的任务(pending)、连接获取时间 | 拒连、获取超时 |
| 锁/串行热点 | 持有与等待持续时间 | 等待链及同对象请求积压 | 死锁、锁超时、重试 |
CPU 100% 要说明是一核、进程相对多核、整机,还是相对于容器 CPU 配额(quota)。配额限制容器可用的 CPU 资源,达到限制后可能被节流,即暂时限制执行。不同口径下的 100% 不能直接比较。
磁盘 %util 表示采样窗口内设备的忙碌程度。多队列 SSD 可以并行处理请求,忙碌时间高不一定意味着吞吐已经达到上限;忙碌时间低也不排除少数关键同步写延迟很高。短尖峰还可能被较长采样周期抹平。因此要一起看读写延迟、队列和业务响应,跨设备、虚拟化和容器指标使用现场平台定义,不照搬固定阈值。
同时记录计划到达率、实际发送率、完成率、错误/超时和 P50/P95/P99。到达率是单位时间进入目标环节的工作量,完成率是实际完成的工作量;P50、P95、P99 分别是约 50%、95%、99% 样本不超过的响应时长。高分位数反映较慢一端的请求,也就是尾延迟,不是最大值。
闭环客户端会等前一次请求完成后再发送下一次;响应变慢时,实际发送量随之下降。这可能让资源指标回落,但原因是输入减少,不能据此认定容量增加。比较正常基线时,应使用相近的业务周期、数据分布、版本和资源额度,不能拿周末低峰比月末高峰。
3. 只读采样:先保存现场,不清累计统计
MySQL 的 Performance Schema 用于采集和汇总执行事件。下面查询语句摘要表:Digest 是归一化 SQL 类别的标识,同类语句会汇总到一起,不保留每次完整绑定参数,因此也不能仅凭 Digest 判断每次查询的工作量相同。
使用有相关查询权限的观察会话,先查看表结构,再采集摘要和全局状态。输出随实例和采样时刻变化,应完整保存到有访问控制的证据目录;SQL_TEXT、DIGEST_TEXT 也可能包含敏感业务结构,不直接发群聊。
sql
SELECT NOW(6) AS sampled_at,@@server_uuid AS instance_id,VERSION() AS version;
DESCRIBE performance_schema.events_statements_summary_by_digest;
SELECT SCHEMA_NAME,DIGEST,DIGEST_TEXT,FIRST_SEEN,LAST_SEEN,COUNT_STAR,SUM_TIMER_WAIT,
SUM_ROWS_EXAMINED,SUM_ROWS_SENT,SUM_ERRORS,
SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES,SUM_SORT_ROWS
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST IS NOT NULL;
SHOW GLOBAL STATUS WHERE Variable_name IN (
'Uptime','Threads_connected','Threads_running',
'Questions','Com_commit','Com_rollback',
'Connection_errors_max_connections',
'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_wait_free','Innodb_buffer_pool_pages_dirty'
);第一次采样记为 T0,固定短窗口后的第二次采样记为 T1,并记录采集查询本身的耗时。累计量记录从统计起点至今的总数;要了解这个窗口,就用 T1 的累计量减去 T0 的累计量。瞬时量只表示采样时刻的状态,不能用同样方式计算窗口工作量。
需要完整摘要集合才能计算窗口内贡献最大的 SQL 类别。只取两个时刻各自累计 Top 20,可能漏掉刚成为热点、但历史累计量还不大的 Digest。大摘要表采集也有成本,生产采样频率和范围应限制在批准的监控开销预算内。
- 按实例、
SCHEMA_NAME(可为 NULL)和DIGEST匹配两次结果;同一统计生命周期内,COUNT_STAR和各SUM_*字段的 T1−T0 增量才代表窗口贡献。 SUM_TIMER_WAIT以皮秒计,除以 10¹² 换算为秒。在计时采集完整、执行次数增量大于零的前提下,窗口平均耗时为ΔSUM_TIMER_WAIT / ΔCOUNT_STAR,再换算单位;不能把两次AVG_TIMER_WAIT相减。- MIN/MAX、分位数和 AVG 是聚合结果,不可简单相减获得窗口版本;窗口 P99 需请求样本或直方图桶增量及明确算法。
- 重启、TRUNCATE、统计重置、Digest 淘汰/溢出或采集丢失时不能沿用旧基线。负增量标为无效;新出现行仅在确认其首次出现于窗口时才按零基线处理,FIRST_SEEN 也需保存。
- 当前线程数、脏页数等是瞬时量。脏页是内存中已修改、尚未写回数据文件的页;两次脏页数之差反映净变化,不能直接当作写入吞吐。统计覆盖不完整时明示缺失,不把未看到当作零。
还要核验相关 instrument(事件采集点)的 ENABLED、TIMED,以及所需 consumer(控制事件记录与处理的配置)。计时未启用的事件仍可能贡献计数,却没有相应有效耗时;因此“零耗时”“平均很低”可能是采集缺口,不是语句很快。不要为套用公式擅自修改全局采集开关,先记录覆盖边界。时间统一显示为皮秒不意味着硬件有皮秒精度,详见MySQL 8.4 Performance Schema 计时说明。
4. 验证统计读法:累计耗时大不等于 CPU 消耗大
先用一行数字理解:同一语句在 T0 累计执行 100 次、耗时 1 秒,在 T1 累计执行 102 次、耗时 1.4 秒;两次之间执行 2 次、总语句时间 0.4 秒、平均 0.2 秒。不是把 1.4 秒除以 102 当作当前窗口平均,也不是比较两次累计平均的差。这只是一段统计推演,计时完整、身份一致、无重置才可采用。
这是人为构造的采样数据,不是实际监控;无需初始化业务表即可执行:
sql
WITH samples AS (
SELECT 'scan' AS digest,100 AS old_count,102 AS new_count,
1000000000000 AS old_wait,1400000000000 AS new_wait
UNION ALL
SELECT 'hot_lookup',10000,11000,2000000000000,3000000000000
UNION ALL
SELECT 'reset',100,5,1000000000000,100000000000
)
SELECT digest,
CASE WHEN new_count<old_count OR new_wait<old_wait THEN 'INVALID' ELSE 'VALID' END AS sample_state,
CASE WHEN new_count>=old_count AND new_wait>=old_wait THEN new_count-old_count END AS executions,
CASE WHEN new_count>=old_count AND new_wait>=old_wait
THEN (new_wait-old_wait)/1000000000000.0 END AS statement_seconds,
CASE WHEN new_count>old_count AND new_wait>=old_wait
THEN (new_wait-old_wait)/1000000000000.0/(new_count-old_count) END AS average_seconds
FROM samples ORDER BY digest;预期输出:
| digest | sample_state | executions | statement_seconds | average_seconds |
|---|---|---|---|---|
| hot_lookup | VALID | 1000 | 1 | 0.001 |
| reset | INVALID | NULL | NULL | NULL |
| scan | VALID | 2 | 0.4 | 0.2 |
数值显示的小数位数可能不同。scan 单次更慢,hot_lookup 总耗时贡献更大,但两者都不是 CPU 秒。判断 CPU 时间消耗在哪些活动上,需要可用的语句 CPU 指标、线程/进程采样,或经批准的 profiling(对执行活动采样或分析);使用前确认目标版本的字段、工具权限和采集开销。
窗口总语句耗时可以大于窗口实际经过的时间(墙钟时间),因为多个并发语句的计时会重叠。这不是计算错误,也不是 CPU 超出了可用时间。MySQL 的总语句耗时不能与 Oracle DB Time 逐项互换,跨产品比较时要先确认统计定义。
5. 分诊分支:每个假设都需要反证
同窗口出现的变化只能支持假设,还要检查有没有更合理的解释。下面的“反证”列用于提醒哪些证据可能推翻当前判断;缺少某项采样本身,并不能证明对应等待不存在。冷读指所需数据不在缓冲中,需要物理读取;工作集是当前负载经常访问的数据。mmap 是内存映射文件,filesort 是 MySQL 的排序机制名称,不能仅凭名称判定是否落盘;MDL 是保护表结构等元数据的锁。
| 症状/假设 | 支持证据 | 应检查的反证 | 优先动作 |
|---|---|---|---|
| 扫描/计算消耗 CPU | 进程 CPU + 窗口高频/高工作量 SQL + 计划输入量 | 慢事件主要等锁/日志,CPU 是后台或别的租户 | 减少无效输入,按 25单变量改写 |
| 数据冷读瓶颈 | 物理读增量、数据文件读延迟与 P99 同步 | 缓冲命中但提交仍慢、设备错误或旁路负载 | 02区分工作集与其他路径 |
| 提交日志同步慢 | 提交阶段延迟与日志文件等待相符 | 实际是锁或网络,日志设备无相应事件 | 21、22;不降低耐久性凑数字 |
| 临时资源压力 | 排序/临时事件 + 文件/内存峰值 + 并发 | 临时表计数低仍可能 mmap/filesort,空间来自备份 | 15控制输入与报表并发 |
| 锁/长事务阻塞 | 等待者与持有者同一对象链、事务和应用时间线 | 没有等待链可能采样遗漏或 MDL,不能宣布无锁 | 19、20确认完整清理 |
| 池等待/消费慢 | 获取连接(acquire)耗时、使用中连接和等待者数量,或大结果消费时间 | 数据库总连接不满不排除本地池满 | 23拆连接生命周期 |
后台工作也会争用资源,应把备份、DDL(表结构变更等操作)、统计更新、Purge(清理不再需要的历史版本)、复制和引擎维护与故障窗口对齐。Compaction 是适用 LSM(日志结构合并树)引擎中的数据合并整理工作,不应默认当成 InnoDB B+ Tree 的后台工作。多个瓶颈可以共存,先处理正在损害服务目标(SLO,如响应时长与错误率约定)的主要环节,再检查压力是否转移。
6. 从诊断到止损:降低进入速率,不放大重试
止损动作必须有审批、作用范围、回退和验收:暂停可延迟的报表或批任务,限制入口与重试并发,为关键事务保留容量。资源紧张时,重试会额外增加进入数据库的工作量,可能让排队进一步加重。扩大连接池也不会自动增容:它允许更多任务同时持有连接,可能增加内存占用、资源争用和超时,进而延长连接持有时间,见 23。
处理根阻塞者前保存身份、SQL、事务修改量和上游逻辑;KILL QUERY 不等于结束事务,KILL CONNECTION 也不保证瞬间完成回滚。取消客户端请求不证明数据库已停。终止与重试按 20、26的失败边界,不在本篇提供自动 kill 脚本。
扩容适合已确认的持续资源不足。若请求仍须串行争用同一个热点,增加资源未必能消除等待;若 SQL 处理了大量无效数据,应先减少工作量。迁移报表到副本时,还要按 27评估读一致性;需要读取主库写入后状态来决定后续操作的逻辑,不能直接迁到可能延迟的副本。
7. 验收与跨产品边界
优化前后在相近业务分布和到达率下比较完成吞吐、P99、错误、队列和资源。只有资源下降,却没有完成量、响应时间和错误率的共同改善,不足以证明瓶颈已解决。验证暂缓任务恢复后没有再次压垮系统,记录压力是否转移到日志、临时盘或副本。
Oracle AWR(自动工作负载资料库)和 ASH(活跃会话历史)用于分析负载与会话活动,其采样、聚合定义、权限及许可需由组织和 DBA 核验。使用云托管指标、容器配额、分布式节点最大值或平均值时,也要先查现场平台文档,不能用整机均值掩盖单节点或租户热点。
上述 SQL 只读取统计或计算合成数据,没有需要清理的业务表;保存的证据按敏感信息保留期管理。确定故障方向后,按 25把假设变成可验证的改动。
参考资料
- MySQL 8.4 Reference Manual:Performance Schema Statement Summary Tables、Statement Histogram Summary Tables、Status Variables、InnoDB Metrics。
- 适用条件:现场 CPU 归因、资源口径及所有处置效果应有真实证据支持,不把合成统计当成压测或生产结果。
8. 名词解释
| 名词 | 本文含义 |
|---|---|
| USE 方法 | 看资源利用率、饱和排队和错误,不是 SQL 的 USE 选库语句 |
| SLO | 业务服务目标,如响应时长与错误率的约定 |
| quota/节流 | 容器获准使用的资源额度/超出额度后被限制执行 |
| 到达率/完成率 | 进入目标环节的工作速率/实际完成的速率,要同时看失败 |
| 瞬时量/累计量 | 当前状态照片/从统计起点累计的数据 |
| Digest | 归一化语句类别身份,不包含每次完整绑定参数 |
| instrument/consumer | 事件采集点及其启用、计时配置/记录事件的消费设施 |
| P99/尾延迟 | 统计窗口内的高分位响应时间/慢的一端,不是最大值 |
| profiling | 对执行活动采样或分析以定位时间消耗,需要工具权限与预算 |