MySQL my.cnf配置指导请求：添加/更新WordPress文章时CPU负载过高

我从未修改过WHM（cPanel）默认的my.cnf配置，服务器是CentOS+WHM(cPanel)环境。现在每次添加或更新WordPress文章时，MySQL的CPU负载会瞬间拉满，要3-5分钟才会降下来。已经用phpMyAdmin做过数据库优化，但我完全不懂MySQL，求帮忙调整配置。

服务器硬件配置 #

• CPU：Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz
• 处理器总数：24核
• 内存：24GB

当前my.cnf配置 #

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=26214400
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1

MySQLTuner检测结果 #

>>  MySQLTuner 2.0.7
         * Jean-Marie Renouard <jmrenouard@gmail.com>
         * Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] 跳过MySQLTuner脚本版本检查
[OK] 当前运行的MySQL版本5.7.39受支持
[OK] 运行在64位架构上

-------- 日志文件建议 ------------------------------------------------------------------
[OK] 日志文件/var/log/mysqld.log存在
[--] 日志文件大小：/var/log/mysqld.log (1M)
[OK] 日志文件/var/log/mysqld.log不为空
[OK] 日志文件/var/log/mysqld.log小于32Mb
[OK] 日志文件/var/log/mysqld.log可读
[!!] /var/log/mysqld.log包含1226条警告
[!!] /var/log/mysqld.log包含4494条错误
[--] 检测到mysqld启动13次
[--] 1) 2022-07-26T06:19:40.139514Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 2) 2022-07-26T06:19:31.781074Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 3) 2022-06-22T06:18:34.436714Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 4) 2022-06-13T17:32:41.391135Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 5) 2022-05-20T06:20:53.227443Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 6) 2022-04-28T06:23:10.156350Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 7) 2022-04-26T06:19:36.293205Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 8) 2022-04-26T06:19:27.053465Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 9) 2022-02-11T04:11:16.610287Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 10) 2022-02-09T05:10:52.845046Z 0 [Note] /usr/sbin/mysqld: 已准备好接受连接
[--] 检测到mysqld关闭11次
[--] 1) 2022-07-26T06:19:39.477939Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 2) 2022-07-26T06:19:30.590977Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 3) 2022-06-22T06:18:33.049031Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 4) 2022-05-20T06:20:51.192314Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 5) 2022-04-28T06:22:53.450673Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 6) 2022-04-26T06:19:35.698012Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 7) 2022-04-26T06:19:26.078360Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 8) 2022-02-11T04:11:15.723668Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 9) 2022-02-09T05:10:52.081450Z 0 [Note] /usr/sbin/mysqld: 关闭完成
[--] 10) 2022-02-08T20:25:03.836241Z 0 [Note] /usr/sbin/mysqld: 关闭完成

-------- 存储引擎统计 -----------------------------------------------------------------
[--] 状态：+ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] MyISAM表数据量：291.8M（共267张表）
[--] InnoDB表数据量：2.5G（共760张表）
[OK] 碎片表总数：0

-------- 分析性能指标 --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] 查询INFORMATION_SCHEMA时不会更新统计信息

-------- 视图指标 -----------------------------------------------------------------------------

-------- 触发器指标 --------------------------------------------------------------------------

-------- 存储过程/函数指标 --------------------------------------------------------------------------

-------- 安全建议 ------------------------------------------------------------------
[OK] 未检测到角色用户
[OK] 没有匿名数据库用户账号
[!!] 用户'erroriope'@'server.localhost.com'未设置密码
[!!] 没有基础密码文件列表！

-------- CVE安全建议 --------------------------------------------------------------
[--] 因未指定--cvefile选项，跳过检测

-------- 性能指标 -----------------------------------------------------------------------
[--] 运行时长：53天0小时35分4秒（总查询10亿次 [403.841 qps]，连接2100万次，传输量：28622G，接收量：221G）
[--] 读写比例：97% / 3%
[--] 二进制日志已禁用
[--] 物理内存：23.4G
[--] MySQL最大内存占用：38.0G
[--] 其他进程内存占用：0B
[--] 总缓冲区：全局66.0M + 每个线程257.1M（最大151线程）
[--] Performance_schema最大内存占用：0B
[--] Galera GCache最大内存占用：0B
[!!] 已达到的最大内存占用：38.2G（为已安装内存的163.12%）
[!!] 最大可能内存占用：38.0G（为已安装内存的162.05%）
[!!] 加上其他进程的内存占用已超出系统可用内存
[OK] 慢查询占比：0%（0/10亿）
[!!] 最高连接使用率：100%（152/151）
[OK] 中断连接占比：0.02%（5215/21398314）
[!!] cPanel和Flex系统应开启skip-name-resolve
[OK] 查询缓存默认已禁用，避免多处理器环境下的互斥锁竞争
[OK] 需要临时表的排序占比：1%（400万次临时排序 / 2.25亿次总排序）
[!!] 未使用索引的JOIN操作：756362次
[!!] 磁盘临时表占比：51%（1.05亿次磁盘临时表 / 2.06亿次总临时表）
[OK] 线程缓存命中率：99%（创建11.9万次 / 连接2100万次）
[OK] 表缓存命中率：99%（命中20亿次 / 请求20亿次）
[OK] table_definition_cache（1400）大于表总数（1307）
[OK] 打开文件限制使用率：1%（501/40000）
[OK] 立即获取表锁占比：99%（2.79亿次立即获取 / 2.79亿次锁请求）

