三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

MySQL高CPU使用率排查与优化实战指南

发布时间：2026/8/6 7:14:40 三亩地 编程学习日记
MySQL高CPU使用率排查与优化实战指南

1. MySQL高CPU使用率问题概述

最近在排查线上数据库性能问题时，发现一个MySQL实例的CPU使用率长期维持在90%以上，这种情况在业务高峰期尤为明显。作为DBA，我们需要系统性地分析可能导致CPU飙升的各种因素，并给出针对性的优化方案。

高CPU使用率通常意味着数据库正在执行大量计算密集型操作，可能是查询效率低下、锁争用、配置不当或硬件资源不足导致的。长期处于这种状态会导致查询响应变慢，严重时甚至引发服务不可用。下面我将结合多年实战经验，分享完整的排查思路和解决方案。

2. 核心排查方法与工具

2.1 实时监控与性能分析

首先通过标准监控工具获取实时性能数据：

# 查看系统整体CPU使用情况 top -H -p $(pgrep -d, mysqld) # MySQL自带的性能监控 mysqladmin -uroot -p ext | grep -E 'Threads_running|Queries|Questions|Slow_queries'

更专业的做法是使用Performance Schema收集详细指标：

-- 开启性能监控 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'; -- 查看CPU消耗最高的SQL SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

2.2 慢查询日志分析

慢查询是CPU过载的常见原因，建议配置：

# my.cnf配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1

使用pt-query-digest工具分析日志：

pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

典型输出会显示：

  • 执行时间最长的查询
  • 执行频率最高的查询
  • 全表扫描的查询
  • 锁等待时间长的查询

3. 常见原因与解决方案

3.1 低效SQL查询

这是最常见的原因，约占高CPU案例的70%。典型表现包括：

  • 缺少合适索引
  • 存在全表扫描
  • 复杂子查询嵌套
  • 错误使用JOIN

解决方案：

-- 使用EXPLAIN分析查询计划 EXPLAIN SELECT * FROM orders WHERE user_id = 100; -- 添加适当索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); -- 重写复杂查询 -- 原查询（问题示例）： SELECT * FROM users WHERE id IN ( SELECT DISTINCT user_id FROM orders WHERE create_time > '2023-01-01' ); -- 优化后： SELECT u.* FROM users u JOIN ( SELECT DISTINCT user_id FROM orders WHERE create_time > '2023-01-01' ) o ON u.id = o.user_id;

3.2 锁争用问题

锁冲突会导致大量线程等待，表现为：

  • 大量线程处于"Waiting for table lock"状态
  • InnoDB行锁等待
  • 元数据锁争用

排查方法：

-- 查看当前锁状态 SHOW ENGINE INNODB STATUS\G -- 查看等待锁的线程 SELECT * FROM performance_schema.events_waits_current WHERE EVENT_NAME LIKE '%lock%';

优化建议：

  1. 将大事务拆分为小事务
  2. 避免在事务中执行DDL操作
  3. 合理设置隔离级别
  4. 使用SELECT ... FOR UPDATE时明确指定索引

3.3 配置不当

错误的MySQL配置会显著增加CPU负载：

常见问题配置推荐值说明
innodb_buffer_pool_size70%物理内存缓冲池过小导致频繁磁盘IO
table_open_cache4000+表缓存不足导致频繁开关
tmp_table_size64M+临时表过小导致磁盘临时表
max_connections合理值连接数过多导致上下文切换

检查当前配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW STATUS LIKE 'Table_open_cache%';

3.4 硬件资源不足

当数据量和并发量增长到一定程度时，硬件可能成为瓶颈：

  1. CPU核心数不足：建议至少4核，高并发场景8核+
  2. 内存不足：InnoDB缓冲池应能容纳活跃数据集
  3. 磁盘IOPS低：考虑使用SSD或NVMe

检查命令：

# CPU核心数 grep -c ^processor /proc/cpuinfo # 内存使用 free -h # 磁盘IO iostat -dx 1

4. 高级排查技巧

4.1 火焰图分析

使用perf工具生成CPU火焰图：

# 采集数据 perf record -F 99 -p $(pgrep mysqld) -g -- sleep 30 # 生成火焰图 perf script | stackcollapse-perf.pl | flamegraph.pl > mysql.svg

火焰图可以直观显示：

  • 哪些函数调用消耗CPU最多
  • 调用栈深度
  • 热点代码路径

