走啊走
加油

Nginx + PHP-FPM + MySQL部署WordPress后MySQL CPU占用异常如何诊断？

2026-01-12 16:00:58 分类：阿里云ECS
服务器价格表

当 Nginx + PHP-FPM + MySQL 部署 WordPress 后出现 MySQL CPU 占用异常高（如持续 >80%、top 显示 mysqld 进程 CPU 突增），需系统性诊断。以下是完整、可落地的诊断流程（含命令、配置建议和常见根因）：

🔍 一、快速确认现象与范围

# 1. 查看实时 CPU 占用（确认是否是 mysqld 主导）
top -b -n1 | grep mysqld

# 2. 查看 MySQL 线程数和活跃连接
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Threads_running';"

# 3. 检查 MySQL 是否在做全表扫描/慢查询积压
mysql -u root -p -e "SHOW PROCESSLIST;" | grep -E "(Query|Sleep)" | head -20

✅ 关键指标预警：

  • Threads_running > 10（高并发执行中）
  • SHOW PROCESSLIST 中大量 State: Sending data, Copying to tmp table, Sorting result
  • 存在长时间运行（>5s）的 SELECT 或 UPDATE

🛠️ 二、核心诊断步骤（按优先级排序）

✅ 步骤 1：启用并分析慢查询日志（最有效！）

# 1. 临时启用（无需重启）
mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录 >1s 的查询（WordPress 建议设为 0.5~2）
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的查询
"

# 2. 查看慢日志路径（若未配置，先设置）
mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log_file';"
# 默认通常为 /var/lib/mysql/hostname-slow.log

