WordPress站点MySQL性能调优求助：高内存与磁盘临时表问题

一、降低MySQL内存占用 #

你的MySQL最大内存占用达13.8G，完全是参数配置与服务器硬件不匹配导致——2GB物理内存下，必须严格限制MySQL各组件的内存分配，同时预留足够内存给系统、Web服务（Nginx/Apache）和PHP-FPM。

关键参数调整（写入my.cnf的[mysqld]段） #

• innodb_buffer_pool_size=128M：你的数据库仅60MB，128M足够缓存全量数据，避免过大的缓冲池占用内存
• max_connections=50：WordPress站点并发需求低，50个连接足够，过多连接会成倍消耗内存
• join_buffer_size=128K、sort_buffer_size=256K：调小连接/排序缓冲，避免单连接内存占用过高
• read_buffer_size=128K、read_rnd_buffer_size=256K：优化顺序/随机读缓冲的内存分配
• tmp_table_size=64M、max_heap_table_size=64M：两个参数保持一致，限制内存临时表的最大尺寸，同时避免过小导致频繁转磁盘
• table_open_cache=200：匹配WordPress的表数量，避免不必要的内存开销
• query_cache_type=0、query_cache_size=0：关闭查询缓存（WordPress场景下查询缓存效率极低，还会占用内存）

调整后，MySQL总内存占用可控制在400MB以内，为其他服务留出足够空间。

二、减少磁盘临时表 #

70%临时表创建在磁盘，主要原因是内存临时表尺寸不够、查询未用到索引或涉及TEXT/BLOB字段的排序/分组，按以下步骤解决：

1. 先通过参数优化内存临时表阈值 #

上述tmp_table_size和max_heap_table_size设为64M，可覆盖绝大多数WordPress查询的临时表需求，避免小临时表转磁盘。

2. 优化WordPress查询与索引 #

• 开启慢查询日志定位问题：

  slow_query_log=1
  slow_query_log_file=/var/log/mysql/slow.log
  long_query_time=2
  

  重启MySQL后，查看慢日志找出频繁创建磁盘临时表的查询。
• 补充WordPress核心表缺失的索引：
  • 给wp_postmeta添加(post_id, meta_key)组合索引
  • 给wp_usermeta添加(user_id, meta_key)组合索引
  • 给wp_commentmeta添加(comment_id)索引
    这些索引能避免大部分GROUP BY/ORDER BY操作创建临时表。
• 避免SELECT *：只查询业务需要的字段，减少临时表的数据量。
• 规避TEXT/BLOB字段的排序/分组：这类字段会强制使用磁盘临时表，若必须操作，可将字段内容摘要为短字符串存入新字段并建立索引。

3. 调整后的my.cnf核心片段示例 #

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 内存调优
innodb_buffer_pool_size=128M
max_connections=50
join_buffer_size=128K
sort_buffer_size=256K
read_buffer_size=128K
read_rnd_buffer_size=256K
tmp_table_size=64M
max_heap_table_size=64M
table_open_cache=200
query_cache_type=0
query_cache_size=0

# 查询优化
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2
innodb_flush_log_at_trx_commit=1
sync_binlog=1

最后操作 #

修改配置后执行systemctl restart mysqld重启服务，再用MySQLTuner检测内存占用是否达标，同时定期分析慢查询日志优化查询。

内容的提问来源于stack exchange，提问作者RocketBrian