MySQL服务在CentOS/Ubuntu服务器上吃掉80%内存，怎样排查和调优？-CLOUD云计算

MySQL 占用 80% 内存（尤其是长期稳定在高位）确实需要系统性排查，不能直接调小 innodb_buffer_pool_size 就了事——可能掩盖真实问题，甚至引发性能恶化。以下是面向生产环境的 分步诊断 → 根因定位 → 安全调优 流程（兼顾 CentOS 和 Ubuntu，命令通用）：

🔍 一、快速确认：是否真由 MySQL 引起？（排除误判）

# 查看整体内存使用（重点关注 RES 列）
ps aux --sort=-%mem | head -10
# 或更直观：
htop  # (需安装：yum install htop / apt install htop)

# 检查 MySQL 进程实际 RSS 内存（非虚拟内存 VIRT）
ps -o pid,user,%mem,vsz,rss,comm -C mysqld
# 示例输出：RSS=6.2g → 实际物理内存占用约 6.2GB

✅ 关键点：

若 RSS（Resident Set Size）接近总内存 80%，才确认是 MySQL 真实吃内存；
若 VIRT 高但 RSS 低 → 可能是 mmap 映射或预分配（InnoDB Buffer Pool 的正常行为），不危险。

🧩 二、深入诊断：定位内存消耗来源

1️⃣ 检查 InnoDB Buffer Pool（最大头号嫌疑）

-- 连入 MySQL
mysql -u root -p

-- 查看当前配置与实际使用率
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_%';

-- 计算使用率（关键！）
SELECT 
  ROUND(@@innodb_buffer_pool_size / 1024 / 1024 / 1024, 2) AS pool_gb,
  ROUND((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) * 100.0 / Innodb_buffer_pool_read_requests, 2) AS hit_rate,
  ROUND(Innodb_buffer_pool_bytes_data / 1024 / 1024, 2) AS data_mb,
  ROUND(Innodb_buffer_pool_bytes_dirty / 1024 / 1024, 2) AS dirty_mb
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Innodb_buffer_pool_bytes_data','Innodb_buffer_pool_bytes_dirty');

📌 解读：

✅ 健康指标：hit_rate > 95%（越高越好），data_mb 接近 pool_gb*1024 → 说明 Buffer Pool 被有效利用；
⚠️ 风险信号：hit_rate < 90% + data_mb 远小于池大小 → Buffer Pool 过大，浪费内存；
❗ dirty_mb 持续 > data_mb*30% → 刷脏压力大，可能需调 innodb_io_capacity。

2️⃣ 检查连接与线程内存（易被忽视的“内存泄漏”源头）

-- 查看当前连接数及每个连接的内存开销
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- 检查 per-connection 内存参数（累加后很可观！）
SELECT 
  @@sort_buffer_size/1024/1024 AS sort_buffer_mb,
  @@read_buffer_size/1024/1024 AS read_buffer_mb,
  @@read_rnd_buffer_size/1024/1024 AS read_rnd_buffer_mb,
  @@join_buffer_size/1024/1024 AS join_buffer_mb,
  @@tmp_table_size/1024/1024 AS tmp_table_mb,
  @@max_heap_table_size/1024/1024 AS max_heap_mb;

💡 计算公式：
理论峰值内存 = Threads_connected × (sort_buffer + read_buffer + join_buffer + ...)
→ 若 max_connections=1000，且各 buffer 均为 2MB → 仅此就占 8GB！
🔍 检查是否有大量空闲连接：

SHOW PROCESSLIST;  -- 查看 Sleep 状态连接（尤其注意未关闭的长连接）
SELECT user, host, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY user,host;

3️⃣ 检查临时表与排序（OOM 杀手）

-- 查看创建了多少磁盘临时表（严重性能问题！）
SHOW STATUS LIKE 'Created_tmp%';
-- 关键指标：Created_tmp_disk_tables / Created_tmp_tables > 10% → 排序/JOIN 内存不足
SHOW STATUS LIKE 'Sort_merge_passes'; -- > 100/小时需警惕

4️⃣ 检查查询缓存（MySQL 8.0+ 已移除，但 5.7 需关！）

SHOW VARIABLES LIKE 'query_cache%';
-- 若启用（query_cache_type=1），且 query_cache_size > 0 → **强烈建议关闭**（锁竞争严重，反而耗内存）
SET GLOBAL query_cache_type = 0; -- 临时关闭
-- 永久关闭：my.cnf 中注释或设 query_cache_type=0

5️⃣ 检查其他内存大户（插件/日志）

-- 检查 Performance Schema（默认开启，但可能吃内存）
SHOW VARIABLES LIKE 'performance_schema';
-- 查看其内存使用（5.7+）
SELECT * FROM performance_schema.memory_summary_global_by_event_name 
WHERE event_name LIKE 'memory/performance_schema/%' 
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 5;

-- 检查慢查询日志缓冲区（若开启且过大）
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'log_output'; -- FILE 方式比 TABLE 更省内存

