MySQL实例内存一路狂奔swap被吃穿mysqld进程的RES物理内存占用越来越高重启后没几天又被“打回原形”。这个问题几乎所有搞过MySQL运维的人都遇到过。它不像慢查询那样有明确的日志可查也不像死锁那样直接报错而是以一种“温水煮青蛙”的方式把服务器内存耗干最后连SSH都卡到没法敲命令。我花了不少时间跟这个问题较劲从top命令看到表象到翻遍performance_schema和sys库找证据再到逐一排查大Buffer、线程栈、临时表和各种“一次性”的大查询最后总结出了一套适合大多数生产环境的问题排查路径。这篇文章就是把这套路径拆开揉碎每一步该看什么指标、怎么解读、如何确认根因全部记录下来供遇到同类问题的朋友直接参考。1. 明确问题边界到底是“占用高”还是“内存泄漏”拿到“MySQL内存占用过大”这个命题第一步不是急着调参数而是先分清问题属于哪一类。这一步如果搞错了方向后面所有的排查都会白费功夫。1.1 区分“高水位正常”与“异常上涨”正常运行的MySQL实例内存占用本来就很高。InnoDB的缓冲池Buffer Pool默认会吃掉系统物理内存的相当大一部分如果配置了128G内存innodb_buffer_pool_size设为96G那top看到mysqld进程RES高达90多G其实是“意料之中”的。这种高水位不是问题只要内存确实花在了缓存数据页上而且没有持续上涨就不是异常。真正的异常有两种表现持续上涨内存占用随着时间推移不断爬升清理缓存或者空闲状态下也不回落最终触发OOM或者swap。突增后不回收在某次大查询、大批量导入或者备份操作之后内存涨到高位操作结束后却一直没有降下来。这两种情况对应的排查重点是截然不同的。第一种偏向于内存分配器碎片化、线程栈累积、系统表缓存膨胀第二种则更像是大事务、大排序或临时表导致的瞬时内存峰值没有及时释放。我的建议是先花两到三分钟把“内存为什么会高”这个问题定性。确认了是“高水位正常”还是“异常上涨”下面的排查才有意义。1.2 从操作系统层面获取第一手证据要判断异常上涨首先要有基线数据。用top或free只能看到当前时刻的快照必须结合历史趋势才能判断“到底涨了多少”。我通常先用下面这组命令拿到基础数据# 查看系统整体内存状况 free -h # 查看mysqld进程的详细信息RSS是物理内存VSZ是虚拟内存 top -p $(pgrep mysqld) # 持续观察mysqld进程的内存变化每5秒刷新一次 top -d 5 -p $(pgrep mysqld) # 查看swap占用情况 swapon -s cat /proc/meminfo | grep -E SwapTotal|SwapFree|Dirty这里有几个非常容易踩坑的细节VSZ虚拟内存大不代表什么很多程序预分配的虚拟内存远大于实际使用量。真正需要关注的是RSS减去共享内存那部分之后的数值这个才是进程实际独占使用的物理内存。另外free -h里的buff/cache一栏有一部分是操作系统用来做文件缓存的这部分内存理论上可以被回收但MySQL进程自身的RSS持续上涨跟buff/cache没什么关系排查时要分开看待。2. 内存画像先搞清楚MySQL的内存都花在哪了这一步是整个排查过程的核心不能靠猜要靠performance_schema的统计数据给出答案。2.1 查看内存分配器和全局缓冲配置MySQL 5.7及其实践决定performance_schema在内存统计方面做得相当细致。只要编译时没有关闭performance_schema并且运行时有performance_schema ON就能直接查询内存分配情况。先把关键缓存配置全部列出来看看有没有明显不合理的配置SHOW VARIABLES WHERE VARIABLE_NAME IN ( innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, max_connections, table_open_cache, performance_schema_max_memory_classes, tmp_table_size, max_heap_table_size, sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size, binlog_cache_size );上面的每一项都是MySQL内存的重要组成部分但它们各自扮演的角色差异很大innodb_buffer_pool_size占用最大的单一内存区域默认是128M生产环境一般会调到物理内存的50%-70%。它缓存数据页和索引页显著提升查询效率。key_buffer_sizeMyISAM引擎的键缓存如果已经全面使用InnoDB引擎这项可以设置得很小8M即可。很多旧配置模板还留着这个参数白白浪费内存。max_connections与各线程级缓冲区的乘积这是“连坐”内存的大头。每增加一个连接MySQL要为其准备sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size等线程级缓冲区。虽然这些缓冲大多按需分配但极端情况下每个连接都可能同时把各缓冲区用到最大内存上限就是这几项的乘积。tmp_table_size / max_heap_table_size控制内存临时表的上限。查询中如果使用了GROUP BY、ORDER BY、DISTINCT等操作且结果集超出这个阈值就会把临时表写到磁盘内存中只保留一部分。2.2 直接用performance_schema查内存占用Top 10在确认配置参数之后用下面这条SQL直接查看MySQL内部各模块实际消耗内存的真实情况SELECT event_name, current_alloc, high_alloc, high_number_of_bytes_alloc FROM sys.memory_global_by_current_bytes WHERE current_alloc 0 ORDER BY current_alloc DESC LIMIT 20;这条查询的输出结果通常能看到memory/sql/THD、memory/innodb/mem0mem、memory/sql/String::value、memory/sys/schema这类条目。它们分别对应memory/sql/THD每一个会话线程及其内部数据结构占用的内存。如果这里累积出的数值非常大基本可以断定是“连接数过多”或“单连接内做了大量操作”。memory/innodb/mem0memInnoDB缓冲池之外的内存池开销包含自适应哈希索引Adaptive Hash Index等。如果这里明显异常多半是因为热点索引频繁被查询自适应哈希索引占用的内存水涨船高。memory/sql/String::valueSQL语句或结果集字符串分配的内存。这里出现峰值通常意味着某条SQL构造了很大的临时字符串比如GROUP_CONCAT拼接大量结果或者预处理语句Prepared Statement没有正确释放。拿到这个输出之后排查范围就能立刻缩小到“哪一个模块在吃内存”。这是整条排查路径上性价比最高的一步强烈建议写在笔记本里下次碰到类似问题直接先跑这条SQL。2.3 评估InnoDB缓冲池的真实命中率innodb_buffer_pool_size是内存大户所以需要确认“占得是否合理、是否有必要建这么大”。用下面这条查询可以评估缓冲池的读写命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads; SHOW GLOBAL STATUS LIKE Innodb_pages_read; SHOW GLOBAL STATUS LIKE Innodb_pages_written;然后手动计算一下逻辑读与物理读的比值缓冲池命中率 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)正常生产环境下这个命中率应该高于99%如果明显低于这个水平说明缓冲池偏小调大innodb_buffer_pool_size是合理方向。反之如果命中率已经接近99.9%还持续涨内存就要怀疑是不是缓冲池被“无效数据页”污染了比如全表扫描带来的大量冷数据页挤占了热点数据页的位置。InnoDB缓冲池本身还包含一个预读机制当MySQL检测到顺序扫描模式时会提前把相邻的数据页读入缓冲池这个过程叫linear read ahead。如果业务里有大量全表扫描预读会把很多无辜的数据页加载到内存里占着位置又用不上造成缓冲池实际可用空间变小进而触发MySQL把更多热数据页淘汰掉恶性循环浮出水面。3. 从“进程层”到“SQL层”把占用高与真正的元凶对上号全局缓存配置看完了接下来就要面对最琐碎但也最常见的问题某一个连接或某一条SQL把内存推高。这时候不能只盯全局指标要把粒度缩小到会话级别甚至SQL级别。3.1 通过sys库找出“吃内存”的会话MySQL 5.7以上版本自带的sys库封装了很多高价值的诊断视图。查看当前正在执行的SQL占用的内存有两条SQL几乎可以包打天下-- 查看当前所有连接的状态及内存开销 SELECT thread_id, processlist_id, user, db, command, time, state, current_statement, sys.format_bytes(current_alloc) AS current_alloc FROM sys.session LEFT JOIN sys.memory_by_thread_by_current_bytes USING (thread_id) ORDER BY current_alloc DESC LIMIT 20;这条SQL会把当前每个线程分配的字节数、正在执行的语句、连接来源全都列出来。正常情况下内存占用排名靠前的几条就是导致内存异常的疑似元凶。如果排名靠前的全是Sleep状态的连接那就很可能是客户端拿了连接不干活也不释放导致MySQL为它维护的空闲会话占了大量内存。还有一种特殊场景连接数没有激增但每条连接都在跑一个重型的排序查询。这时sys.memory_by_thread_by_current_bytes会显示出挤满内存的sort_buffer。要避免这类问题不光要从SQL层优化减少排序行数还可以调低sort_buffer_size避免每连接都预留过大内存。3.2 关注临时表溢出对内存的连累临时表是MySQL内存消耗的“隐形杀手”。很多人以为只有GROUP BY才产生临时表其实ORDER BY、DISTINCT、UNION、带有内存临时表辅助的子查询都会在内存里创建临时结果集。默认情况下内存临时表的上限是tmp_table_size和max_heap_table_size两者中的较小值。一个常见的坑是max_heap_table_size设得很小比如16M而tmp_table_size设得很大比如256M结果实际生效的是16M导致查询稍大点就要落盘频繁落盘又会引发磁盘临时表的挤压拉高并发I/O。更糟的是在某些排序算法如归并排序中即便落盘内存里残留的缓冲区碎片也没有被及时回收累积后会让进程RSS缓慢上升。查看临时表的创建情况SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables;如果Created_tmp_disk_tables / Created_tmp_tables的比例长期超过25%就要认真审视大查询逻辑和临时表参数了。临时表导致的内存问题需要同时看这两个状态值因为它们直接反映了“内存临时表转磁盘”的发生频率。3.3 大事务与长连接的内存累积效应事务处理过程中InnoDB需要额外维护回滚段undo log、锁信息、以及事务对应的内存快照。大事务持有大量未提交的变更时内存增长是必然的。但这里有个容易忽略的点事务提交后这部分内存并不总是立即归还给操作系统MySQL自带的内存分配器需要把这些内存块重新整理碎片多的时候会暂时吞下这部分空间不释放。查看当前是否有长事务SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_seconds, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;如果一个事务已经存活了几分钟甚至几十分钟而且trx_rows_modified数值很大那它对内存的影响一定不小。长连接的问题更隐蔽连接不关闭线程对象和缓存语句就一直在如果有预处理语句Prepared Statement不断执行memory/sql/Prepared_stmt这一项的累积会非常可观。这种情况下最简单的止损方案就是从连接池侧加上“连接闲置超时”的回收机制从源头防止连接无限堆积。4. 排查现场实录一套逐步收紧的完整操作模板说了那么多原理和概念把一套可以照抄的排查步骤完整记录下来直接对着执行就能用。这套步骤在我自己的环境里验证过适用于MySQL 5.7和8.0。4.1 第1步采集现场快照并确认“异常定位”接到内存告警后我一般不做任何变更先采集这批现场数据# 进程层快照 free -h ps aux --sort-%mem | head -n 10 # 抓取mysqld进程的内存实时变化 pidstat -r -p $(pgrep mysqld) 5 5然后进入MySQL执行SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Max_used_connections; SHOW ENGINE INNODB STATUS\G其中Max_used_connections非常重要它记录了实例启动以来并发连接数的高水位。如果当前Threads_connected没爆但Max_used_connections远超预期说明前面某段时间发生过连接风暴内存虽然从风暴中恢复了一部分但分配器没有把碎片归还给操作系统。4.2 第2步列出内存消耗Top N并把账对平接着用前面提到的查询把内存占用Top 5的模块拉出来确认总额跟RSS增量能否对应上SELECT SUBSTRING(event_name, 1, 50) AS event, SUM(current_alloc) / 1024 / 1024 AS current_alloc_mb FROM performance_schema.memory_summary_global_by_event_name GROUP BY event ORDER BY current_alloc_mb DESC LIMIT 10;如果模块总额远小于RSS增幅说明内存并不在MySQL内部可追踪的模块里而是被分配器或外部库比如jemalloc的arena缓存吞掉了。这时需要用操作系统的pmap来交叉验证pmap -x $(pgrep mysqld) | sort -k3 -n -r | head -n 20pmap能看到进程地址空间里每块映射的大小如果存在大量64K、128K退回零散的小块说明glibc的malloc产生了严重碎片。这里有一个经验之谈使用jemalloc替代glibc malloc之后MySQL进程RSS的长期缓慢上涨趋势大多能得到实质性的改善尤其在高并发大查询的场景下效果非常明显。4.3 第3步检查连接数与线程级缓冲乘积连接数连接着线程级缓冲区这是最容易排查也最容易踩雷的一环。用以下方式算出“理论上限”内存理论上限 ≈ innodb_buffer_pool_size key_buffer_size max_connections × (sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size) table_open_cache × 单张表结构占用的缓存开销把计算结果跟实际RSS对比如果实际占用明显超出理论上限多半在某个地方突破了常规分配路径比如大字段、大对象或者内部临时表翻倍。如果实际占用正好卡在理论上限附近那问题核心就是配置参数确实开得太大需要按业务调低。有个非常经典的案例某系统的max_connections是2000sort_buffer_size是16Mjoin_buffer_size是16M如果800个并发连接同时触发大的SORT和JOIN单线程级缓冲就可能冲到32M2000个连接全部用满会达到64G的内存开销这还不算其他缓冲。真相看起来非常骇人光线程级缓冲就能吃光整台服务器。4.4 第4步阅读错误日志定位OOM与重启事件这个问题还有个很关键的排查视角翻MySQL错误日志和系统日志看看此前是否发生过OOM或者MySQL被内存耗尽拖垮导致重启的事件。# 查看系统日志中的OOM记录 dmesg | grep -i -E out of memory|oom|killed process | tail -n 20 # 查看MySQL错误日志中的异常关闭与启动记录 grep -n -E Out of memory|Segmentation fault|Crash recovery|InnoDB: Shutdown completed /var/log/mysql/error.log如果dmesg里看到oom-killer把mysqld或其他进程杀了那基本可以判定内存耗尽发生在操作系统层面。需要特别注意OOM日志中杀掉的进程如果杀掉的是其他服务进程如Redis、Nginx更说明mysqld的内存占用把自己的“邻居”给挤死了这种情况往往是MySQL配置参数过度占用导致的。4.5 第5步针对性止损并保留证据排查清楚根因之前可能需要先采取止损措施。常用的几类止损操作临时降低并发连接数在应用层或者数据库侧尽快将max_connections调低把连接池收缩到正常水位。杀掉大批空闲连接通过information_schema.processlist找出Sleep超过阈值且无事务的会话用KILL清理掉这一步能在几秒内释放大量内存。清理堆外缓存如果使用了QPS压力较大的查询缓存Query Cache5.7.20之前且命中率很低直接SET GLOBAL query_cache_type OFF并清空查询缓存。止损的同时记得把现场快照保存在另一台机器上因为重启后performance_schema的统计就会被清零很多证据就永远找不回来了。5. 用一份速查表收尾常见内存异常场景与排查方向在长期的排查过程中我把各种遇到的场景归类整理成了一张速查表遇到什么问题直接按表对着查非常省心。这里把这张表分享出来。异常场景特征优先排查项常用确认SQL/命令常规解决思路内存持续缓慢上涨实例运行越长RSS越高内存分配器碎片、Prepared Statement缓存、表缓存膨胀sys.memory_global_by_current_bytes、pmap -x、SHOW GLOBAL STATUS LIKE Prepared_stmt_count切换jemalloc定期清理无用预编译语句调低table_open_cache并增加table_open_cache_instances并发连接高时内存飙升连接数下降后内存不复原连接线程栈与线程级缓冲导致的分配器占用sys.session、SHOW VARIABLES LIKE %buffer_size调低单连接各buffer_size连接池设置合理的最大连接数尽量用短连接执行批量任务特定时间段内存突增如凌晨批量任务大查询、大批量导入、备份任务、报表统计SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables、pt-query-debug或慢查询日志优化大查询拆分为多个小批次事务开启并行备份并限制备份对数据库内存的占用缓冲池命中率低且RSS很高全表扫描导致预读污染缓冲池命中率计算SQL、SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_ahead优化SQL避免全表扫描适当扩大缓冲池在业务允许情况下使用INDEX提示强制走索引容器或虚拟化环境下内存被RSS限制误杀cgroup内存限额不足cat /sys/fs/cgroup/memory/memory.limit_in_bytes显示设置innodb_buffer_pool_size和max_connections的总和小于cgroup限额预留一定缓冲TPS/QPS不高但内存居高不下查询缓存5.7早期颗粒度太细导致的缓存碎片SHOW GLOBAL STATUS LIKE Qcache%关闭查询缓存或设置query_cache_type DEMAND且小容量这张表覆盖了大多数我实际见过的“内存占用过大”场景。重点不是背下来而是理解每一行背后的因果关系内存不是凭空涨的它总是有去向的排查的目的就是把去向一五一十地找出来。6. 最后再分享三个我实际踩过的坑排查MySQL内存问题真正决定效率的不只是SQL写得熟不熟还在于对几个“反直觉”现象有没有充分心理准备。下面这三个坑我几乎每次都有人因为同样的误解而绕远路。第一个坑别把buff/cache当成MySQL内存。Linux的free -h里buff/cache一栏包括文件页缓存和部分slab对象这部分内存被标记为“可回收”系统内存紧张时会被内核自动回收。但MySQL进程自己的RSS是独立于buff/cache的。如果看到free -h显示available很小就直接去调低innodb_buffer_pool_size很容易判断错对象。务必先确认mysqld这个进程的RSS趋势再决定调什么。第二个坑别在所有版本都一刀切地去调小innodb_buffer_pool_size。在MySQL 5.7及以上版本innodb_buffer_pool_size可以动态调整但只增不减是安全方向。频繁调小缓冲池会造成大量脏页强制刷新和热数据页淘汰I/O压力会瞬间爆炸。如果只是临时为了给其他进程腾内存应该优先调整连接数和线程级缓冲而不是动缓冲池。第三个坑别忽略监控数据的时间粒度。只看某一时刻的top和free很可能错判内存问题的真实面貌。我之前处理过一个案例业务方反馈“MySQL把内存吃光了”结果用历史监控图一看内存是某天凌晨01:30突然涨上去的和时间点对应的正好是当天的全量数据导出任务。因为没有留存分钟级的内存监控很多排查方向一开始都跑偏了。建议给任何接手的数据库资产提前配置好分钟级别的mysqld进程RSS监控这是规避“事后失忆”最廉价的手段。排查MySQL内存占用过大的过程本质上就是一个“缩小嫌疑人范围”的过程从操作系统到实例、从实例到模块、从模块到会话、从会话到SQL每一层都在过滤无关因素。只要把上面这些步骤按顺序走一遍基本都能把内存问题的根因锁定在很小的范围内。希望这份经验记录能帮大家少走几步弯路。