排查MySQL数据处理服务器随机CPU飙升问题

看起来你碰到了个挺闹心的问题——好好跑着的MySQL数据处理服务器，毫无征兆就CPU拉满到100%，持续几秒还时不时发作，关键是没做过啥明显改动对吧？我结合你给出的服务器配置、查询语句和MySQL Tuner结果，整理了一套排查和解决的思路，你可以一步步来试试：

先搞定最紧急的内存过载问题 #

从MySQL Tuner的结果来看，这绝对是当前最大的隐患：你的服务器物理内存只有1.9G，但MySQL的最大内存占用居然能到10.1G，实际还跑到过10.2G，是物理内存的5倍多！这种情况下系统必然会疯狂用swap（虚拟内存），而swap的读写速度比内存慢几个数量级，直接导致CPU被大量消耗在等待IO上，这大概率就是你看到随机CPU飙升的核心原因。

你需要立刻调整my.cnf里的参数：

• 降低内存占用上限：
  • 把innodb_buffer_pool_size从默认的128M调整到1G（别听Tuner说的>=1.5G，物理内存不够，得留些内存给系统和其他进程），也就是innodb_buffer_pool_size=1G
  • 把max_connections从151降到50左右（每个连接要占65.9M内存，太多连接会直接把内存吃光），同时设置wait_timeout=300和interactive_timeout=300，让闲置连接自动断开，减少资源浪费
• 关闭无用的内存占用：
  • 因为你的表都是InnoDB引擎，没有MyISAM表，所以把key_buffer_size设为0，也就是key_buffer_size=0，这个参数对InnoDB没用，还占内存
• 减少连接时的CPU消耗：
  • 开启skip-name-resolve=1，避免MySQL给每个新连接做反向域名解析，这个操作很耗CPU，尤其是连接多的时候

优化InnoDB核心配置，提升整体性能 #

InnoDB的配置不合理也会导致频繁的磁盘IO，进而引发CPU飙升：

• 调整日志文件大小：按照Tuner的建议，InnoDB日志文件总大小应该是buffer pool的25%。如果你把buffer pool设为1G，那每个日志文件设为256M，也就是innodb_log_file_size=256M（注意默认是两个日志文件，总大小就是512M，刚好是1G的25%）。修改这个参数需要先停MySQL，删掉/var/lib/mysql下的ib_logfile0和ib_logfile1，再重启MySQL，不然会报错
• 关注日志写入效率：当前InnoDB Write Log efficiency只有59.62%，说明日志写入效率很低，调整buffer pool和日志文件大小后，这个指标应该会明显提升。如果还是不行，可以用iostat -x 1看看磁盘的读写负载，确认是不是磁盘本身有瓶颈

给高频查询加合适的索引，避免资源紧张时掉链子 #

你提到的那个高频SELECT语句，平时跑很快，但CPU飙升时就会出现在慢查询日志，这是因为服务器资源不足时，没有索引的查询会瞬间变得很慢，进一步加重CPU负担。

先看你的audio表有96万多行，这个SELECT的过滤条件和排序逻辑是：

SELECT * FROM audio
WHERE associated_incident IS null
AND archive IS NULL
AND temp_skip IS null
AND length >= 3
AND (locked <> 1 or locked IS NULL)
ORDER BY `audio`.`id` DESC
LIMIT 1;

建议创建一个复合索引，覆盖所有过滤条件和排序字段，让MySQL不用全表扫描就能拿到结果：

CREATE INDEX idx_audio_unprocessed ON audio (id DESC, associated_incident, archive, temp_skip, length, locked);

创建完索引后，用EXPLAIN命令跑一下这个SELECT，确认它已经用上了新索引。另外，那个UPDATE语句是根据id更新，id是自增主键，已经有主键索引，所以没问题。

排查其他潜在的小问题 #

• 清理binlog文件：你有8.2G的mysql-bin文件，虽然max_binlog_size设了100M，但没开启自动清理，建议加上expire_logs_days=7，让MySQL自动删除7天前的binlog，避免占用过多磁盘空间，磁盘满了也会引发性能问题
• 临时开启general log抓异常：如果调整完上面的配置还是有问题，可以临时开启MySQL的general log（general_log=1，general_log_file=/var/log/mysql/general.log），等CPU飙升时关掉，看看日志里有没有异常的查询或者大量重复的请求，找到根源
• 确认磁盘IO状态：在CPU飙升时，用iostat -x 1实时查看磁盘的使用率、读写速度，如果磁盘IO很高，那还是内存不足导致的频繁读磁盘，继续优化内存配置就行

备注：内容来源于stack exchange，提问作者Johnathan Martin