⚙️ 三、安全调优策略（按优先级排序）

✅ 第一步：优化 `innodb_buffer_pool_size`（最有效）

黄金法则：
innodb_buffer_pool_size = (总内存 × 75%) - 其他进程预留内存（如 OS、Redis、应用）
→ 例如：32GB 服务器 → 24GB 是安全上限，但必须结合 hit_rate 调整！

动态调整（无需重启）：

SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024; -- 12GB

永久生效：编辑 /etc/my.cnf（CentOS）或 /etc/mysql/my.cnf（Ubuntu）

[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8  # 内存>6GB时设为8，减少锁争用

✅ 第二步：收紧连接与 per-connection 内存

# my.cnf 中调整（避免全局设大值！）
max_connections = 200          # 根据业务QPS压测确定，非越大越好
wait_timeout = 60              # 空闲连接60秒断开（应用层也应配连接池）
interactive_timeout = 60

# 关键！降低 per-connection 缓冲区（根据慢查询分析结果调）
sort_buffer_size = 256K       # 默认2M → 大幅下调（除非有超大ORDER BY）
join_buffer_size = 256K       # 同上
read_buffer_size = 128K
read_rnd_buffer_size = 256K

# 临时表限制（防OOM）
tmp_table_size = 64M
max_heap_table_size = 64M     # 必须等于 tmp_table_size！

✅ 第三步：禁用无用功能

# my.cnf
skip_log_bin                 # 若非主库，关闭二进制日志（省IO+内存）
log_error_verbosity = 1      # 减少错误日志内存占用（MySQL 8.0+）
performance_schema = OFF     # 若不用性能分析，彻底关闭（5.7+ 可关）
query_cache_type = 0         # MySQL 5.7 必关！

✅ 第四步：操作系统级优化（防止OOM Killer杀MySQL）

# 查看当前OOM分数（分数越高越容易被kill）
cat /proc/$(pgrep mysqld)/oom_score_adj
# 降低MySQL被kill概率（-1000=永不kill，-500=较低概率）
echo -500 | sudo tee /proc/$(pgrep mysqld)/oom_score_adj
# 永久生效（Ubuntu/Debian）：
echo 'vm.oom_kill = 0' | sudo tee -a /etc/sysctl.conf
sudo sysctl -p

🚨 四、必须做的事后验证

压测对比：用 sysbench 或业务流量验证调优后 QPS/延迟是否提升；
监控基线：用 Prometheus + Grafana 监控 Innodb_buffer_pool_hit_rate、Threads_connected、Created_tmp_disk_tables；
日志审计：开启慢查询日志（slow_query_log=ON, long_query_time=1），用 pt-query-digest 分析 TOP SQL；

检查锁与死锁：

SHOW ENGINE INNODB STATUSG  -- 查看 LATEST DETECTED DEADLOCK
SELECT * FROM information_schema.INNODB_TRX; -- 长事务

📌 终极建议（避坑指南）

场景	正确操作	错误操作
Buffer Pool hit_rate 99%	保持当前大小，不要盲目缩小（会大幅增加磁盘IO）	`innodb_buffer_pool_size = 2G`（导致性能雪崩）
大量 Sleep 连接	应用层修复连接池泄漏 + 调 `wait_timeout`	`max_connections = 1000`（加剧内存压力）
频繁磁盘临时表	优化SQL（加索引、拆分复杂JOIN）+ 调 `tmp_table_size`	只调大 `tmp_table_size`（治标不治本）
MySQL 8.0+	关闭 `performance_schema`（若不用）	保留默认开启（默认吃 500MB+ 内存）

🔧 附：一键诊断脚本（保存为 `mysql_mem_check.sh`）

#!/bin/bash
echo "=== MySQL Memory Diagnosis ==="
echo "1. Process RSS:"
ps -o pid,user,%mem,vsz,rss,comm -C mysqld
echo -e "n2. InnoDB Buffer Pool Stats:"
mysql -Nse "SELECT ROUND(@@innodb_buffer_pool_size/1024/1024/1024,2), ROUND((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)*100.0/Innodb_buffer_pool_read_requests,2) FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'"
echo -e "n3. Connection Count:"
mysql -Nse "SELECT COUNT(*) FROM information_schema.PROCESSLIST"
echo -e "n4. Disk Temp Tables:"
mysql -Nse "SELECT Variable_value FROM information_schema.GLOBAL_STATUS WHERE Variable_name='Created_tmp_disk_tables'"

运行：chmod +x mysql_mem_check.sh && ./mysql_mem_check.sh

如需进一步分析，请提供：
✅ SHOW VARIABLES LIKE '%buffer%'
✅ SHOW STATUS LIKE 'Innodb_buffer_pool_%'
✅ free -h 和 ps aux --sort=-%mem | head -5 输出
我可为你定制调优方案。

💡 记住：内存不是越少越好，而是让每字节都高效服务业务。调优的目标是消除浪费，而非压缩资源。