-------- Performance Schema ------------------------------------------------------------------------
[!!] 应启用Performance Schema以获得更好的诊断信息
[--] Sys schema已安装

-------- 线程池指标 ------------------------------------------------------------------------
[--] 线程池统计已禁用

-------- MyISAM指标 ----------------------------------------------------------------------------
[OK] 键缓冲区使用率：100.0%（已用8.0M / 缓存8.0M）
[OK] 键缓冲区大小/MyISAM总索引大小：8.0M/70.9M
[OK] 键缓冲区读命中率：99.6%（缓存140亿次 / 读取5400万次）
[!!] 键缓冲区写命中率：89.1%（缓存100万次 / 写入97.6万次）

-------- InnoDB指标 ----------------------------------------------------------------------------
[--] InnoDB已启用
[--] InnoDB线程并发数：0
[OK] 已启用InnoDB独立表空间
[!!] InnoDB缓冲池/数据大小：25.0M / 2.5G
[!!] InnoDB日志文件大小/InnoDB缓冲池大小比例（384%）：48.0M * 2 / 25.0M，该比例应等于25%
[OK] InnoDB缓冲池实例数：1
[--] InnoDB缓冲池块数：1（对应1个缓冲池实例）
[OK] Innodb_buffer_pool_size与Innodb_buffer_pool_chunk_size及Innodb_buffer_pool_instances对齐
[OK] InnoDB读缓冲区效率：98.47%（命中347419925689次 / 总请求352826132195次）
[!!] InnoDB写日志效率：60.07%（命中56677143次 / 总请求94358371次）
[OK] InnoDB日志等待次数：0.00%（0次等待 / 37681228次写入）

-------- Aria指标 ------------------------------------------------------------------------------
[--] Aria存储引擎不可用

-------- TokuDB指标 ----------------------------------------------------------------------------
[--] TokuDB已禁用

-------- XtraDB指标 ----------------------------------------------------------------------------
[--] XtraDB已禁用

-------- Galera指标 ----------------------------------------------------------------------------
[--] Galera已禁用

-------- 复制指标 -----------------------------------------------------------------------
[--] Galera同步复制：NO
[--] 无复制从服务器
[--] Binlog格式：ROW
[--] XA事务支持已启用：ON
[--] 半同步复制主服务器：未激活
[--] 半同步复制从服务器：未激活
[--] 这是一台独立服务器

-------- 建议 ---------------------------------------------------------------------------
通用建议：
    检查/var/log/mysqld.log中的警告信息
    检查/var/log/mysqld.log中的错误信息
    为用户'erroriope'@'server.localhost.com'设置安全密码：SET PASSWORD FOR 'erroriope'@'server.localhost.com' = PASSWORD('secure_password');
    降低MySQL整体内存占用以保证系统稳定性
    若追求最高性能，可将服务器专用于数据库服务
    减少或消除持久连接以降低连接使用率
    因cPanel不支持，域名解析已启用
    建议提高'join_buffer_size'直到解决无索引JOIN问题
    调整时保持tmp_table_size和max_heap_table_size相等
    减少无LIMIT子句的SELECT DISTINCT查询
    应启用Performance Schema以获得更好的诊断信息
    修改innodb_log_file_size和/或innodb_log_files_in_group前请参考相关文档

需要调整的变量：
  *** MySQL最大内存占用过高，存在风险 ***
  *** 若要增加MySQL缓冲区变量，请先增加服务器内存 ***
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    skip-name-resolve=0
    join_buffer_size (> 256.0K，或始终为JOIN操作添加索引)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema=ON
    innodb_buffer_pool_size (>= 2.5G) 若条件允许
    innodb_log_file_size 应设为(=3M) 若条件允许，使InnoDB日志文件总大小等于缓冲池大小的25%

优化后的my.cnf配置建议 #

基于你的服务器配置和检测结果，以下是调整后的my.cnf配置（替换原有[mysqld]段内容，保留原有注释或按需添加）：

[mysqld]
performance-schema=1
# 基础配置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 内存相关配置（针对24GB内存，预留部分给系统和cPanel）
innodb_buffer_pool_size=12G  # 分配约一半内存给InnoDB缓冲池，覆盖现有2.5G数据
innodb_buffer_pool_instances=12  # 按每1G缓冲池分配1个实例，提升并发性能
tmp_table_size=64M
max_heap_table_size=64M
join_buffer_size=1M  # 提升无索引JOIN的性能，优先建议给查询加索引
sort_buffer_size=2M
read_rnd_buffer_size=2M

# 连接相关配置
max_connections=250  # 解决连接占满问题
wait_timeout=300
interactive_timeout=300
skip-name-resolve=0  # cPanel环境保持默认，避免解析问题

# InnoDB日志优化
innodb_log_file_size=1.5G  # 缓冲池的25%（12G*25%=3G，分2个文件则每个1.5G）
innodb_log_files_in_group=2
innodb_log_buffer_size=64M