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

MySQL 占用 80% 内存属于严重异常（除非你明确配置了超大缓冲池且服务器专用于 MySQL），需系统性排查。以下是实战导向的排查与调优流程，兼顾 CentOS（RHEL系）和 Ubuntu（Debian系）差异，避免盲目调参：

🔍 一、快速定位：确认是否真由 MySQL 引起？

# 1. 查看整体内存使用（排除其他进程干扰）
free -h && echo "---" && ps aux --sort=-%mem | head -10

# 2. 精确查看 mysqld 进程实际 RSS 内存（非虚拟内存 VIRT）
ps -o pid,user,%mem,rss,comm -C mysqld

# ✅ 关键指标：RSS（Resident Set Size）是否真的接近总内存80%？
# ❌ 注意：top/htop 显示的 %MEM 可能因共享库被重复计算，以 RSS 为准

⚠️ 常见陷阱：mysqld 进程 RSS 高 ≠ MySQL 内存泄漏！InnoDB Buffer Pool、OS Page Cache、临时表/排序缓存等均计入 RSS。

🧩 二、深入分析 MySQL 内存消耗来源（登录 MySQL 执行）

-- 1. 查看关键缓冲区配置（是否远超物理内存？）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'read_buffer_size';

-- 2. 实时内存使用情况（MySQL 5.7+，需 performance_schema 启用）
SELECT 
  SUBSTRING_INDEX(event_name,'/',2) AS code_area,
  format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.memory_by_thread_by_current_bytes 
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;

-- 3. 检查 InnoDB Buffer Pool 使用率（健康值应 75%~95%，过高可能碎片化）
SELECT 
  (pages_used * page_size) / 1024 / 1024 AS used_mb,
  (pages_total * page_size) / 1024 / 1024 AS total_mb,
  ROUND(pages_used/pages_total*100, 2) AS pct_used
FROM information_schema.INNODB_BUFFER_POOL_STATS 
CROSS JOIN (SELECT VARIABLE_VALUE AS page_size FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'innodb_page_size') s;

-- 4. 检查是否存在大量临时表（磁盘临时表尤其耗内存+IO）
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables / Created_tmp_tables > 0.1 表示频繁落盘，需优化SQL或增大 tmp_table_size

🚨 三、高频问题诊断清单（按优先级排序）

现象	检查命令/SQL	解决方案
① Buffer Pool 配置过大	`SHOW VARIABLES LIKE 'innodb_buffer_pool_size';` 若 > 总内存70% 且服务器还跑其他服务（如 Nginx、PHP-FPM）	✅ 立即下调：`innodb_buffer_pool_size = 50%-60% of RAM`（专用DB可到75-80%）
② 大量连接 + per-connection 缓冲累积	`SHOW STATUS LIKE 'Threads_connected';` `SHOW VARIABLES LIKE '%buffer_size';`（sort/read/write_buffer_size）	✅ 降低 `sort_buffer_size`（默认2M→256K）、`read_buffer_size`（→128K） ✅ 设置 `max_connections` 合理值（如 200-500），避免连接风暴
③ 大查询生成巨型临时表	`SHOW PROCESSLIST;`（找 State=`Copying to tmp table`, `Sorting result`） `SELECT * FROM sys.statement_analysis ORDER BY exec_count DESC LIMIT 10;`	✅ 优化慢SQL（加索引、改写JOIN） ✅ 临时增大 `tmp_table_size` & `max_heap_table_size`（但需监控 `Created_tmp_disk_tables`）
④ 内存泄漏（罕见但致命）	`SHOW ENGINE INNODB STATUSG` → 查看 `BUFFER POOL AND MEMORY` 部分检查 `malloc` 分配是否持续增长	✅ 升级 MySQL 到最新稳定版（如 8.0.33+） ✅ 检查自定义 UDF 或插件
⑤ OS Page Cache 与 MySQL Buffer Pool 双重缓存	`cat /proc/meminfo \| grep -E "Cached\|Buffers"`	✅ 无需干预：Linux 的 Cached 是文件缓存，MySQL 读取 ibd 文件时会利用它，属正常现象

⚙️ 四、安全调优步骤（操作前必做！）

# 1. 备份当前配置（CentOS/Ubuntu 通用）
sudo cp /etc/my.cnf{,.backup_$(date +%F)}

# 2. 推荐基础调优（根据 16GB 内存服务器示例，按比例缩放）
# 编辑 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# --- 核心缓冲 ---
innodb_buffer_pool_size = 8G          # 物理内存的50%（非专用DB）
innodb_buffer_pool_instances = 8       # ≥8G时设为8，减少锁争用

# --- 连接与临时表 ---
max_connections = 300
tmp_table_size = 64M
max_heap_table_size = 64M

# --- per-connection 缓冲（大幅降低）---
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K

# --- 其他 ---
innodb_log_file_size = 512M            # 日志大小，建议 25% buffer_pool_size
innodb_flush_method = O_DIRECT         # 避免双缓存（Linux）
skip-log-bin                           # 非主从环境关闭binlog省内存（谨慎！）

✅ 重启生效：

# CentOS/RHEL 7+
sudo systemctl restart mysqld
# Ubuntu 16.04+
sudo systemctl restart mysql

📈 五、长期监控建议（防复发）

部署监控：
- mytop / innotop（实时查看连接、锁、缓冲）
- Prometheus + Grafana + mysqld_exporter（告警：mysql_global_status_memory_used > 85%）

日志审计：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

定期检查：

# 每周运行：检查内存相关状态
mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%'; SHOW GLOBAL STATUS LIKE 'Threads_%';"

💡 终极原则

不要迷信“调大就快”：sort_buffer_size 从 2M→16M 不会让查询变快，反而让100个连接吃掉1.6GB内存。
专用数据库服务器：可分配 70-80% 内存给 innodb_buffer_pool_size。
混合服务器（Web+DB）：必须为 OS、Web 服务预留至少 3-4GB，Buffer Pool ≤ 50%。
升级优于调优：MySQL 8.0 的内存管理比 5.6/5.7 更高效，碎片更少。

如需进一步诊断，请提供：

free -h 输出
ps aux --sort=-%mem | head -5
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"

我可帮你定制调优方案 👨‍💻

✅ 附：一键检查脚本（保存为 mysql_mem_check.sh）：

#!/bin/bash
echo "=== System Memory ==="; free -h
echo -e "n=== MySQL Process RSS ==="; ps -o pid,user,%mem,rss,comm -C mysqld
echo -e "n=== MySQL Key Variables ==="; mysql -N -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'max_connections'; SHOW VARIABLES LIKE 'tmp_table_size';"
echo -e "n=== MySQL Memory Status ==="; mysql -N -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%'; SHOW GLOBAL STATUS LIKE 'Threads_connected';"

执行后结果贴出，立刻定位根因！