MySQL这种问题干运维的兄弟十有八九都遇到过。进程一启动内存蹭蹭往上涨看着free命令里那点剩余内存心都凉了。更气人的是网上搜出来的答案要么是复制粘贴的官方文档要么就是“重启大法”看完了也不知道下一步该干嘛。今天我就把这个老问题掰开揉碎讲清楚。这活儿我前后排查过几百次从MySQL 5.7到8.0都踩过坑这篇文章把思路、命令、参数调整、实战案例一次说完。文章不是教你怎么按书本来而是告诉你我遇到问题时第一眼要看什么、第二步查什么、最后改什么。1. 先搞清楚MySQL的内存到底花在了哪排查任何问题之前先得知道“正常是什么样”。MySQL的内存使用模型和Java应用那种一上来就把内存吃满的套路完全不同它是一个典型的“缓存优先”架构。理解了它为什么吃内存才知道该从哪里下手。1.1 全局缓冲区和会话缓冲区两类开销要分清MySQL的内存消耗主要分两大块。一块是全局缓冲区这是服务启动时就分配好的、大家共用的大块内存。比如那个最出名的innodb_buffer_pool_size它决定了InnoDB能把多少热数据放进内存里默认情况下光是它就能占到服务器总内存的60%到70%。这一块属于“预分配”进程一启动就占住了操作系统看到的内存占用数据里绝大部分都是它。另一块是会话缓冲区每个客户端连接都有自己的独立内存空间。比如sort_buffer_size是给排序用的join_buffer_size是给连接查询用的read_buffer_size和read_rnd_buffer_size是给顺序和随机读用的。这些参数看单个值都不大默认可能就256KB到1MB但问题在于MySQL的分配机制是“按需最大值”一个连接只需要8KB也可能给你按256KB分配连接多了以后累加效应非常可怕。我见过一台16G内存的机器被300多个会话连接撑爆会话缓冲区的总占用能超过6G这个数字绝对不夸张。1.2 为什么“看任务管理器”思路在MySQL这里不灵很多新手喜欢边看操作系统的内存占用边猛敲SHOW VARIABLES试图找出是哪个参数在“偷内存”。这个方向就错了。MySQL有不少内存是运行过程中动态产生的根本不在配置文件里体现。典型的例子是performance_schema从MySQL 5.7开始默认开启它本身就要吃上百MB内存你随便哪个DATABASE的连接会话一多它消耗的内存还会跟着涨。更让人头疼的是MySQL不会主动把用过的内存归还给操作系统。这是它和Tomcat这种应用服务器最大的区别。应用服务器用完了内存可以GC回收还给OSMySQL的很多内存块是复用型的比如InnoDB缓冲池里的LRU链表它只是内部挪来挪去就算你删了一堆数据内存占用也未必降下来。所以看到内存报警第一反应不是去杀进程重启而是先判断这些内存到底是被谁占着是不是真的有压力。2. 三步定位内存大户全局配置、会话连接、动态视图排查MySQL内存问题我有一套固定的三板斧。不管是什么版本的MySQL这套流程基本都能把你带到正确的方向上去。2.1 第一步看全局配置算一遍理论内存上限先别急着连数据库直接在服务器上把配置文件的真实生效值拉出来。这里有个坑MySQL的参数生效来源有三个层级配置文件、启动命令行、还有SET GLOBAL动态修改的。你以为你改的my.cnf生效了实际上运行中的实例可能还是老参数。mysql -uroot -p -e SHOW VARIABLES LIKE %buffer%; SHOW VARIABLES LIKE %cache%; SHOW VARIABLES LIKE %size%;拿到这些参数后自己动笔算一笔账。粗略公式是理论最大内存 innodb_buffer_pool_size key_buffer_size max_connections * (sort_buffer_size join_buffer_size read_buffer_size read_rnd_buffer_size binlog_cache_size net_buffer_size) performance_schema内存开销比如一个常见的配置8G内存机器max_connections500sort_buffer_size2Mjoin_buffer_size2Mread_buffer_size1Mread_rnd_buffer_size1M光会话缓冲区的理论上限就是500乘以6M整整3G。再加上4G到5G的innodb_buffer_pool还没算其他杂项就已经奔着8G以上去了。这种配置不出事才怪。提示max_connections参数是内存计算的“放大器”。很多公司机器配置不差就是连接数设得太高每个连接哪怕什么活都不干光挂在那也要吃内存。后面章节我会专门讲怎么对付它。2.2 第二步查会话连接看看谁在薅内存全局配置算的是“最坏情况”是不是真的达到了这个最坏情况得看当前实际的连接状态。内存被吃到报警通常都是连接数暴涨导致的。这个时候我会立刻执行一条SQL看看都有哪些用户、哪些来源IP占着连接SELECT user, host, db, command, time, state FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC;注意这里用了个小技巧command ! Sleep先把那种挂了几小时没干活的空闲连接过滤掉只看真正在做事的线程。Sleep连接不是不能吃内存而是它们吃的只是连接建立时分配的基础内存真正危险的是处于Query、Sorting result、Sending data状态的连接它们会额外触发排序、临时表、结果集等更耗内存的操作。如果发现几百个连接同时处于Sending data状态八成是某条慢查询在扫描大表。这时候就别只盯着内存了得回到SQL本身去优化杀掉几条关键连接内存压力会立刻缓解。2.3 第三步用performance_schema搞清内存去向MySQL 5.7以上版本自带的performance_schema库里有内存统计表这是排查内存问题最锋利的工具没有之一。它能告诉你每个模块、每个线程到底消耗了多少内存比你在操作系统层面瞎猜高效无数倍。SELECT event_name, SUM(CURRENT_NUMBER_OF_BYTES_USED) AS total_used FROM performance_schema.memory_summary_global_by_event_name WHERE CURRENT_NUMBER_OF_BYTES_USED 0 GROUP BY event_name ORDER BY total_used DESC LIMIT 10;这张表我每次排查内存问题必查。它列出的前几项正常情况下就是memory/innodb/buffer_pool、memory/sql/THD、memory/sql/Query_cache这几类。如果发现一个叫memory/sql/User_alloc或者memory/mysys/IO_CACHE的值特别大往往意味着有大排序或者大临时表在运行顺着event name里的线索就能定位到具体代码路径。提示performance_schema自己也要吃内存。如果机器内存本身就紧巴巴的排查完了记得把用不到的那些events_waits_*、events_stages_*采集项关掉能省下一笔可观的开销。3. 那群最吃内存的配置项一个一个调过来定位到问题方向后终究要落到参数调整上。下面这几个参数是我这几年来调整频率最高、对内存影响最直接的每个都单独拿出来说说。3.1 innodb_buffer_pool_size全局内存的大头这个参数是InnoDB的缓存池里面放着脏页、索引页、数据页还有插入缓冲。可以说MySQL的热点数据全在它肚子里。Buffer pool设小了数据库会频繁刷盘性能暴跌设大了内存又不够用。我的经验公式是纯粹跑MySQL的专用机器设为物理内存的60%到70%。如果你在服务器上还跑着别的应用那就降到40%到50%。问题是很多人改了my.cnf里的这个值后重启完发现内存占用居然没降多少。原因在MySQL 5.7.5之后的版本innodb_buffer_pool_size支持动态调整你可以直接在线改不用重启SET GLOBAL innodb_buffer_pool_size 6442450944;改完后用这条SQL确认修改生效SELECT innodb_buffer_pool_size;需要注意的是这个参数的小数题很容易让人翻车它只认字节数不认M、G这种单位后缀。不过在MySQL 8.0里写innodb_buffer_pool_size 6G是可以的5.7的老版本还是乖乖写字节吧。3.2 sort_buffer_size、join_buffer_size这类“会话炸弹”这几个参数是我在排查内存问题时重点审查的对象。它们的坑在于配置的存在本身不占内存一旦连接触发排序、连接查询MySQL就会按你配置的最大值去分配内存。也就是说500个连接不是同时每人分2M而是只要有20个并发排序操作每人就可能掏走2M再加点临时表什么的内存瞬间就吃紧了。所以我的处理原则很简单不要追求大值来提高性能默认值能应付绝大多数场景如果确实要调sort_buffer_size不要超过2Mjoin_buffer_size不要超过1M调整时必须同步压降max_connections用SQL临时把会话级参数调小比自己改配置文件更安全毕竟你可以在线验证效果SET GLOBAL sort_buffer_size 1048576; SET GLOBAL join_buffer_size 1048576; SET GLOBAL read_buffer_size 524288;调完以后观察几天如果业务没影响再把同样的值写进配置文件永久生效。3.3 容易被忽略的元数据缓存和performance_schema开销除了上面这些table_open_cache、thread_cache_size、binlog_cache_size也都是内存小怪兽。table_open_cache每多一个配置值就多一个文件描述符和表结构缓存在线DBA折腾多了以后最容易把这个值调得特别大。performance_schema的内存占用在5.7默认开启后动辄就是100多M到300多M如果机器内存真的很紧张可以关闭掉不常用的事件采集。一条比较实用的SQL可以把这些容易被忽略的配置一次性拉出来看看SHOW VARIABLES WHERE Variable_name IN ( table_open_cache, thread_cache_size, binlog_cache_size, performance_schema, max_heap_table_size, tmp_table_size );比如tmp_table_size和max_heap_table_size这两个控制的是内部临时表能用到多少内存。如果查询里经常有GROUP BY、DISTINCT、ORDER BY临时表内存不够就会被写入磁盘临时文件表面上看起来是磁盘I/O飙高但实际上内存也没少占因为MySQL判断“要不要转磁盘临时表”时就是拿这两个值比大小的。3.4 连接数max_connections必须学会“限流”很多公司一遇到“连接数超限”的报错第一反应是把max_connections调大从默认的151调到1000、2000。这个操作我强烈不建议无脑做除非你的机器内存是用不完的。每个连接都要维护THD结构、网络缓冲区、认证信息就算连接闲着也要占大概1M到2M内存。1000个连接就是至少1G到2G内存。真正的解法是要看业务是否需要这么多连接。如果是应用服务端连接池导致的正确做法是去调应用侧的连接池上限而不是在数据库这边硬扛。如果确实需要提高数据库连接上限我建议按照“1个连接预留2M内存”这个标准来反推配合innodb_buffer_pool_size一起测算保证总内存占用不超过物理内存的80%。4. 一次线上内存告警的完整复盘从发现到修复理论说了一堆实战才见真章。下面这个案例是我帮一个朋友的电商项目排查的现象非常典型发出来给大家做个参考模板。4.1 现场信息收集那是一个积分商城系统MySQL 8.0.32版本跑在16G内存的云主机上配置大概是innodb_buffer_pool_size12Gmax_connections500sort_buffer_size4M。运维监控显示内存使用率在晚高峰直接冲到97%服务器开始疯狂使用swapCPU负载也飙到8以上。我接到消息的第一件事不是上去改参数而是先把现场数据收集起来。依次执行了下面几条命令free -h cat /proc/meminfo | grep -E MemTotal|MemFree|SwapTotal|SwapFree mysql -uroot -p -e SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Max_used_connections;free -h的结果显示Mem: 16G总共used 15.5GSwap用掉了1.8G。Threads_connected直接蹦到486Max_used_connections接近500。第一印象已经很清晰了连接数打到上限内存被会话缓冲区吃掉大半。4.2 定位与修复过程接着我用前面提到的performance_schema.memory_summary_global_by_event_name跑了一遍结果让我很意外内存占用排名第一的居然是memory/sql/THD占了2.8G第二才是memory/innodb/buffer_pool占了8G多。这说明什么说明会话线程结构本身就吃掉了巨量内存连接数才是真正的元凶。我再往深挖了一步查了processlist发现同一时刻有400多个连接处于Sleep状态。这些连接都是应用侧的连接池建立的用了以后既不释放、也不干活纯挂机。每个Sleep连接虽然单个只占几百K到1M但架不住量多。修复动作分了三步走第一步直接改会话级别的排序缓存把sork_buffer_size降到2Mjoin_buffer_size降到1M。因为是线上环境我先用SET GLOBAL动态调整验证效果不直接改配置文件避免重复重启。第二步把max_connections从500降到200同时通知应用开发方修改连接池的maximumPoolSizeHikariCP里就设成50就够了不需要500。第三步晚上低峰期把innodb_buffer_pool_size从12G降到10G给系统和会话缓冲区留出余量。4.3 参数调整后的验证调整完过了30分钟再去看free -h里的used降到了13.2G一天后稳定在11.8G左右。swap也开始慢慢回收CPU负载回落到1以下。应用端没有出现连接被拒的报错接口响应时间反而因为减少了内存换页而更快了。这次排查帮我确认了一个很重要的点很多MySQL内存问题根因就是连接数失控不是buffer pool不够更不是缓存设置不合理。优先排查连接数往往五分钟就能找到七成问题的答案。5. 日常运维的预防手段和排障速查表问题解决之后一定要把经验沉淀成流程不然下次换个场景换个队友还得从零开始。5.1 建立内存水位监控和趋势告警我建议在监控系统里除了对CPU、磁盘、网络做基础监控外至少要把这几个指标单拎出来画趋势图MySQL进程内存占用以RSS为准Threads_connected当前连接数Max_used_connections历史峰值连接数Buffer pool命中率内部临时表创建数量注意判断“内存是否出问题”不能只看某个瞬间的快照要看趋势。比如Threads_connected从早上的50慢慢爬到晚上的480这种曲线就是在提醒你连接池明显有泄漏或者回收策略有问题。推荐把下面这条SQL放到定时任务里每5分钟跑一次输出到监控系统SELECT variable_name, variable_value FROM performance_schema.global_status WHERE variable_name IN (Threads_connected,Threads_running,Uptime);5.2 常见误区和避坑经验排查MySQL内存问题有几个坑我踩过不止一次专门列出来第一看到内存占用高就直接调小innodb_buffer_pool_size。这是新手最容易犯的错误。Buffer pool是MySQL性能的命脉调小它会导致大量磁盘读换来的是性能断崖式下跌。在排除连接数和排序缓冲区问题之前绝不先动buffer pool。第二忽略swap空间的使用情况。MySQL进程一旦被换到swap里性能会立刻降到你怀疑人生。排查时一定要看si和so两个交换指标如果持续有换入换出说明物理内存已经不够了得赶紧处理。第三忘记了performance_schema这个隐藏的内存大头。用Docker之类的方式部署MySQL时宿主机内存看着不大performance_schema一开就占了几百M再加上一些开发者自己加的监控表内存占比直接爆炸。如果确实不需要数据库级别的性能监控可以考虑在配置里显式关闭掉不需要的采集项。5.3 一套可直接抄的“参数基线和巡检模板”最后分享一套我长期使用的基线参考它适合8G到16G内存的专用MySQL机器参数名推荐值说明innodb_buffer_pool_size内存的50%-65%专用机器取高值混合部署取低值max_connections150-300结合应用连接池上限来定sort_buffer_size1M-2M大排序靠优化SQL不靠加大内存join_buffer_size1M超过这个值要考虑索引优化read_buffer_size512K-1M顺序扫描场景才值得调大tmp_table_size16M-32M避免临时表落盘即可max_heap_table_size同tmp_table_size必须大于tmp_table_sizeperformance_schemaON排障必需但要控制采集项每次变更完参数我习惯顺手执行一条SHOW GLOBAL STATUS LIKE Aborted_connects如果这个值变高说明连接数压得过低了需要回退。注意改参数最忌讳一次改一大堆宁可一次只动一个跑几天看趋势再动下一个这样才能在出现问题时准确定位到是哪个变更引起的。我在实际排查中还有个习惯就是每次遇到内存问题处理完都会把当时的配置备份、监控截图、处理步骤存到一个专门的文档里。下次再碰到直接查旧文档就能找到方向比硬想快多了。这套方法用顺手之后MySQL内存报警基本就是15分钟到半小时就能定位到根因剩下的就是按部就班地做参数调整和验证了。