# 3. 实时监控慢日志（新请求触发后立即可见）
sudo tail -f /var/lib/mysql/*.slow.log

📌 重点关注：

  • SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC LIMIT 10 → 缺少 post_status+post_date 复合索引
  • SELECT ... FROM wp_options WHERE option_name LIKE 'transient_%' → transient 表未定期清理
  • JOIN wp_postmeta 无索引关联字段（如 post_id, meta_key）

✅ 修复动作： 

-- 为常见慢查询添加索引（WordPress 官方推荐）
ALTER TABLE wp_posts ADD INDEX idx_status_date (post_status, post_date);
ALTER TABLE wp_postmeta ADD INDEX idx_post_key (post_id, meta_key);
ALTER TABLE wp_options ADD INDEX idx_name (option_name);

✅ 步骤 2：检查 WordPress 插件/主题引发的低效查询

# 1. 开启 MySQL 查询分析（临时）
mysql -u root -p -e "SET profiling = 1;"

# 2. 在 WordPress 前台刷新页面，然后查看最近查询耗时
mysql -u root -p -e "SHOW PROFILES; SHOW PROFILE FOR QUERY N;"  # N 为上一步 ID

# 3. 更实用：安装 Query Monitor 插件（开发环境）
# 启用后在页面底部查看每个请求的 SQL、执行时间、未索引警告

⚠️ 高频罪魁祸首插件：

  • SEO 插件（Yoast、Rank Math）：自动更新 sitemap、分析内容 → 频繁扫描 wp_posts
  • 缓存插件失效（WP Super Cache/WP Rocket 关闭或配置错误）→ 全动态渲染，每次请求执行 50+ 查询
  • 统计插件（MonsterInsights、Jetpack Stats）：实时记录访问 → INSERT INTO wp_stats 锁表
  • 未优化的主题：get_posts() 无 posts_per_page 限制、WP_Query 未使用 cache_results => true

🔧 应急措施： 

# 临时禁用所有插件（通过数据库，避免后台无法访问）
mysql -u root -p -e "UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';"
# 重启后逐个启用，观察 CPU 变化

✅ 步骤 3：检查 MySQL 配置合理性（尤其对 WordPress 场景）

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"  # ⚠️ MySQL 8.0+ 已移除！

📌 WordPress 推荐配置（/etc/mysql/my.cnf 或 /etc/my.cnf）：

[mysqld]
# 内存分配（物理内存的 50%~70%，但至少 256MB）
innodb_buffer_pool_size = 1G

# 减少锁竞争（WordPress 写操作多）
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2  # 平衡安全性与性能（生产环境可接受）

# 连接与缓存
max_connections = 200
table_open_cache = 400
tmp_table_size = 64M
max_heap_table_size = 64M

# 禁用已废弃的查询缓存（MySQL 5.7+ 建议关闭）
query_cache_type = 0
query_cache_size = 0

✅ 验证配置生效：
sudo systemctl restart mysql && mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

✅ 步骤 4：排查外部攻击或异常流量

# 1. 检查 Nginx 访问日志中的高频 IP（可能暴力扫后台或爬虫）
sudo awk '{print $1}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -10

# 2. 检查 WordPress 登录尝试（wp-login.php 404/200 频次）
sudo grep "wp-login.php" /var/log/nginx/access.log | grep " 404 " | awk '{print $1}' | sort | uniq -c | sort -nr | head -5

# 3. 检查 MySQL 连接来源（是否存在异常 IP）
mysql -u root -p -e "SELECT host, user, COUNT(*) FROM information_schema.processlist GROUP BY host, user;"

🛡️ 防御建议：

  • 用 fail2ban 封禁暴力登录 IP
  • 重命名 wp-login.php（通过插件如 "WPS Hide Login"）
  • Nginx 层限制 /wp-admin/ 访问 IP（allow 192.168.1.0/24; deny all;）

✅ 步骤 5：检查系统级资源瓶颈（常被忽略！）

# 1. 确认不是 I/O 瓶颈导致 CPU 等待（iowait 高会表现为 CPU 占用高）
iostat -x 1 3  # 查看 %util 和 await

# 2. 检查 MySQL 数据目录磁盘类型（HDD vs SSD）
lsblk -f | grep -A5 "$(dirname $(mysql -N -s -e "SELECT @@datadir;"))"

# 3. 检查 swap 使用（MySQL 内存不足时频繁 swap → CPU 剧增）
free -h && swapon --show

💡 如果 iowait > 30% 或磁盘 util > 90%：

  • 升级 SSD
  • 调整 innodb_io_capacity（SSD 设为 2000，HDD 设为 200）
  • 启用 innodb_adaptive_hash_index = ON

🚀 三、一键诊断脚本（复制即用）

#!/bin/bash
echo "=== MySQL CPU 诊断报告 ==="
echo "1. 当前 CPU 占用:"
top -bn1 | grep mysqld | head -5
echo -e "n2. MySQL 连接状态:"
mysql -N -s -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Threads_running';"
echo -e "n3. 活跃慢查询 (>1s):"
mysql -N -s -e "SELECT TIME, INFO FROM information_schema.PROCESSLIST WHERE TIME > 1 AND COMMAND = 'Query' ORDER BY TIME DESC LIMIT 5;"
echo -e "n4. 最近慢日志条目:"
sudo tail -n5 /var/lib/mysql/*.slow.log 2>/dev/null | tail -n3
echo -e "n5. Nginx 高频访问 IP:"
sudo awk '$9==200 && $7 ~ /wp-/ {print $1}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -3

保存为 mysql-diagnose.sh，chmod +x 后运行。

✅ 四、长效优化建议

类别 推荐方案
缓存 ✅ Nginx FastCGI Cache + Redis Object Cache（插件 WP Redis）
数据库 ✅ 定期清理：DELETE FROM wp_options WHERE option_name LIKE '_transient_%';（用插件 WP-Optimize）
架构 ✅ 读写分离：主库写 + 从库读（WPDB 插件支持）
监控 ✅ 部署 Prometheus + Grafana + mysqld_exporter，设置 CPU >70% 告警

❗ 常见误区避坑

  • ❌ 盲目增加 max_connections → 可能导致内存溢出，应先优化查询
  • ❌ 关闭 innodb_flush_log_at_trx_commit=0 → 极高风险数据丢失（仅测试环境）
  • ❌ 用 OPTIMIZE TABLE 频繁整理表 → WordPress 表碎片化不严重，反而加重 I/O
  • ❌ 忽略 PHP-FPM 日志 → 实际可能是 PHP 死循环导致 MySQL 连接堆积（查 /var/log/php-fpm/www-error.log）

如按以上步骤仍无法定位，可提供以下信息进一步分析：
🔹 mysqladmin proc stat 输出
🔹 SHOW ENGINE INNODB STATUSG 中的 SEMAPHORES 和 TRANSACTIONS 部分
🔹 pt-query-digest /var/lib/mysql/*.slow.log 分析报告（Percona Toolkit）

需要我帮你解读具体日志或生成定制化优化脚本，欢迎贴出相关输出 👇

相关推荐