最近连续处理了几台 MySQL 实例内存居高不下的问题正好借这篇文章把完整的排查思路和最终调整方案梳理一遍。事情起因其实很简单一台 8G 内存的服务器上mysqld 进程的 RSS 一路涨到 2.3G 左右单看数字好像还能接受但问题在于这台机器同时跑着其他服务系统已经出现 swap 用量缓慢爬升的迹象这就有点危险了。swap 一旦开始持续增长意味着物理内存真的吃紧后续很容易引发 IO 抖动最终拖垮业务响应。如果你也遇到类似情况比如 mysqld 进程占内存过高、free -g 看内存余量越来越少或者 swap 使用量悄悄上涨这篇文章应该能给你一套可以照着做的排查路径。整个过程围绕一个核心思路先弄清楚内存到底被谁吃了是全局缓冲、连接会话缓冲还是某个大查询临时占用的再决定要不要动手调参。1. 内存占用高的常见来源先给 mysqld 内存做个分类排查内存问题第一步不是急着调参数而是先搞清楚 mysqld 的 RSS 内存里都装了什么。从实践来看mysqld 进程占用的内存大致可以分成三类每类的特征和排查方式都不一样。第一类是全局缓冲池最典型的就是 InnoDB 的 buffer pool也就是 innodb_buffer_pool_size。这部分内存是 MySQL 启动时就预分配的用来缓存数据页和索引页是整个实例内存占用的绝对大头。很多生产环境里 innodb_buffer_pool_size 会配置到物理内存的 50% 到 75%如果表的数据量根本没那么大这部分内存就等于白占着这就是最常见的内存虚高原因。MyISAM 引擎对应的 key_buffer_size 也属于这一类不过它只对 MyISAM 表生效如果业务里 MyISAM 表不多这个参数保持默认值就行。第二类是会话级别session的缓冲这就比较隐蔽了。包括每个连接独立的 sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size、myisam_sort_buffer_size 等。这些缓冲的特点是每个连接都会有自己的一份而且很多参数在连接建立时就会预分配而不是真正用到才分配。连接数一多这部分内存就像滚雪球一样膨胀。我见过一个比较夸张的案例max_connections 设了 5000myisam_sort_buffer_size 设了 256Msort_buffer_size 设了 2M结果 MySQL 刚启动还没有任何业务流量进程就已经占了几个 G 内存。算一下就知道100 个连接光是 myisam_sort_buffer_size 就是 25G相当吓人。第三类是临时性内存比如排序操作、临时表、group by、join 过程中产生的内存消耗。这部分内存用完会释放正常情况下不会长期占用但如果存在慢查询或者大 SQL 长时间运行就会有一批连接持续占着大量内存不放。这类问题通过慢查询日志和 performance_schema 一般都能定位到具体 SQL。判断优先级的方法也很简单先用几个命令看当前状态。show variables like %buffer_pool% 看全局缓冲配置show global status like %threads_connected% 看连接数show global status like max_used_connections 看历史最大连接数再 show processlist 看当前连接状态。这几条命令跑完内存的主要去向基本就能摸出个大概。2. 我这台实例的具体情况连接数正常但是会话缓冲吃掉了大头拿我这次排查的实例来说服务器内存 8Gmysqld 进程 RSS 2.3G。先用 show variables 把关键参数逐个过了一遍发现一个很典型的特征这台机器的 MySQL 配置基本是默认安装状态全局缓冲的设置非常保守但会话级别的参数在一堆连接叠加下反而成了内存消耗的主力。具体参数如下innodb_buffer_pool_size 128M这个值接近默认配置。对 8G 内存的机器来说128M 的 buffer pool 说明业务基本没做过优化反而衬托出内存大头不在 InnoDB 缓存。key_buffer_size 8M同样是默认值。如果 MyISAM 表不多这个参数对内存影响很小主要影响 MyISAM 表的磁盘 IO 效率。max_connections 151默认值。这里要注意max_connections 本身不直接占内存但它决定了最多能有多少个连接每个连接都会分配自己的一套 buffer所以它才是内存消耗的放大器。myisam_sort_buffer_size 8M这是 myisam 表排序用的缓冲每个会话都会分配。sort_buffer_size 2M每个会话排序用的缓冲同样按连接数翻倍。join_buffer_size 2M每个连接做 join 时分配的缓冲。read_buffer_size 2M每个连接做顺序扫描时分配的缓冲。read_rnd_buffer_size 2M每个连接做随机读时分配的缓冲。bulk_insert_buffer_size 8MMyISAM 批量插入用的缓冲。table_open_cache 2000table_definition_cache 1400这两个控制表结构、文件描述符等元数据的缓存表数量特别多的时候会占一些内存但通常不是大头。thread_cache_size 9线程缓存数量本身占的内存很小。tmp_table_size 16Mmax_heap_table_size 16M这两个是内存临时表的上限。超过上限会转磁盘临时表内存占不住但性能会降如果上限设得太大内存临时表变多内存也会涨。光看这些参数还不够还得确认当前连接数和连接状态。执行 show global status like %threads_connected% 和 show global status like max_used_connections发现当前连接数几十个历史最大连接数也不高。show processlist 看了下大部分连接处于 Sleep 状态也就是连接池里空闲的连接并没有什么大查询在跑。问题到这里就清晰了既然没有大 SQL 和慢查询积压全局缓冲也就 128M那 2.3G 的内存必然来自会话级别缓冲的累积。几十个连接每个连接分配 myisam_sort_buffer_size 8M、sort_buffer_size 2M、read_buffer_size 2M、join_buffer_size 2M、read_rnd_buffer_size 2M加在一起单个连接光这几项就接近 16M。30 个连接就是近 480M再加上线程栈、临时表、元数据缓存以及 MySQL 自身的内存分配器开销2.3G 就说得通了。这里有个容易被忽视的点Sleep 状态的连接占用内存并不会自动释放。连接还活着它的会话上下文、缓冲都还留着只有连接真正关闭这些内存才会还给系统。所以连接池里维持着大量空闲连接内存就会一直维持在高水位这跟业务是否繁忙关系不大。3. 慢查询与线程状态核查确认内存压力不来自业务 SQL在动手调参之前我习惯把业务 SQL 导致内存上涨这个可能先排除掉不然调了半天参数回头一个大查询进来内存还是照样飙。排查这一步主要看两个维度SQL 的整体分布以及当前线程都在干什么。先看命令类型分布用 show global status like %Com_% 可以拿到 select、insert、update、delete 各自的累计次数。重点不是看绝对值而是看比例是否合理以及有没有某类命令异常偏多。比如大量无索引的全表扫描select 次数会虚高而且每次扫描都可能把 read_buffer_size、read_rnd_buffer_size 用满。更精确的方式是借助 performance_schema。打开 performance_schema 后查 events_statements_summary_by_digest 这张表按累计耗时、扫描行数、临时表使用量排序可以直接锁定最耗资源的几条 SQL。再配合 sys.statement_analysis 视图可以直观看到每条 SQL 的 avg rows examined、tmp tables、sort_merge_passes 等指标。当前线程状态则用 show processlist 看注意 state 列的内容处于 Sorting result 说明正在排序Copy to tmp table 说明正在建临时表Statistics 状态往往意味着没走索引、在扫全表。我这台实例查下来睡眠连接占绝大多数活跃连接也只是普通的增删改查没有长时间占用 CPU 的大查询慢查询日志里也基本干净。综合判断后可以确定内存压力不是来自 SQL 本身而是来自会话缓冲的预分配。在这个前提下调整 myisam_sort_buffer_size 和 sort_buffer_size 才是对症下药的方案。如果你的场景里有明显的慢查询那就得先优化 SQL不然调参只是治标不治本。4. 参数调整的核心逻辑sort_buffer 的分配机制与合理取值这次调整的主要对象是两个参数myisam_sort_buffer_size 从 8M 降到 4Msort_buffer_size 从 2M 降到 256K。有人可能担心 sort_buffer_size 降到 256K 会不会太小排序性能会不会严重下降。这里需要先讲清楚 MySQL 排序缓冲的分配机制这一点很多文档没有说透。MySQL 5.7 以及之后的版本排序缓冲是动态增长模式而不是传统理解的那样一次分配固定大小。也就是说sort_buffer_size 设置的其实是每个连接的初始分配值排序过程中如果数据量超过了当前缓冲MySQL 会按需扩展直到达到上限阈值再放不下才会使用磁盘临时文件。所以把 sort_buffer_size 设成 256K并不意味着排序只能用到 256K而是连接建立时先少占内存真正需要时再增长。从这个机制出发对普通 OLTP 场景来说sort_buffer_size 设 256K 完全够用。绝大多数业务查询排序的数据量都很小可能几百 K 到 1M 就结束了初始分配 2M 和初始分配 256K 对排序耗时几乎没差别。真正受影响的是那些排序数据特别大的查询这时候 256K 初始值会更快触发磁盘临时文件性能下降明显。所以这个值怎么设取决于业务里有没有大量需要排序的查询。没有的话256K 就是合理值Percona 的默认配置也是 256K可以参考。myisam_sort_buffer_size 也是同理它针对的是 MyISAM 表的排序操作。如果业务几乎不用 MyISAM 表那这个参数设多大都是空转默认 8M 对每个连接来说都是浪费。我这次因为实例里还有少量 MyISAM 表就降到了 4M 留出余量如果表全换成 InnoDB这个参数甚至可以设成 1M 甚至更小。调整参数有两种方式。第一种是直接改 my.cnf 配置文件然后重启 MySQL 服务这样所有参数全局生效所有连接的内存都会重新分配。第二种是在线修改执行 set global 语法只对新连接生效已有连接继续使用旧值。在线修改的优点是无需重启、不影响业务缺点是内存释放不彻底想立竿见影还是得靠重启。我这次因为业务允许短时重启就采用了改配置文件加重启的方式。重启前先记录一下 mysqld 的 RSS 内存重启后再对比从 2.3G 降到了 1.6G 左右降幅约 700M。这个数字看起来不算巨大但方向是对的说明会话缓冲确实是主要的内存消耗来源。如果业务不允许重启那就用在线修改set global myisam_sort_buffer_size 4194304; set global sort_buffer_size 262144;注意这两个参数都是 session 级别的set global 只会影响之后新建的连接。已有的连接尤其那些 long-lived 的 sleep 连接还是会保留旧缓冲直到它们被关闭。这种情况下内存不会马上降下来需要一个连接重建的过程。如果想快速看到整体效果还是得在低峰期重启一次让所有连接重建。5. 进一步降低 MySQL 内存占用的进阶思路调完两个核心参数之后如果内存水平还是不理想还有几个方向可以继续深挖。这些我都实测过或者见过别人踩坑按性价比从高到低排列。第一精简连接池。很多业务侧的连接池配置非常随意最大连接数设得很大空闲超时时间设得极长。我见过一个 Java 服务连接池最大连接数 100空闲超时 8 小时结果这 100 个连接几乎全部常年维持 sleep 状态光 session buffer 就吃掉了好几百兆。把连接池最大连接数降到 20空闲超时降到 10 分钟内存立竿见影降下来而且对业务几乎无感知。这个方向往往比调 MySQL 参数更有效。第二压缩 table_open_cache 和 table_definition_cache。如果实例里只有几百张表这两个参数设 64 到 128 就够用了我见过有人按默认值 2000 来跑纯属浪费。不过要注意如果表数量真的特别多比如几千张这部分缓存就不能太低否则会导致表打开和关闭频繁反而增加 CPU 开销。第三从 SQL 层面做资源瘦身。用 performance_schema 找出扫描行数特别大、临时表使用频繁的 SQL针对性地加索引或者改写法。比如把一次处理几千行的逻辑改成分批处理把 select * 改成只查需要的列都能减少排序和临时表的压力。这一步属于长期优化但对内存稳定性的帮助非常大。第四把 MyISAM 表转成 InnoDB。MyISAM 表存在的情况下key_buffer_size 总得留一些而且 myisam_sort_buffer_size 这个参数也得保留。如果业务允许把剩余几张 MyISAM 表迁移到 InnoDBkey_buffer_size 可以设到 1M 甚至 0myisam_sort_buffer_size 也可以进一步调小内存占用能再降一截。当然迁移前需要核对锁行为、事务支持这些差异不能盲转。第五升级 MySQL 版本。MySQL 8.0 在内存管理方面相比 5.7 改进了很多包括更合理的缓冲分配策略、更高效的临时表处理、更克制的内存分配器使用。如果你的业务在 5.7 上长期内存吃紧升级到 8.0 是一个值得认真考虑的方向。不过 8.0 的默认配置和 5.7 差异不小升级前一定要做参数对比别升级完反而因为默认值不同出现新的问题。6. 排查过程中最容易忽略的两个细节最后补充两个我这次排查期间印象很深的细节希望能帮你少走弯路。第一个是关于sleep 连接为什么会导致内存不释放的疑问。这个问题不少人都问过sleep 状态的连接虽然不执行任何 SQL但它的会话内存还在。MySQL 为每个连接分配的读缓冲、排序缓冲、join 缓冲以及线程栈空间在连接存活期间都不会被回收。只有连接断开这些内存才会真正释放。所以连接池里躺着大量空闲连接内存占用就一直压在很高的水平。排查内存问题的时候一定要把连接数管理和 SQL 优化放在同等重要的位置光盯慢查询是远远不够的。第二个是关于如何精确定位是哪个连接在占用大量内存。基础方法是查 information_schema.processlistselect id, user, host, db, command, time, state, info from information_schema.processlist where command ! Sleep;配合 sys.session 视图select * from sys.session where command ! Sleep;可以快速看到当前活跃连接都在跑什么。如果遇到半夜某个时间点内存突然暴涨这类偶发问题建议提前打开 performance_schema然后查 events_statements_summary_by_thread_by_event_name按线程维度统计哪些连接累计消耗了最多内存和最多执行时间。这种问题靠复现很难靠历史数据定位才能真正找到根因。另外有一个操作习惯值得养成每次调整完参数把调整前后的 show global status 关键项、mysqld RSS 内存、连接数记录下来。信息越多下次排查就越快。我自己就是因为有之前某台机器的记录做对比才敢那么快判断出这台实例的问题在 session buffer 上。整体来说这次 mysql 占用内存过大的排查路径就是排除全局缓冲是不是过大确认连接数是否正常检查会话层缓冲参数是否被放大再确认有没有大 SQL 长期占用临时内存。四步走下来问题定位很快调整方案也有据可依。如果你的实例也出现类似症状建议先按这个顺序排查一遍大概率能比我这个案例找到更明显的内存大头。