MySQL内存占用过高排查与调优实战指南

最近又被一个老问题找上门一台8核16G的MySQL服务器内存长期占用95%以上swap被疯狂使用业务高峰期SQL响应动不动就超3秒。把服务重启能清爽一两天然后内存又慢慢“吃”回去。这种情况我排查了不下十次今天干脆把完整的排查思路、命令、调优参数和踩坑点一次整理清楚专门聊聊mysql内存占用过大这个问题。先说结论MySQL占内存这件事绝大多数情况下不是“内存泄漏”而是“分配规划不合理”。你看到的14G占用往往是全局缓存、连接会话缓冲、临时排序内存共同堆出来的。搞清楚这三块各自占了多少、为什么会占这么多问题就解决了一半。这篇文章就沿着mysql内存问题排查这条线从内存是怎么分配的、怎么查、怎么调到真实案例复盘一次讲透。不管你是刚接手数据库的新手还是已经被线上告警折磨了一周的运维这套思路都直接用得上。1. 先搞清楚MySQL的内存都装了什么1.1 全局内存与会话内存两种完全不同的账本MySQL的内存占用我习惯把它分成两大类全局内存和会话内存。全局内存是实例启动时按照参数预先分配好的不管有没有人查询那部分内存就已经占上了典型的就是InnoDB Buffer Pool、Redo Log Buffer、Key Buffer、Performance Schema自身的开销。会话内存则是每个新建连接单独分出来的每个连接都会有自己的sort_buffer_size、join_buffer_size、read_buffer_size、thread_stack、临时表空间等连接一多这部分就是几何级数地膨胀。可以这样类比全局内存是店铺的仓库和货架不管进不进来客人都在那里成本固定会话内存是每个进店客人手里的购物篮客人越多篮子占的地儿就越大。每个连接一个篮子网上很多做法是每个篮子都配得特别大结果几个人进店就把过道占满了。理解这两类内存最大的意义在于同样一个“内存过高”现象全局内存问题可以一次调参解决会话内存问题却往往需要结合连接数、业务并发特征和SQL执行特点来综合处理。如果不做区分上来就调大buffer pool很可能会进一步压垮系统。1.2 一条查询从进入到返回内存是怎么被一步步吃掉的随便发一条SQL比如SELECT * FROM orders WHERE user_id123 ORDER BY pay_time DESC LIMIT 20。这条SQL从发起到返回会在内存里经过好几道站点。首先连接层MySQL给这个会话分配线程栈和基础运行内存别看每项不大但几十上百个连接同时活着累计就非常可观。接着进入解析和优化阶段SQL语法解析、权限检查、表结构解析都会消耗全局的一些缓存结构比如表结构定义缓存table_open_cache、预读缓存等。然后到了执行阶段如果是排序操作sort_buffer_size会接管部分数据在内存里排序如果是joinjoin_buffer_size会在内存里构造连接缓存如果数据量超出了这些缓冲的阈值InnoDB还会把中间结果写到磁盘临时表但此前内存中已经经历了一次“满负荷运转”。最后结果集返回客户端前网络发送缓冲和输出缓冲也会有少量占用。所以排查内存问题时不要只盯着buffer pool一个参数。一条大排序SQL在极短时间内就能把几个连接的sort_buffer_size全部耗满这种瞬间尖峰往往就是业务卡顿的直接导火索。后面的排查步骤也是按这个内存链路逐层去看的。2. 内存占用排查从哪里开始看2.1 第一眼判断先分清是不是MySQL在吃内存拿到一台内存异常的服务器我的习惯是第一件事先看系统整体布局不要急着连进MySQL看参数。最原始的三板斧free -m top -c ps aux --sort-%mem | head -20 vmstat 1 5free -m看物理内存和swap的占用分布注意看available这一列它比free更能反映真实可用内存因为包含了可回收的Page Cache。top按内存排序确认是mysqld进程占用高还是其他进程比如Java应用、ES、Redis在抢内存。vmstat看si和so两列数值持续非零说明swap已经很紧张。这一步能帮你快速判断是整个系统资源紧张还是只有MySQL一个进程异常。很多人一上来就改MySQL参数结果最后发现是旁边一个Java应用把内存吃光了MySQL只是被系统OOM机制误杀的。这里还要提一个容易被忽略的判断点用ps看到的RES列其实是进程的“驻留内存”是物理内存里真实占用的部分。MySQL的RES通常比它的“逻辑内存配置”小一些因为它有一部分数据在OS Page Cache里也会被计入。别把这个数当成MySQL配置值的对账依据它只能说明进程实际占用的物理内存规模。如果确认是mysqld占用高再进到MySQL内部查配置和实际运行状态。2.2 MySQL内部用performance_schema直接定位“内存大户”MySQL 5.7开始performance_schema提供了非常方便的内存统计维度可以把内存按模块拆分出来。想用这个功能得先确认两件事performance_schema本身开启同时内存采集项也开启了。SHOW VARIABLES LIKE performance_schema;如果没开需要到配置文件的[mysqld]段加参数然后重启。开启后最简单的查询是SELECT * FROM sys.memory_global_by_current_bytes WHERE current_alloc 0 ORDER BY current_alloc DESC LIMIT 20;这张表会按内存事件类型汇总当前的全局内存占用输出里通常你会看到memory/innodb/buffer_pool占大头然后是各线程的堆内存、performance_schema自己的内存、临时表相关内存等。如果系统库里sys不可用直接查底层表也是一样的SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name WHERE CURRENT_NUMBER_OF_BYTES_USED 0 ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 20;通过这个输出你可以准确地说出“哪块占了多少MB”而不是靠猜。还有一个命令值得记住SHOW ENGINE INNODB STATUS\G里面BUFFER POOL AND MEMORY一节会给出InnoDB缓冲池的总大小、空闲页、数据库页等统计以及BP命中率相关数据。结合performance_schema的结果基本就能画出MySQL内存的“饼图”。2.3 状态变量与参数把配置和实际运行对起来看只看performance_schema还不够最好把“配置值”和“运行状态”放在一起对账。我常用的查询组合包括SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE sort_buffer_size; SHOW VARIABLES LIKE join_buffer_size; SHOW VARIABLES LIKE tmp_table_size; SHOW VARIABLES LIKE thread_stack; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_hit_rate; SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Created_tmp_tables;Threads_connected是当前连接数Threads_running是正在执行的线程数这两个数字直接决定会话内存的放大倍数。Created_tmp_disk_tables和Created_tmp_tables则可以反映临时表的磁盘化比例如果磁盘临时表比例很高说明内部临时表频繁超限“内存临时表→磁盘临时表”的切换正在拖慢SQL、也在透支内存。到这一步排查已经从“看系统”推进到了“看MySQL内部”下一步就是针对内存大户逐项处理。3. 常见的内存大户与针对性处理3.1 InnoDB Buffer Pool最大也是最好调的一块绝大多数MySQL实例里InnoDB Buffer Pool就是内存开支的绝对主力。它负责缓存InnoDB的表数据和索引页目的是让频繁访问的热点数据尽量停留在内存里减少磁盘IO。默认情况下innodb_buffer_pool_size是128M这显然不够生产用所以很多DBA会把它的值设到物理内存的50%到75%。这个“设到75%”的通用经验在不少场景里恰恰是内存过高的源头。我见过几台典型的故障机器配置是16G内存innodb_buffer_pool_size设成12G剩下4G要养活操作系统、连接会话、排序内存和其他进程结果必然吃紧。判断buffer pool该调多大主要看两点一是实例实际缓存的数据量增长趋势二是命中率。SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;read_requests是总读请求次数reads是真正落到磁盘的次数命中率约等于(read_requests - reads) / read_requests。命中率长期在95%以下可以适当上调长期在99%以上就说明buffer pool偏大可以去“接济”一下系统内存。注意不要只调一次就完事数据量和访问模式会变这个参数应该每三个月重新评估一次。另外MySQL 5.7以后可以设置innodb_buffer_pool_chunk_size配合多buffer pool instance使用但日常很少需要改。真正要注意的是innodb_buffer_pool_size必须设置成一个容易整除的值比如4G、8G否则实例启动后可能自动扩容到对齐值造成意外的内存多占。MySQL 8.0里buffer pool默认会有多个instance每个instance又分成多个chunk配置不合理时也会导致内存分配和预期不一致这些都是老江湖踩过坑的细节。3.2 连接数与线程缓冲最容易被人忽略的累计效应如果说buffer pool是“一块大石头”那么连接相关的会话内存就是“很多颗小沙子”。单看某个连接不算大但架不住连接数量多。每个会话至少会占用thread_stack默认256KB有些系统是512KBsort_buffer_size默认256KB但很多人按教程改成2M-16Mjoin_buffer_size默认256KBread_buffer_size默认128KB到1Mnet_buffer_length默认16KB算一笔账如果300个连接每个连接在极端情况下占用3M-5M光会话缓冲就奔着1G以上去了。有些高配服务器把max_connections设到2000即使平时只有500个连接在用出现短时并发高峰时内存瞬间就可能被吃出几个GB。我自己的经验是普通业务系统根本用不着1000以上的连接上限配合连接池使用500以内已经非常宽裕。相应的排查优化手段有用SHOW PROCESSLIST;看连接状态杀掉长期Sleep的僵尸连接应用侧改用连接池控制活跃连接数避免每来一个请求就新建一个MySQL连接调小sort_buffer_size、join_buffer_size等会话级参数避免“大篮子装小东西”设置合理的wait_timeout和interactive_timeout让空闲连接尽快释放这里特别提一下连接池的最大连接数一定要和MySQL的max_connections配套设置。我见过一个应用把连接池最大连接数设成400而MySQLmax_connections只有300结果应用层排队几个小时后MySQL连接数打满新连接全部报错性能瞬间雪崩。3.3 排序与临时表操作级的内存爆发点sort_buffer_size和tmp_table_size这两个参数很有意思它们的设计初衷是“允许单条SQL使用比较大的临时内存”但在并发场景下它们会非常可怕。比如你把sort_buffer_size调到16M理论上一台16G内存的服务器能同时支撑几十条排序查询但如果业务里恰好有一批慢SQL20个连接同时排序那就是320M再乘以其他缓冲内存可想而知。最典型的临时表场景是GROUP BY、ORDER BY、DISTINCT和某些子查询。MySQL会在内存里建立内部临时表如果超出tmp_table_size或max_heap_table_size的限制就会落盘到磁盘临时表。落盘本身是性能灾难但内存侧也不是没有代价只有在内存中完全放不下的那一瞬间它才把部分数据写到磁盘之前内存已经经历过一次满载。排查时除了看之前提到的Created_tmp_disk_tables比值我还习惯跑EXPLAIN看SQL执行计划Extra列如果出现Using filesort或者Using temporary就意味着这条SQL一定会消耗排序或临时表内存。EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;如果看到Using temporary; Using filesort说明这条SQL既建了临时表又要排序。这时候别盲目调大tmp_table_size正确的做法是去优化SQL加上合适的索引让排序走索引有序扫描减少排序拆分大分组查询避免SELECT *带大字段进临时表。内存调参只是缓解SQL优化才是根治。3.4 表缓存与元数据那些“看不见”的内存消耗除了上面几个明显大户还有一些零散内存消耗容易被漏掉。比如table_open_cache它缓存已打开表的文件描述符和表结构信息表数量多、分区多的情况下这个缓存也会吃几百MB。还有Performance Schema自身它如果配置了过大的performance_schema_max_*相关参数本身就会占用不小的内存很多人在排查内存时忘了看它但它恰恰是“不明内存增长”的常见来源。另外MySQL 5.7的binlog_cache_size会在每个客户端事务写入二进制日志时占用一块缓存事务大、并发高时同样不可小觑。MyISAM的key_buffer_size虽然在新架构里用得少但老系统里若还有MyISAM表这部分也照样占内存。对这类杂项内存排查策略很简单先用2.2小节的memory_global_by_current_bytes把全部内存来源拉出来按大小排序凡是看不懂的模块再去SHOW VARIABLES里找对应参数。只要你能把内存去向解释到90%以上这个排查就算合格了。4. 一个完整排查案例从现象到参数落地4.1 案例背景与初步定位我前阵子处理的一个实际案例服务器配置是8核16G操作系统CentOS 7MySQL 5.7.44。现象是free -m显示可用内存长期不足1Gswap已经被用了3G多业务高峰时应用层大量报“连接超时”。初步排查用的还是三板斧。top里面mysqld的RES稳定在14.2G左右ps aux --sort-%mem确认没有其他大内存进程。这就基本锁定问题出在MySQL实例本身。连进MySQL后SHOW VARIABLES看到的关键参数是这样的innodb_buffer_pool_size 12G max_connections 800 thread_stack 512K sort_buffer_size 2M join_buffer_size 2M tmp_table_size 64M max_heap_table_size 64M再看SHOW GLOBAL STATUSThreads_connected在高峰时冲到350Innodb_buffer_pool_hit_rate是99.8%Created_tmp_disk_tables的比例只有3%左右。这个数据很有意思buffer pool命中率极高说明12G里有很大一部分其实用不上但连接数350是实打实的会话内存的累计就会被放大。4.2 参数调整的计算逻辑先算一笔账12G的buffer pool加上350个连接。每个连接按thread_stack512K sort_buffer_size2M join_buffer_size2M read_buffer_size1M SQL执行环境的杂项1M来算富余一点就是6M左右。350个连接在很多时刻并非全部处于“满载排序”状态但MySQL的缓冲参数是“按需分配、预分配上限”的一个连接只要执行过较大查询这些buffer就会被真实分配出来且不会很快释放。实测在高峰时仅会话相关内存就吃掉了2G以上。操作系统再占用1.5G到2G的Page Cache和其他系统服务内存早就见底了。调整方案分三路。第一buffer pool从12G降到8G。依据是命中率99.8%说明8G仍能覆盖绝大多数热数据剩下的4G相当于为系统缓存留出余量。16G物理机配8G buffer pool比例只有50%确实偏保守但对这台已经频繁swap的机器来说先保证不swap才是重点等业务迁移或加内存后再逐步调整也不迟。第二压缩连接数。max_connections从800降到300wait_timeout从8小时降到300秒。应用侧同步启用连接池把最大活跃连接数控制在100以内。这里顺便说一句有些团队对max_connections有执念觉得“设大点总不会错”但在内存有限的环境里连接上限本身就是一种资源保护设得越大危险越大。第三收窄会话缓冲。sort_buffer_size从2M降到1Mjoin_buffer_size从2M降到1Mread_buffer_size保持1Mtmp_table_size从64M降到32M。这些参数调小后单连接的最大内存占用直接折半而绝大多数OLTP短查询根本用不到2M的排序缓冲业务影响很小。配置文件里最终改成了这样[mysqld] innodb_buffer_pool_size 8G max_connections 300 thread_stack 256K sort_buffer_size 1M join_buffer_size 1M read_buffer_size 1M tmp_table_size 32M max_heap_table_size 32M wait_timeout 300 interactive_timeout 3004.3 调优后验证效果与权衡重启MySQL后观察一周free -m显示可用内存稳定在4G以上swap使用量归零高峰时Threads_connected最高150Innodb_buffer_pool_hit_rate还是维持在99.6%左右。业务侧超时报错消失慢查询数量没有明显增加。这说明8G的buffer pool完全够用。这里要特别说一句调优不是单纯地把每个参数都改小而是找到一个让业务、MySQL、操作系统三方都舒服的平衡点。案例里sort_buffer_size从2M降到1M之所以没有影响业务是因为业务的排序SQL要么走了索引、要么本身结果集很小如果你的业务里确实有大量几百MB的排序需求那要解决的就不是内存参数而是SQL本身。还有一个值得记住的教训改完参数后不要急着退出先用SHOW VARIABLES验证配置真的生效再观察SHOW GLOBAL STATUS里几个关键指标。有些参数在MySQL 5.7里只能写到配置文件重启生效用SET GLOBAL动态修改只能临时生效重启后又会回到配置文件的值。动态参数适合应急止损长期修复一定要落配置文件。5. 常见问题速查与长期监控建议5.1 问题排查速查表把这些年遇到的内存类常见问题整理成一张速查表排查时可以直接对号入座现象可能原因优先排查项快速缓解手段内存缓慢增长重启后恢复连接未释放、缓冲累积Threads_connected、Processlist中Sleep连接调小wait_timeout杀僵尸连接内存突然飙升大排序、大join、临时表Created_tmp_disk_tables、慢查询日志优化SQL、补充索引内存长期95%以上Buffer pool设置过大命中率、数据实际规模调小innodb_buffer_pool_size峰值时OOM杀进程内存规划失衡free -m、多实例隔离预留系统内存合理配置swap改了参数不生效修改方式错误SHOW VARIABLES写入配置文件并重启有内存但总在swapPage Cache被挤压vmstat的si/so调小buffer pool给OS留空间5.2 长期监控别等内存满了才去处理排查是一次性的事但MySQL的内存管理是持续的事。我强烈建议给生产库配一份最低限度的监控至少盯这几个指标物理内存使用率、swap使用量、Threads_connected、Innodb_buffer_pool_hit_rate、Created_tmp_disk_tables与Created_tmp_tables的比值。监控阈值方面物理内存使用率达到80%就应该有黄色告警到达90%则需要立即处理Threads_connected如果长期超过max_connections的70%就要考虑是连接泄漏还是并发真实增长buffer pool命中率低于95%优先看SQL是否有大量全表扫描而不是急着加内存。另外还要提一个很多教程不会讲的小点Linux的OOM Killer不区分“MySQL重要还是别人重要”内存耗尽时它只挑最占内存的进程杀掉。如果MySQL是最爱内存的那个那OOM时第一个被杀的就是它。所以给系统预留内存、合理配置swap不只是为了性能更是为了保命。我个人实际操作中的体会是MySQL内存排查这件事90%的情况最后都落在三句话上——buffer pool设得有没有根据连接数是不是失控SQL是不是在内存里做太多事。把这三句话反复验证清楚内存问题基本都能解决。最后再分享一个小技巧每次调参后把当时的配置、状态变量、业务流量情况记在一张表里几次之后你就能建立属于自己业务的“内存基线”以后再出问题一眼就能看出偏离了多少。