数据库性能调优这件事聊十次有九次会绕到PostgreSQL的那两个经典参数shared_buffers和work_mem。不管你是刚接手一个线上系统还处于四处救火的阶段还是已经在生产环境里优化过不少SQL这两个参数都是躲不开的。很多人一听到“缓存不够就加内存”“排序慢就调大work_mem”结果改完以后慢查询没解决数据库却先罢工了。我写这篇东西就是想把自己实际操作中积累的一些经验复盘一遍这两个参数各自管什么、为什么不能盲目调大、怎么用EXPLAIN ANALYZE验证、以及配置完以后怎么避免翻车。1. shared_buffers先理解它缓存的是“什么级别”的数据1.1 它解决的到底是什么问题shared_buffers是PostgreSQL启动时预先申请的一块共享内存用于存放数据页的副本。数据页是什么可以理解成一张表在底层文件系统里的最小读单元通常大小是8KB。数据库里所有表、索引的数据最终都以页的形式落在磁盘上而shared_buffers就是把其中一部分页常驻在内存里让读写请求不用每次穿透到磁盘。很多人以为它是整个数据库最大的“缓存”甚至觉得数据总量都能放进去才算好。实际上不是这样。PostgreSQL的设计里操作系统自己的页缓存也会缓存最近读过的文件内容。数据库进程从磁盘读一个页面时常常先命中OS缓存并不会直接摸到机械硬盘或SSD。所以shared_buffers和OS缓存是一种叠加关系并不是说数据库缓存越大就一定越好。我刚入门的时候也做过这样的蠢事在一台32GB内存的机器上把shared_buffers调到24GB重启之后整个数据库实例花了将近一分钟才起来而且检查点一到磁盘IO就像被人拿斧头劈了一样。后来才慢慢理解这个参数不是越大越好而是要找到一个合适的平衡点。1.2 为什么“越大越好”是个陷阱shared_buffers过大带来的问题主要有三个我逐个说清楚。第一是启动初始化变慢。这块共享内存在实例启动时就要全部分配并做初始化物理内存越大启动耗时越长。在几十GB内存的机器上如果设到总内存的一半以上启动时间从几秒拉长到几十秒很正常。对需要快速重启恢复的环境来说这是实实在在的隐患。第二是检查点checkpoint的IO风暴。数据库不是每改一页就立刻写回磁盘而是攒一批脏页在检查点统一刷盘。shared_buffers越大意味着脏页可能攒得越多一次检查点需要写回的数据量就越大。默认的checkpoint_timeout是5分钟如果缓冲太大每次检查点阶段的写IO尖峰非常明显直接影响同一块盘上其他业务的响应时间。第三是Buffer管理本身的CPU开销。PostgreSQL用的是类似时钟扫描的淘汰算法shared_buffers越大扫描候选淘汰页的代价越高。内存设得特别夸张的时候你会发现CPU可能先被这种内部管理耗掉性能反而下降。所以业界比较常见的经验是把shared_buffers设在物理内存的15%到25%之间起步可以先从15%试再根据命中率和IO情况慢慢往上调整。注意这里的前提是机器基本上在做数据库专用如果还有其他服务占内存得先把那些内存预算扣掉。1.3 一个通用的初始估算方法分享一个我比较常用的估算方法。假设你有一台64GB内存的服务器只看PostgreSQL数据库。第一步把操作系统和其他进程占用的内存预留出来大概留8GB到12GB第二步把剩余内存乘以20%比如56GB乘以20%约等于11.2GB凑整到12GB第三步先用12GB作为初始值跑一段观察一段时间的命中率和系统负载再决定继续上调还是下调。如果不想手动计算也可以直接写成shared_buffers 12GBPostgreSQL会自动换成对应的8KB页数。注意在部分云数据库或托管实例上可能只允许通过控制台修改这类环境里参数默认值往往已经经过平台方调过不建议再盲目动。还有一个容易忽视的点shared_buffers改了以后不能reload必须重启整个数据库实例。生产环境需要安排维护窗口。所以说调这个参数之前最好先通过监控确认真的是数据页面命中率低导致的性能问题而不是因为某条SQL本身就写得有问题。2. work_mem查询内部“临时工作台”的内存预算2.1 一次排序或哈希连接到底占多少内存如果说shared_buffers是数据库的“一级缓存仓库”那work_mem更像是每个查询自己手里的一张小工作台。排序要在这张工作台上把数据先排整齐哈希连接要在这张工作台上建哈希表聚合、窗口函数、CTE物化也会用到它。工作台有多大直接决定这个操作是在内存里平滑完成还是把数据倒到磁盘上慢慢折腾。举个例子一条SQL里如果有一个ORDER BY大字段排序在执行计划里对应的就是Sort节点。Sort节点在执行时会先尝试在work_mem允许的内存里完成排序如果内存够用排序方法显示为quicksort很快如果数据量超过work_mem方法会变成external merge也就是把数据分成多个小文件写到磁盘再归并。同样是排序内存排序可能只需几百毫秒落盘排序动辄几秒甚至几十秒。关键点在于work_mem不是全局只给一条语句用一次。如果有好几个操作节点都需要内存各自都可能分配一份。比如一条SQL里既有排序又有哈希连接那么理论上可能需要两份work_mem。这是新手最容易忽略的坑。2.2 默认4MB为什么在实战中远远不够PostgreSQL的默认work_mem只有4MB这个取值更多是为了保证低配环境不会轻易出现内存耗尽而不是说它适合生产环境。现在的服务器内存普遍32GB起步业务表动辄几千万行一个几十万行结果集的排序想压在4MB以内几乎不现实结果就是大量排序落盘临时文件疯狂膨胀查询延迟严重。你可能要问了直接把全局work_mem改到512MB行不行先别急。这里有个非常重要的内存放大公式系统里可能同时运行的并发连接数乘以每个查询里会占用work_mem的操作个数再乘以work_mem的配置值才是理论上可能被占用的内存总量。如果你的配置是512MB同时有40个连接在做排序每个查询按平均两个操作算理论峰值就是512MB乘以80那是40GB以上的内存。真到那个程度操作系统内存先被吃满接下来就是swap甚至OOM。所以我更推荐的做法是把全局work_mem设置在一个相对保守的范围比如16MB到64MB同时针对少数确实需要大吃内存的报表查询、ETL任务单独用会话级或者角色级的方式调大。这样既照顾到了大多数简单查询又不会让内存预算被少数几个大查询瞬间击穿。2.3 怎样用EXPLAIN ANALYZE判断work_mem是不是瓶颈判断某个查询是不是因为work_mem太小而变慢最直接的办法就是看执行计划。在psql里执行EXPLAIN (ANALYZE, BUFFERS) 你的查询;找到Sort节点如果看到这样的输出Sort Method: external merge Disk: 123456kB那基本断定这个排序已经落盘了work_mem不够。如果看到的是Sort Method: quicksort Memory: 20480kB说明排序在内存里完成work_mem给了20MB空间如果配置值正好也是20MB左右说明这个节点是贴着配额完成。继续看哈希相关的节点有没有类似“Memory usage: 51200kB”或者“Hash Memory”的提示如果某项内存使用量接近work_mem上限且还不是一次性能完成的大概率也要调整。我在实际排查的时候习惯先用会话级语句去验证而不是一上来就改全局配置。做法是SET work_mem 256MB;然后再跑一遍EXPLAIN ANALYZE。如果执行计划里的落盘排序变成了quicksort查询时间明显下降那就说明这个查询确实需要更大的work_mem。接着再思考一个问题生产环境同时会有多少条类似查询在跑这个256MB到底能不能安全地全局铺开。2.4 内存放大效应为什么不能盲目调到GB级我把这个单独拿出来讲因为翻车现场几乎都是从这里开始的。内存放大效应听起来是个术语其实道理很直白work_mem是“每个操作”的预算不是“每台机器”的预算。一个查询里有两个排序节点它就敢申请两份同一时间有六个用户各跑一条类似查询那这个数字还得再乘六。我们不妨算一笔账。一台64GB内存的机器假设去掉系统和业务进程给数据库的可用内存是48GB。如果全局work_mem设为1GB同一时间有8个并发连接每个连接上的复杂查询平均用3个排序/哈希操作理论峰值就需要24GB看起来还在喘息范围。但如果并发数变成20峰值直接到60GB那就彻底失控了。更麻烦的是PostgreSQL通常要到内存实在不够用了才会把操作切换到落盘模式而在到达那个临界点之前操作系统可能已经先陷入疯狂的swap。这也是为什么很多有经验的DBA宁可让一部分排序落盘也不愿意为了把一个查询提速20%而把整体稳定性押上去。数据库优化永远是取舍不是数学题。3. 两个参数的配合逻辑与调优先后顺序3.1 调优顺序先共享缓存再会话内存我接手的项目里有不少同事把这两个参数当成两个独立的旋钮一个往左拧一个往右拧结果哪个都没拧对。其实它们之间有明确的配合关系。先想一个问题如果表里的数据在shared_buffers里根本命中不了每次查询都要到磁盘上把数据搬进来那么即便给work_mem一个很大的额度查询执行到排序阶段之前就已经慢掉了一大截。排序本身再快也是建立在数据读取得够快的基础上。所以我的调优顺序基本固定先评估shared_buffers观察数据页的命中率再看有没有必要调整把数据页这一层稳定下来之后再回到查询计划去找那些落盘的排序和哈希节点决定work_mem该怎么动。顺序反了最典型的结果是work_mem调大以后排序确实不落盘了但查询整体耗时没有明显改善因为瓶颈根本不在这里。另外还有一层关系值得注意。shared_buffers变大以后更多表页常驻在数据库进程自己的内存里排序操作读取这些页面时可以直接在共享内存里拿到不走操作系统IO这时候再给work_mem一个合理的值两者的效果会叠加得更明显。换句话说两个参数协同工作才能真正把一个重查询“留在内存里跑完”。3.2 从查询计划看两者如何共同影响性能我拿到一条慢查询除了看Sort节点之外还会重点看两个指标。一个是Buffer命中相关的信息EXPLAIN ANALYZE里会输出类似“Buffers: shared hit123 read456”的内容。shared hit表示在shared_buffers里直接命中的页面数read表示从磁盘或OS缓存读取的页面数。如果read比例很高说明shared_buffers可能偏小或者数据本身的访问局部性不好。另一个是执行计划里的实际执行时间和预估行数如果实际行数和估算行数差得离谱那问题多半出在统计信息或SQL写法上跟这两个参数没关系。举个简单例子一条大范围查询过滤出10万行再排序如果执行计划的read部分有几十万页先把数据读进来这一步可能占掉整体耗时的大头。这时候你去调work_mem意义不大反而应该考虑是不是该加索引或者把shared_buffers加大一些、增加索引页面缓存命中率。相反如果查明数据几乎都在shared_buffers里命中只有排序那一步在落盘那work_mem才是真正的主角。有个经验可以分享数据库层面先解决“读得慢”再解决“算得慢”。“读得慢”大多跟缓存命中、索引相关“算得慢”才轮到work_mem这类执行内存参数。3.3 配合监控的三个关键指标实战里我不会只靠EXPLAIN一条一条去看太慢了。我会把监控固定下来重点关注三个指标。第一临时文件和临时字节数。PostgreSQL的pg_stat_database视图里有temp_files和temp_bytes两个字段分别表示这个数据库累计创建的落盘临时文件数量和大小。如果temp_bytes持续增长说明落盘排序或哈希操作频繁work_mem在总体上是偏小的。第二shared_buffers的命中率。可以用pg_stat_database中的blks_hit和blks_read算一个近似命中率。不过我得提醒一句这个命中率包含了OS缓存的命中不能完全等价于shared_buffers的命中。想要看更精细的指标可以配合pg_buffercache扩展按表统计哪些数据页在缓冲里被访问得多。第三慢查询日志里那些执行计划中带“external merge”或者“Disk: xx”字样的SQL。把这些SQL定位出来后有针对性地用EXPLAIN ANALYZE进一步确认而不是盲目调整全局参数。这三个指标一起看基本能形成一个判断闭环到底该加shared_buffers还是该调work_mem还是两者都要动。4. 实操从查看配置到修改生效的全流程4.1 查询当前参数值动手之前先把现状摸清楚。用SQL可以直接看当前会话里生效的值SHOW shared_buffers; SHOW work_mem;如果要看更完整的参数信息包括单位、来源、是否能够动态修改可以查pg_settings视图SELECT name, setting, unit, context, boot_val, reset_val FROM pg_settings WHERE name IN (shared_buffers, work_mem);这里有个值得说的细节context字段会告诉你这个参数是在postmaster启动时生效还是session级别就能改。shared_buffers一般是postmaster意味着要改它必须重启实例work_mem一般是user意味着DBA甚至可以随时指定某个用户在某个会话里用不同的值。另外注意SHOW work_mem返回的是当前会话的值不是全局默认值。如果你用连接池工具连接数据库个别连接池可能设置了会话参数这时候看到的值不一定等于postgresql.conf里的值。如果要确认全局值可以用pg_settings里的boot_val或者直接去配置文件里看。4.2 修改参数的方法与生效方式差异PostgreSQL提供了至少三种改参数的方式直接编辑postgresql.conf、使用ALTER SYSTEM、以及会话级或角色级的SET命令。三种方式有各自的适用场景。全局修改一般推荐ALTER SYSTEM因为它会把配置写进一个独立的配置文件里不用手动去翻庞大的主配置文件也方便以后用pg_reload_conf()统一重载。ALTER SYSTEM SET shared_buffers 8GB; ALTER SYSTEM SET work_mem 64MB;执行完以后分别处理生效方式。shared_buffers需要重启实例work_mem可以只做reloadSELECT pg_reload_conf();重启数据库实例时不同平台命令不太一样可以用系统自带的服务管理工具完成也可以在PostgreSQL的bin目录下用pg_ctl命令。重启前务必确认配置没写错否则实例可能起不来。稳妥做法是先用pg_ctl工具做一次dry run检查或者先用一条SQL验证当前配置里的值能否被实例接受。ALTER SYSTEM也有一个坑如果同一个参数在主配置文件里也设置了优先级取决于顺序。PostgreSQL的配置加载规则里ALTER SYSTEM产生的值会覆盖postgresql.conf里的同名设置。如果哪天你发现改了postgresql.conf怎么不生效可以去检查一下postgresql.auto.conf里是不是还残留着ALTER SYSTEM写进去的旧值。4.3 用EXPLAIN ANALYZE验证work_mem效果验证阶段我的习惯是先找一个具体的慢查询在测试环境或只读从库上做对比。假设有这么一条查询对一个大表做分组排序EXPLAIN (ANALYZE, BUFFERS) SELECT category, count(*) FROM test_table GROUP BY category ORDER BY category;第一次跑的时候如果Sort节点显示external merge执行时间可能比较难看。接着会话级设置work_memSET work_mem 256MB;再跑一次同样的EXPLAIN ANALYZE观察Sort节点是否变成quicksort执行时间、磁盘IO和Buffers部分有没有明显变化。如果变化明显就可以考虑把这个值提升到某个合理范围。如果变化不明显说明排序落盘并不是这个查询的绝对瓶颈还得回到数据读取和表结构上找原因。这里要特别提醒EXPLAIN ANALYZE是真的会把查询完整执行一遍的在生产实例上操作时要选择业务低峰期并且别拿特别耗时的查询直接跑避免把线上环境拖垮。4.4 一个典型报表慢查询的调优现场讲一个我印象很深刻的案例虽然具体业务做了脱敏但思路很有代表性。某统计系统在每天凌晨生成一批报表其中一个核心SQL是将一张存量上千万行的明细表按维度和时间片排序后做聚合。最初表现是查询执行时间接近30秒每天报表任务经常超时。第一次分析时EXPLAIN显示Sort节点一直在用external merge落盘量接近1GB。我先在会话里把work_mem临时调到512MB重新跑了一遍排序确实变成了quicksort但整体查询时间只从30秒下降到20秒并没有想象中那么大提升。进一步看Buffers部分发现shared hit很少read比例很高说明数据页缓存命中率明显偏低。这时候我才意识到这张表平时很少被访问到数据在shared_buffers里几乎不驻留每次报表查询都要从磁盘读进去。后续调整方案分两步走先结合机器实际内存把shared_buffers从2GB稳妥地提升到8GB同时对报表任务的连接通过单独的数据库角色把work_mem设为256MB。经过几轮观察查询时间最终降到8秒左右而且临时文件数量也大幅下降。这个案例给我一个特别深的印象两个参数不是彼此孤立的一个管“能不能快点把数据搬进内存”一个管“搬进来之后能不能在内存里快速算完”两件事都做好了整体效果才会出现。5. 常见问题与排查技巧实录5.1 work_mem调大后排序还是落盘怎么回事这个问题我遇到过不止一次。最典型的原因是你调大的是全局默认值但当前连接池里早就分配好了会话没有重新加载参数或者连接池本身在创建连接时会用SET命令覆盖掉全局值。排查时可以分两步走先确认当前会话的真实值是否是你想要的值用SHOW work_mem看看再检查连接池或者中间件里有没有针对会话级别参数的统一设置。另一个很隐蔽的原因是查询里的排序可能不止一个节点。你看到某个Sort节点确实用了内存排序但后面还有另一个Sort节点落在磁盘上整体耗时还是上不去。这时候只看第一个Sort节点就会判断错误必须把执行计划从头到尾扫一遍找出所有标记为external merge的节点。还有一个原因和内存分配粒度有关work_mem给的是每个操作的上限不是保证一定会给足。当系统内存压力很大时PostgreSQL宁可把这个操作切到落盘模式也不会强行把机器内存吃干榨净。5.2 shared_buffers调大后启动缓慢怎么办先明确一个正常范围一台几十GB内存的机器从发出启动命令到实例接受连接几十秒是可以接受的。如果超过一分钟甚至更久那就要检查是不是给的值超出物理内存了。尤其要注意一些虚拟化实例系统里显示的内存可能是超配的实际可用量并没有那么大。如果确认硬件没问题还是慢可以检查一下日志里有没有类似“could not write to file”或者共享内存不足的提示。部分Linux系统的共享内存内核参数kernel.shmmax和kernel.shmall会影响PostgreSQL能否成功申请大块共享内存。虽然现代系统通常自动处理得不错但偏低的内核限制确实会让实例启动变慢甚至失败。遇到这种情况可以调整内核参数或者用分片共享内存的方式绕过限制不过那就进入系统层面的调优范围了不在这里展开。更实用的建议是把shared_buffers改成需要重启生效的参数之后一定先找一个维护窗口把配置改了重启观察启动时间和进入正常运行后的性能再决定是否回退。不要在下班前匆匆改完就回家。5.3 并发连接过多导致内存不足如何收手很多人把work_mem调大以后原以为自己能驾驶一台64GB内存的机器结果20条连接同时跑大查询内存直接报警数据库响应变得像蜗牛。这就是前面说的内存放大效应在现实里爆发。遇到这种状况唯一的短期办法就是收缩会话内存把连接数降下来或者把work_mem临时设置调低让一部分查询落盘先把系统的稳定性救回来。长期方案分几个方向一是对产生大量内存消耗的查询做分流比如把报表类查询放到独立的只读从库上跑二是给不同角色分配不同的work_mem让普通业务连接保持较小值只对特定角色放大三是在应用层做并发限制避免大批量任务在同一时刻挤压数据库。实际操作中我最常用的是角色级设置一行SQL就能搞定ALTER ROLE report_user SET work_mem 256MB;这样只有report_user这个角色建立的会话会走大内存配置其他普通连接仍然保持全局默认值。既照顾了报表需求又把整体风险控制住了。5.4 快速估算合理值的方法虽然没有万能公式但可以给一个快速估算的思路。先估算数据库实例可用的内存总量去掉系统和其他业务占用然后预估峰值并发大查询数量再用可用内存除以“并发数乘以每查询操作数”得出一个相对安全的work_mem初始值。举个例子64GB内存的机器扣除系统后大约50GB给数据库。日常峰值并发里大查询最多5个每个大查询平均会同时触发2个排序/哈希操作。那么50GB除以10大约5GB这是理论上限。为了保命我一般会再打个三折起步先设512MB到1GB观察临时文件数量和执行计划逐步微调。宁可落盘几次也不要OOM一次。OOM对数据库的伤害比起那几次落盘的性能损失要大得多。另外work_mem还可以按查询单独设置。单个复杂SQL可以在事务里用SET LOCAL work_mem 512MB;事务结束后自动恢复这种用法对临时跑批操作非常友好。6. 容器与虚拟化环境下的额外提醒6.1 容器内存限制对shared_buffers的影响现在的部署环境里PostgreSQL跑在容器里已经很常见。容器有个特点它在自己里面看到的内存总量通常就是宿主机分配给容器的上限并没有计算宿主机上其他进程的占用。如果你在容器里按照物理内存的20%去算可能算出来的值比容器实际能用的还大。举个例子某个宿主机物理内存64GB一个容器被限到8GB内存容器内看到的free值就是8GB。如果你按20%算设了1.6GB的shared_buffers看起来没问题但容器里还有PostgreSQL进程本身、work_mem、临时文件、连接管理等各种内存开销很快就能把8GB打满。一旦超出容器限制容器会被内核直接杀掉这在生产环境里就是事故。在容器环境里我建议把shared_buffers和work_mem的取值都再保守一些。shared_buffers可以先按容器上限的10%到15%起步work_mem则保持默认或者用一个较小的全局值只对必要角色放大。不要觉得浪费容器环境里的稳定运行永远排在性能前面。6.2 work_mem在共享型实例上的风险控制虚拟化环境尤其是云上的共享型实例还有一个麻烦别的实例和你在同一台物理机上抢资源。你在自己实例里看到的内存是稳定的但真实物理机的CPU、IO、内存带宽可能是波动的。这时候如果work_mem设置得很大一旦多人同时触发大排序那不只是你自己实例变慢可能还会干扰同宿主机的其他邻居最终被平台限流或强制重启。在这种场景下我的原则是work_mem这种参数宁可保守也不要追求账面好看。临时文件落盘的代价通常还能忍受但OOM或者被内核强杀是任何业务都无法接受的。即便遇到特定大查询也优先考虑在应用层拆批、优化SQL而不是把所有内存重担都压给数据库的work_mem。如果确实需要通过调整参数解决持续的性能问题建议先在预发环境按生产配置压测一轮把并发、数据量、查询类型都覆盖到再决定最终取值。毕竟这两个参数的调优最终看的不是某一个查询快了没有而是整个系统在真实负载下是否稳得住。个人体会是这两个参数我调了很多年最大的感受就是“克制”两个字。shared_buffers不是越大越快work_mem也不是越多越好它们都是在同一个整体内存预算里做分配。先把监控做起来用数据判断瓶颈再小步调整、逐步验证比一上来就堆配置值要靠谱得多。希望这篇东西能帮你少走一些弯路。
