1. 排查前的准备先搞清楚大是多大是不是真的异常接到MySQL占用内存过大这个反馈第一反应别急着调参数。先回答两个问题内存到底占到多少这个量级和你的配置、业务负载匹配不匹配我用过最有用的起手式是看两个数操作系统层面的进程内存常驻集和MySQL内部的缓冲池统计。拿一台典型的云服务器举例假设总内存16GMySQL配置了innodb_buffer_pool_size8G其他连接、临时表、排序、性能监控能吃掉2-3G那MySQL的RSS驻留内存稳定在10-11G左右是正常的。如果RSS飙到14G甚至15G这才算真正需要排查的大。怎么快速观察两条命令# 看MySQL进程占了多少物理内存 top -p $(pidof mysqld) # 或者更精确按字节显示 ls -l /proc/$(pidof mysqld)/status | grep VmRSS grep VmRSS /proc/$(pidof mysqld)/status通过/proc/pid/status里的VmRSS能看到真实的物理内存驻留比top里的RES更直观单位是kB。注意别只看free -m的used列。文件系统缓存会让used非常高但那是可回收的不一定代表问题。要看真正的压力得盯free -m里的available同时结合vmstat 1观察持续的内存压力以及是否触发swap。2. 第一轮快排用现成命令定位内存大户排查内存问题我习惯分三个层次走先看连接和会话再看缓冲区配置最后看性能监控本身。第一轮不指望立刻找出根因而是把最可能出问题的几个点迅速过一遍。2.1 连接数和会话状态先摸清楚底子连接数超标是内存上涨的最常见推手。每个MySQL连接都要分配线程栈、网络缓冲、排序缓冲等虽然很多是运行时才真正分配但连接量一大内存自然水涨船高。-- 当前活跃连接数 SHOW STATUS LIKE Threads_connected; -- 连接配置上限 SHOW VARIABLES LIKE max_connections; -- 看看连接来源分布是不是有某个应用疯狂建连 SELECT HOST, COUNT(*) FROM information_schema.PROCESSLIST GROUP BY HOST;如果Threads_connected长期贴近max_connections那问题基本可以先锁定在连接管理上。连接为什么会这么多常见的有应用连接池设置太大、慢查询把连接占住不放、或者有脚本在频繁重连。另外看一下processlist里有没有大量长时间不释放的会话-- 按时间和状态排序找出长时间sleep或executing的会话 SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO, 60) FROM information_schema.PROCESSLIST ORDER BY TIME DESC LIMIT 30;有些应用框架默认开了连接池但是空闲不回收这类会话会一直sleep在那里每个都占着内存。我遇到过一次比较典型的某业务侧的中间件配置了最小连接数50结果它没有实际流量时也不释放白占了50个连接对应的线程栈和缓存。2.2 缓冲池和全局缓冲区逐个对表MySQL里内存消耗的大头通常是InnoDB缓冲池其次是各种global buffer。先看几个关键值SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_buffer_pool_instances; SHOW VARIABLES LIKE innodb_additional_mem_pool_size; SHOW VARIABLES LIKE innodb_log_buffer_size; SHOW VARIABLES LIKE key_buffer_size; SHOW VARIABLES LIKE performance_schema; SHOW VARIABLES LIKE performance_schema_max_table_instances;其中innodb_buffer_pool_size是重中之重。很多默认配置或者云厂商的默认模板给的buffer pool都偏大。比如一台4G内存的机器默认配置可能把buffer pool设成2G甚至更多再加上其他开销内存直接告急。performance_schema也要特别留意。MySQL 5.7开始默认开启而它的内存开销是独立于InnoDB的。在高并发、大表的情况下performance_schema能吃掉几百MB到1G以上。怎么确认它到底吃了多少SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE %memory/performance_schema% ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 10;这条查询能直接看到performance_schema各项内存消耗的实时统计。提示如果业务没有强依赖performance_schema且内存紧张可以将其关闭。但要注意关闭后一些诊断信息会缺失比如后面要用的sys.statement_analysis、等待事件统计等都会失效。关闭前先确认有没有监控系统在依赖它。2.3 临时表和排序区往往被低估临时表是个容易被忽略的内存黑洞。内部临时表超过一定阈值会落盘但在落盘之前先会按tmp_table_size和max_heap_table_size的较小值去分配内存。大量包含GROUP BY、ORDER BY、DISTINCT、子查询、union的SQL如果临时表在内存中频繁建立和释放内存抖动会非常明显。-- 当前会话临时表状态 SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; SHOW GLOBAL STATUS LIKE Created_tmp_tables; -- 这两个值的比值能看出临时表落盘的比例 -- 如果disk占比高说明临时表经常超阈值内存里放不下另外sort_buffer_size、join_buffer_size这类会话级缓冲区虽然是按需分配的但一旦分配就是按配置值给足。有些人图省事把sort_buffer_size调到2M、4M甚至更高并发一多内存就炸。为什么因为每个连接做排序时都会单独申请一份。我自己的经验是sort_buffer_size保持默认的256K-512K就够除非有明确的业务瓶颈证明排序是短板。join_buffer_size建议控制在1M以内这两项对内存的放大效应远比想象中严重。2.4 预读和碎片隐藏的内存消耗还有一个让人容易踩坑的是InnoDB预读prefetch。当MySQL认为你即将需要大量连续数据时会提前把页面读入缓冲池。如果业务逻辑是典型的大查询、全表扫描、定向批处理预读会迅速把buffer pool填满导致大量热数据被挤出内存用得高但命中率反而低。-- 查看预读相关状态 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_ahead; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_ahead_evicted;如果read_ahead_evicted数值很大说明预读进来的页面还没来得及用就被换出了这时候需要考虑是否关闭或降低预读innodb_random_read_ahead和innodb_read_ahead_threshold。3. 第二层深挖用工具和SQL拆解内存去向第一轮快筛完了手里有了大概方向。第二轮要做的是更精确的定位——找到具体是谁在耗内存。直接看进程内存总量只能判断是不是异常要回答哪部分异常还得往下拆。3.1 用sys库快速找到高开销语句MySQL 5.7自带的sys库是排查利器。简单几行SQL就能看到当前最耗资源的语句-- 执行次数多、总耗时高的语句 SELECT * FROM sys.statement_analysis ORDER BY total_exec_time DESC LIMIT 20; -- 正在执行的、等待时间最长的会话 SELECT * FROM sys.session WHERE command ! Sleep ORDER BY conn_id DESC;statement_analysis统计的是从performance_schema聚合出来的执行情况能看到总执行次数、总耗时、平均耗时、扫描行数等。特别关注那些总耗时和锁定时间长同时出现、扫描行数远大于返回行数的语句这类语句大概率触发大量临时表或者大范围扫描间接推高内存压力。我自己遇到过一个典型案例某张表每天会有一次全量同步的批处理SQL里带了个ORDER BY结果每次都生成几百万行的内存临时表。内存层面的表现就是窗口期内存直接拉满等批处理结束才回落。通过statement_analysis一眼就看到了那条语句后面改成按索引顺序处理并限制返回行数问题就解决了。3.2 逐层看performance_schema内存统计如果怀疑performance_schema本身是内存大头上一轮的查询还不够细可以进一步看-- 按 instrumentation 分类统计内存占用 SELECT * FROM sys.memory_by_thread_by_current_bytes ORDER BY current_bytes DESC LIMIT 10; -- 更直观所有线程当前累计内存 SELECT * FROM sys.memory_global_by_current_bytes ORDER BY current_bytes DESC LIMIT 20;memory_global_by_current_bytes能把内存按事件类型分门别类归总从高到低排下来哪些是线程栈、哪些是table cache、哪些是buffer pool相关的内存一目了然。这里有个经验之谈如果memory_global_by_current_bytes结果里出现大量memory/performance_schema/table_handles或者memory/performance_schema/events_statements_history_long相关的记录说明performance_schema对表句柄、语句历史事件的采集太细了。可以适当调低这两个参数SET GLOBAL performance_schema_max_table_handles 10000; SET GLOBAL performance_schema_events_statements_history_long_size 1000;当然全局动态调整在重启后会失效想彻底改的话要写到配置文件里。3.3 观察InnoDB缓冲池细节看buffer pool内部状态最直接的手段还是SHOW ENGINE INNODB STATUS在BUFFER POOL AND MEMORY这一段能找到有效信息SHOW ENGINE INNODB STATUS\G关键看几行Total memory allocated内部总分配Buffer pool size缓冲池总页数Free buffers空闲页数如果长期接近于0说明缓冲池被占满了Database pages实际存放数据页的数量Modified db pages脏页数量。如果脏页量持续很高且Pending reads/writes频繁说明后台刷盘压力大内存中的写缓冲一直存积。Buffer pool从磁盘读数据页和写脏页是内存与磁盘之间最核心的IO通道。当Free buffers长期为0而Database pages却很高但请求命中率不理想时意味着buffer pool虽然大但放了很多并没有被频繁访问的页面——这时候单纯的加内存不是最优解反而应该从SQL效率上去减少不必要的数据页加载。3.4 看看行缓存、数据字典和其他杂项除了缓冲池和连接MySQL还有一些容易忽略的内存消耗项。比如表缓存。table_open_cache这个值控制着打开表的结构缓存如果表数量非常多缓存占用会直线上升。SHOW VARIABLES LIKE table_open_cache; SHOW GLOBAL STATUS LIKE Open_tables; SHOW GLOBAL STATUS LIKE Opened_tables;当Open_tables经常到达table_open_cache上限同时Opened_tables在持续增长说明表频繁被关开缓存被反复淘汰重建这个过程既耗CPU也耗内存。另外还有metadata_locks相关的内存在大量DDL和DML混跑的数据库里尤其明显。可以通过performance_schema.metadata_locks表来观察SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT(*) FROM performance_schema.metadata_locks GROUP BY OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME ORDER BY COUNT(*) DESC;4. 减负实操从参数调整到SQL改写定位完成后就到了动手环节。这一节说的不是理论是我在真实环境里验证过、能落地的调整思路。调整分两条线一条是全局参数优化一条是SQL层面的收敛。4.1 最优先调整降低InnoDB缓冲池并加实例拆分innodb_buffer_pool_size是整个MySQL内存的第一权重项。如果发现内存压力大大部分人第一反应是调低它。调低之前有个前提要验证缓冲池命中率是不是还说得过去。-- 命中率计算 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; -- 总读请求次数 SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads; -- 从磁盘读的次数命中率 (read_requests - reads) / read_requests。如果命中率在95%以上说明池子里存的数据确实在被反复利用这时候大规模砍buffer pool代价是磁盘IO暴涨不划算。如果命中率低于90%且内存压力大那砍buffer pool是对的——反正池子里大部分页面也没人访问留那么大纯属浪费。调整示例[mysqld] innodb_buffer_pool_size 4G innodb_buffer_pool_instances 4这里有两个细节buffer pool instances拆分了之后能降低大并发下的内部锁竞争同时让内存分配更碎片化、更均匀。MySQL 5.7以上innodb_buffer_pool_instances的默认规则是在buffer pool超过1G时自动设为8。手动设4是为了更保守地控制总开销每个instance有自己独立的控制信息。buffer pool size并不是一改配置就能立刻全部释放的。在运行中如果通过SET GLOBAL动态修改InnoDB会逐渐收缩缓冲池把页面淘汰出去。这个过程不会瞬间完成需要观察几分钟到几十分钟。如果内存压力非常紧急建议直接重启配置一次生效。注意别把innodb_buffer_pool_size当成唯一变量。就算buffer pool降下来了如果连接数、临时表、performance_schema这些没有收敛总内存照样会高。4.2 调整连接相关参数杜绝放大效应连接数这块可以做三件事调低max_connections。如果业务没有大并发需求从默认的151或者云厂商的几百降到100甚至更低立刻就能腾出内存空间。调低会话级缓冲。sort_buffer_size 256K join_buffer_size 256K read_buffer_size 128K read_rnd_buffer_size 256K这套配置我实测下来对于常规OLTP业务完全没有影响但内存占用能明显降一截。因为这类会话级缓冲都不是全局预分配的是线程真的用到时才申请。但如果申请阈值太高每个并发线程都留足了内存放大效应就出来了。之前见过有人把sort_buffer_size配到16M的20个并发排序线程同时跑光这一项就吃掉320M完全没必要。排查是否有泄漏式连接。如果应用层的连接池有bug连接只增不减Threads_connected会持续上涨。这种情况光靠配置调整救不了得在应用侧把连接池上限改了。4.3 关闭或精简Performance Schema前面提到performance_schema默认开启且内存开销不小。这里给几种处理策略完全关闭performance_schema OFF适合那些不依赖任何诊断数据的场景。但关闭前务必确认监控系统比如自建的zabbix、prometheusmysqld_exporter不需要它否则关闭后监控指标会大面积失效。降低采样规模performance_schema_max_table_instances 1000 performance_schema_max_thread_instances 200 performance_schema_events_statements_history_size 100 performance_schema_events_statements_history_long_size 1000这一套能明显降低performance_schema对内存的占用同时保留核心的语句和等待事件统计适合还需要做诊断的场景。只关特定监控项。如果你发现具体是某类采集项在吃内存可以在初始化时禁用特定instrumentperformance_schema_max_cond_instances 0不过这个操作比较细一般场景用不上。我建议平时保持performance_schema开启但限制instance数量够用就行。4.4 临时表相关落盘阈值和内存阈值分开看临时表的内存优化要同时看两个参数tmp_table_size 32M max_heap_table_size 32M规则是内部临时表的内存上限取这两个值里较小的那个。如果你只调了tmp_table_size但没调max_heap_table_size而后者默认是16M那实际生效的还是16M——这是很多人调了没效果的原因。调整的目标是让合理大小的临时表留在内存里过大的直接落盘而不是无限占用内存。比如一个GROUP BY结果集有10万行每行按200字节算也就20M32M够用。但如果有500万行的临时结果集内存肯定放不下强行调大阈值只会让内存无谓地先涨一波再落盘不如让它早点落盘换稳定性。提示内存临时表用的引擎是MEMORY落盘后会转为MyISAM或临时InnoDB表取决于版本和配置。落盘虽然慢但至少不会把内存拖垮。两害相权取其轻。4.5 收敛预读防止缓冲池被无效填充预读相关的参数主要是这两个innodb_read_ahead_threshold 56 innodb_random_read_ahead OFFinnodb_read_ahead_threshold控制的是InnoDB认为即将顺序读取的最小连续页数。默认56越小越激进。对于OLTP场景建议保持默认或者调大对于有明显大查询、批处理的场景如果观察到Innodb_buffer_pool_read_ahead_evicted太高适当关闭随机预读能省不少无效的缓冲池填塞。我曾经在某个数据分析型MySQL实例上把innodb_random_read_ahead从ON改为OFF内存压力立竿见影地下降因为之前每次随机访问都会触发预读把不算热的数据页提前拉进缓冲池然后很快又被淘汰白白浪费IO和内存。注意5.7里innodb_random_read_ahead默认就是OFF但很多运维patch或者云厂商模板会打开值得检查。4.6 SQL改写真正治本的手段参数调整只是给数据库减负SQL改写才是让内存问题不反复的关键。我的经验是内存压力大最典型的SQL毛病有几个SELECT * 拉取超大表。明明只需要3个字段结果把整行几KB的数据全拉出来buffer pool里塞满了用不上的列。改成只select需要的字段减少行宽内存压力立刻下降。大范围排序。ORDER BY没有走索引导致排序缓冲区反复申请大内存。解决方式是建立覆盖查询排序字段的复合索引。深分页。比如LIMIT 1000000, 20MySQL要把前100万行全部读到内存里再丢。常见优化是使用游标或者基于上次位置偏移的翻页方式。无必要的DISTINCT。某些开发者习惯性地在查询里加DISTINCT但表结构本身是唯一的或者对结果没有影响。DISTINCT会触发临时表操作去掉后省一大块内存。我接手过一个实际项目某报表查询每天定时跑一次SQL用了多表关联GROUP BYORDER BY临时表突破了max_heap_table_size落盘磁盘临时表膨胀到几个G内存也稳定上涨。后来把查询拆成两步第一步先把关联后的大集缩小第二步在缩小的集上做分组排序内存和IO都稳住了。核心思路是让数据库处理更少的数据量而不是无限加大内存让它去硬扛。5. 常见问题与排查技巧实录这一节集中整理我实际碰到过、也见过不少同行踩过的坑写成速查表按问题现象倒推原因。5.1 问题速查表现象大概率原因首选处置方向内存持续上涨重启后很快又涨满有长事务或连接泄漏会话级缓冲反复分配不释放查processlist里长时间未释放的会话抓应用连接池配置内存window期大涨平时正常定时批处理、报表任务触发大量临时表/排序排查计划任务期间的SQL通过statement_analysis抓高耗语句MySQL启动后内存直接很高buffer_pool_size配置过大或者performance_schema实例数过多检查配置文件把buffer pool和performance_schema相关值调低频繁OOM进程被系统kill总内存已到极限且buffer pool无法收缩腾出空间先加swap应急然后立刻把buffer pool调低并重启再排查连接和SQLbuffer pool命中率很低但内存很高预读或全表扫描大量填充无效页关随机预读优化SQL减少全表扫描修改参数后内存没有按预期释放参数是动态生效但需要时间或者重启才生效用patience等必要时直接重启5.2 一个完整的排查路径示例我整理了一份偏实战的排查顺序新手可以直接照着走# 1. 看状态 free -h vmstat 1 5 top -p $(pidof mysqld) # 2. 看MySQL连接 mysql -e SHOW STATUS LIKE Threads_connected; mysql -e SHOW VARIABLES LIKE max_connections; mysql -e SELECT * FROM information_schema.PROCESSLIST ORDER BY TIME DESC LIMIT 50; # 3. 看配置 mysql -e SHOW VARIABLES LIKE innodb_buffer_pool_size; mysql -e SHOW VARIABLES LIKE performance_schema; mysql -e SHOW VARIABLES LIKE tmp_table_size; mysql -e SHOW VARIABLES LIKE max_heap_table_size; # 4. 看内存归属 mysql -e SELECT * FROM sys.memory_global_by_current_bytes ORDER BY current_bytes DESC LIMIT 20; mysql -e SHOW ENGINE INNODB STATUS\\G # 5. 抓高耗SQL mysql -e SELECT * FROM sys.statement_analysis ORDER BY total_exec_time DESC LIMIT 20; # 6. 看临时表和脏页 mysql -e SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables; mysql -e SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_pages_dirty;这套流程跑下来八成问题都能定位到某一类原因。如果这时候还没发现异常那就要考虑是不是操作系统层的内存分配问题比如jemalloc碎片、内存共享段残留或者同一台机器上还有其他进程在抢内存——别忘了MySQL进程只是总内存的一部分free -h里其他进程的RSS同样值得排查。5.3 我踩过的几个坑提前给你们避掉坑一改完参数不重启然后说没效果有些参数是动态的有些不是。比如innodb_buffer_pool_size支持动态修改但它的收缩和膨胀需要时间不可能秒级生效。而performance_schema相关的大部分参数必须在启动前配置运行期改了不重启就是白该。所以改配置时要分清哪些改完要重启哪些等一会儿就好。坑二只看buffer pool忽略JVM或其他进程有一次遇到一台机器内存报警排查MySQL半天发现buffer pool已经调到很小了但内存还是高。最后看ps aux --sort-rss | head发现同一台机器上还跑着一个Java中间件RSS占了几个G。MySQL被冤枉了一整晚。多人共用服务器的时候先看全进程内存排行永远不亏。坑三把max_connections降得过于激进有次为了省内存把max_connections从200降到80第二天业务高峰期直接报too many connections比内存超了还惨。降连接数之前先确认峰值并发会话数至少留20%余量。坑四临时表阈值调得过大tmp_table_size和max_heap_table_size从16M一路调到512M遇到一个几十万行的JOIN内存瞬间满了然后磁盘写满。阈值恰到好处的判断标准是Created_tmp_disk_tables / Created_tmp_tables的比例在1:10到1:20之间比较健康如果落盘临时表比例太高才需要调大如果本来就低别再动了。5.4 长事务对内存的拖拽效应长事务对内存的消耗经常被忽略。一个事务只要一直不提交它持有的数据页在缓冲池里就不能被替换出去。大量长事务叠加在一起即使单条SQL不复杂也会导致缓冲池的有效空间被压缩相当于变相降低了buffer pool容量。-- 找出运行时间超过1分钟的事务 SELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) 60;如果经常出现长事务优先从应用层找原因是不是事务没提交是不是一个业务操作里包裹了太多次查询处理长事务带来的内存收益有时候比调buffer pool更立竿见影。6. 经验复盘一次完整的内存问题处理小记讲一个最近的实战案例来收尾把前面的知识点串一遍。某公司的内部分析库服务器8核16GMySQL 5.7日常内存使用率长期85%以上偶尔会冲到95%触发告警。初查配置innodb_buffer_pool_size8Gperformance_schemaONmax_connections500连接数日常在80左右。单看配置总内存16G光buffer pool就8G加上performance_schema和连接数内存肯定偏紧但也不算荒谬。所以第一判断是配置偏大但不是唯一问题。接下来用sys.statement_analysis跑了一遍发现有一条定时任务的SQL是每天凌晨全量更新聚合表里面有GROUP BY加ORDER BY扫描行数过千万。再对比Created_tmp_disk_tables发现凌晨时段磁盘临时表大量增长说明这个SQL产生了超大临时表。进一步看SHOW ENGINE INNODB STATUSbuffer pool的Free buffers在白天还可以但凌晨任务启动后迅速下降脏页持续积累。基本结论清晰了日间内存压力来自buffer pool偏大performance_schema开销。凌晨峰值来自批量任务的临时表和大规模数据页加载。处理方案分两步走把innodb_buffer_pool_size从8G调到6Ginnodb_buffer_pool_instances保持8释放2G给操作系统和连接缓冲做余量。优化定时任务SQL把一次巨型GROUP BY拆成按天分片多次执行并且去掉一个不必要的ORDER BY子句让临时表体积从几百M降到几十M。改完后观察一周内存使用率稳定在70%左右深夜峰值没再突破85%磁盘IO也顺带降了。没有大改配置只是把真正浪费内存的部分切掉了。7. 最后说几句实在话MySQL内存排查这事难的不是单个参数而是全局判断。它不像CPU问题那样能一步定位到某个SQL内存是配置、连接、查询、系统环境多方面凑出来的结果。我自己的习惯是先确认总内存承载多少业务量再看buffer pool和连接数这两个大户最后用sys库和INNODB STATUS把SQL级别的内存消耗揪出来。如果给新手一个最值得记住的忠告那就是——配置参数一定要够用就好不要为了性能预先把所有缓冲都开得巨大。MySQL的内存设计很聪明按需分配才是它的正常姿势强行预分配只会让你在内存告警和OOM之间反复横跳。另外排完内存问题不要就完了一定要把当时的配置快照、SQL诊断结果、处理前后的内存曲线记录下来。下次再出类似问题翻旧记录能帮你节省大量时间。我就是这么一路记过来的踩过的坑写下来复现问题的时候照方抓药快很多。