为什么MySQL占用内存远超Buffer Pool该如何排查参数-mysql教程-PHP中文网

mysql 5.7+ 动态调整 innodb_buffer_pool_size 时，若设置值不满足 instances × chunk_size 的整数倍，系统会向下取整（非向上），导致实际生效值小于预期（如设50g、instances=8、chunk_size=128mb→最小单位1024mb，实际生效48g），需用 select @@innodb_buffer_pool_size 验证真实值。

为什么mysql占用内存远超buffer pool该如何排查参数

查`innodb_buffer_pool_size`是否被误设或截断

Buffer Pool 占用远低于实际 RSS，说明大量内存来自别处；但若它本身就被设得离谱（比如 64G 机器配了 56G），就先得确认这个值是否真生效。MySQL 5.7+ 支持动态调整，但innodb_buffer_pool_instances必须整除innodb_buffer_pool_size，否则系统会向下取整到最近的合法块大小——例如设了 innodb_buffer_pool_size = 50G 但 innodb_buffer_pool_instances = 8，实际生效可能只有 48G，而剩余 2G 内存可能被其他模块“补位”占用，造成统计错觉。

实操建议：

运行 SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gb; 确认当前生效值，不是只看配置文件
检查 SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';，确保实例数 × 每实例大小 ≈ 总大小（如 8 × 6G = 48G）
若用容器部署，确认 cgroup 限制未导致内存分配失败后 fallback 到非标准路径（如 jemalloc arena 扩张）

盯`tmp_table_size`和`max_heap_table_size`是否不一致或过大

这两个参数控制单个连接能使用的内存临时表上限，且**必须相等**。设成 1G 看似合理，但如果业务里有几十个并发执行 GROUP BY 或 ORDER BY 的查询，每个都试图建 1G 临时表（哪怕实际只用 200MB），MySQL 就会为每个连接预留接近该上限的虚拟内存（VmSize），而 RSS 可能缓慢爬升——尤其在 THP（透明大页）开启时，malloc 分配的页无法及时归还 OS。

常见错误现象：

SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 中 Created_tmp_disk_tables 高，但 Created_tmp_tables 更高 → 说明大量内存临时表被创建又快速释放，但内存没回收
top 显示 mysqld RSS 持续上涨，free -h 的 available 却不明显下降 → 典型的 THP + 大临时表组合问题

实操建议：

统一设为 256M（非 1G），并确认两者值完全一致：SELECT @@tmp_table_size, @@max_heap_table_size;
在宿主机上运行 cat /sys/kernel/mm/transparent_hugepage/enabled，若输出为 [always]，立即关掉：echo never > /sys/kernel/mm/transparent_hugepage/enabled

抓`sort_buffer_size`、`join_buffer_size`这类线程级参数的隐性叠加

这些参数是“每连接”分配的，不走 Buffer Pool 统计，也不受 performance_schema.memory_summary_by_thread_by_event_name 完全覆盖。设成 4M 看似安全，但若 max_connections = 2000，理论峰值内存占用就是 2000 × 4M = 8GB —— 这部分在 top 里算进 mysqld，但在 PFS 查内存分布时可能只显示为 memory/sql/filesort_buffer 下的一小撮，容易被忽略。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

使用场景：

应用连接池未正确 close()，大量 Sleep 连接长期存在（SHOW PROCESSLIST 中 Time > 300 的连接数 > 100）
慢查询中频繁出现 Using filesort 或 Using join buffer（EXPLAIN FORMAT=JSON 可见）

实操建议：

把 sort_buffer_size 和 join_buffer_size 降回默认值（256K–4M），除非明确某类查询压测证明需要调大
同步收紧 wait_timeout 和 interactive_timeout 到 120 秒，避免空闲连接滞留
用 SELECT user, host, COUNT(*) FROM information_schema.processlist GROUP BY user, host; 快速定位异常连接来源账号

用`sys.memory_global_by_current_bytes`定位“看不见”的内存大户

当 performance_schema.memory_summary_global_by_event_name 前 10 名加起来只占 RSS 的 30%，剩下 70% 就得靠 sys 库的聚合视图挖——它能把底层 malloc 分配按模块归类，比如 memory/mysys/lf_hash（锁哈希）、memory/sql/replication（复制缓存）、甚至 memory/jit/bytecode（MySQL 8.0+ JIT 编译器）。

性能影响：

启用 performance_schema 本身有 3%–5% CPU 开销，但内存统计必须开；若关闭了，sys 视图将为空
sys.memory_global_by_current_bytes 查询比直接查 PFS 表更直观，但依赖 sys schema 是否已安装（MySQL 5.7+ 默认带）

实操建议：

先确认 PFS 已启用：SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'performance_schema'; → 必须为 ON
运行：SELECT event_name, sys.format_bytes(current_alloc) AS allocated FROM sys.memory_global_by_current_bytes ORDER BY current_alloc DESC LIMIT 10;
若看到 memory/temptable/physical_ram 或 memory/sql/Query_cache 异常高（尤其 MySQL 8.0+ 已移除 Query Cache，出现即为插件残留或误配），立刻停用相关组件

真正难处理的，是那些不进 PFS 统计、又不随连接释放的内存：jemalloc 的 arena 碎片、THP 的大页映射、binlog cache 在 GTID 模式下的预分配。它们不会在 SHOW ENGINE INNODB STATUS 里露头，也不会被 sys 视图完全覆盖——得结合 /proc/<pid>/smaps</pid> 里 AnonHugePages 和 MMUPageSize 字段交叉验证。