4.2 InnoDB监控

开启高级InnoDB监控：

SET GLOBAL innodb_monitor_enable = 'all';

重点关注指标：

  • 缓冲池命中率（应>95%）
  • 行锁等待时间
  • 日志写入量
  • 脏页比例

4.3 连接池问题

连接泄漏或连接池配置不当会导致：

  • 大量空闲连接消耗CPU
  • 连接创建销毁开销大

检查命令：

SHOW PROCESSLIST; SHOW STATUS LIKE 'Threads_%';

优化建议：

  1. 合理设置连接超时
  2. 使用连接池中间件
  3. 实现连接复用

5. 系统级优化

5.1 Linux内核参数

调整系统参数提升MySQL性能：

# 增加文件描述符限制 echo "* soft nofile 65535" >> /etc/security/limits.conf # 调整内核参数 echo "vm.swappiness = 1" >> /etc/sysctl.conf echo "vm.dirty_ratio = 10" >> /etc/sysctl.conf sysctl -p

5.2 NUMA架构优化

对于多CPU插槽服务器：

# 启动MySQL时绑定NUMA节点 numactl --interleave=all /usr/sbin/mysqld

5.3 文件系统优化

推荐配置：

  • 使用XFS或ext4文件系统
  • 禁用atime更新
  • 适当增加文件系统缓存
# /etc/fstab示例 /dev/sdb1 /var/lib/mysql xfs defaults,noatime,nodiratime 0 0

6. 长期监控与预防

建立完善的监控体系：

  1. 部署Prometheus + Grafana监控
  2. 设置关键指标告警（CPU>80%持续5分钟）
  3. 定期进行性能健康检查

推荐监控指标：

  • QPS/TPS变化趋势
  • 慢查询数量
  • 连接数变化
  • 缓冲池命中率
  • 锁等待时间

7. 典型问题处理实录

案例1：电商大促期间CPU飙升

  • 现象：秒杀活动期间CPU达到100%
  • 排查：发现大量相同SQL执行（SELECT * FROM inventory WHERE item_id=?）
  • 解决：添加缓存层，优化为批量查询

案例2：报表系统凌晨卡顿

  • 现象：每日3点ETL任务期间CPU满载
  • 排查：发现全表扫描统计查询
  • 解决：添加汇总表，改为增量计算

案例3：连接池泄漏

  • 现象：CPU持续高负载但QPS很低
  • 排查：发现3000+空闲连接
  • 解决：修复应用连接泄漏，设置连接超时

8. 性能优化检查清单

定期执行以下检查：

  1. [ ] 索引使用率检查
  2. [ ] 缓冲池命中率检查
  3. [ ] 锁等待分析
  4. [ ] 临时表使用情况
  5. [ ] 排序操作分析
  6. [ ] 连接使用情况
  7. [ ] 磁盘IO压力检查
  8. [ ] 系统上下文切换频率

具体检查SQL：

-- 未使用索引查询 SELECT * FROM sys.schema_unused_indexes; -- 缓冲池命中率 SELECT (1 - (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')) AS hit_ratio; -- 临时表使用 SHOW STATUS LIKE 'Created_tmp%';

9. 性能优化工具推荐

  1. Percona Toolkit：包含pt-query-digest等实用工具
  2. MySQL Enterprise Monitor：官方监控方案
  3. VividCortex：SaaS性能监控平台
  4. Prometheus + mysqld_exporter：开源监控方案
  5. Orchestrator：复制拓扑管理工具

安装示例：

# Percona Toolkit wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit

10. 配置优化模板

推荐的基础配置模板（my.cnf）：

[mysqld] # 基础配置 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # 内存配置 innodb_buffer_pool_size = 12G # 70%物理内存 innodb_buffer_pool_instances = 8 # 每个实例至少1GB key_buffer_size = 256M # 日志配置 innodb_log_file_size = 2G innodb_log_files_in_group = 2 sync_binlog = 1 # 连接配置 max_connections = 500 thread_cache_size = 100 table_open_cache = 4000 # 查询优化 query_cache_type = 0 # 通常建议禁用 tmp_table_size = 64M max_heap_table_size = 64M

根据服务器配置调整参数后，建议逐步测试验证，避免一次性修改过多参数。

编程学习 学习日记 实战经验
← 返回列表