别再盲目调大 work_mem 了！PostgreSQL 这个“内存乘数”参数，才是性能的关键

每当生产环境 SQL 慢查询出现时DBA 的第一反应往往是“调大 work_mem准没错”但这其实是一个“钝刀子”操作调大了它确实能让 Hash Join 少写临时文件但也让 Sort、Merge Join 等原本不需要那么多内存的操作吃掉了宝贵的内存导致并发一高服务器直接 OOM。很多时候我们试图用一个参数去解决所有问题结果只是把“性能瓶颈”从磁盘搬到了内存甚至引发了更大的风险。其实PostgreSQL 13 引入了一个极其精妙的设计hash_mem_multiplier。它把“哈希操作”和“排序操作”的内存预算从根儿上解耦了。这篇文章咱们把这个参数从原理到实测彻底讲透。搞懂它你就不需要再为了 Hash Join 而盲目扩大全局内存性能也能精准提升 30%。 固定 work_mem 不动只调一个乘数Batches 从 8 降到 1临时文件直接归零执行时间下降 31.7%——PG18 真实实测数据全程可验证。01先从一个让 DBA 崩溃的场景说起你有没有遇到过这种情况线上某条 SQL 突然变慢打开 EXPLAIN ANALYZE 一看——Parallel Hash (cost25018.33..25018.33 rows833333 width4) Buckets: 524288 Batches: 8 Memory Usage: 13920kB temp written11084 I/O Timings: temp write757.825 ms Execution Time: 2693.475 ms757ms全部花在写临时文件上。你心里清楚这是 Hash 内存不够分批溢写了。然后你开始想调大 work_mem调多大16MB → 32MB → 64MB调大了并发 100 个连接每个连接跑几个哈希节点内存直接炸排序也会跟着吃更多内存OOM 风险怎么办这个左右为难的困境PostgreSQL 13 用一个参数彻底解决了——hash_mem_multiplier今天这篇文章带你把这个参数从原理到实测彻底搞懂。1Part 1历史遗留的内存分配痛点PG13 之前的世界是什么样的在 PostgreSQL 13 之前所有依赖 work_mem 的操作共享同一个内存上限排序Sort哈希连接Hash Join哈希聚合Hash Aggregate归并连接Merge Join……看起来统一管理其实暗藏一个根本性问题哈希操作和排序操作的内存开销模型完全不同。排序 vs 哈希内存消耗为什么差这么多排序操作tuplesort的内存开销内存主要用于排序结构本身以及需要参与排序的 tuple/键数据当内存不足时转为外部排序external merge使用临时文件整体 per-tuple 额外开销相对可控哈希操作Hash Join / Hash Aggregate的内存开销哈希桶数组bucket array桶数按 2 的幂次增长存在固定开销每行的哈希元数据哈希值、链表指针、对齐填充等构建侧数据本身在哈希表中的形态拷贝或引用三类开销叠加单位 tuple 的额外负担明显高于排序。举个具体的例子感受一下存储 100 万行、行宽约 100 字节的数据排序在相对小的 per-tuple 额外开销下完成外排触发点较晚同样的数据构建哈希表往往需要显著更高的内存预算才能避免 Batches 1死局就这样形成了这个死局在 PostgreSQL 13 用一个设计被打破了。2Part 2hash_mem_multiplier 是怎么解决这个问题的核心公式先记住哈希操作内存上限 work_mem × hash_mem_multiplier 排序等其他操作 work_mem不受影响PG13 引入默认值 2.0。注意默认值是 2.0不是 1.0。也就是说PG13 起默认就会给哈希操作提供比旧版本更高的内存预算默认行为相比升级前会更敢用内存这在升级时需要纳入评估。设计考量一适配哈希更高的结构性开销引入乘数后可以让哈希操作在 work_mem 基础上获得额外放大的预算使哈希表更大概率在内存内完成从而✅ Hash Join 的 Batches 降低减少分批 I/O✅ HashAgg 的外排减少Disk Usage 下降✅ 整体执行时间显著改善而排序算子的预算保持不变两者彻底解耦。设计考量二实现资源的精细化隔离与控制这是这个设计最精髓的地方典型场景执行计划里哈希节点频繁出现 Batches: 1 或大量 temp 写入但排序操作并不是瓶颈。以前你只能全局调大 work_mem连带影响所有操作。现在你只需要SET hash_mem_multiplier 4.0; -- 只影响哈希排序不动精准干预避免全局内存失控。设计考量三升级风险可控行为可回退如果你希望尽量贴近 PG13 之前的旧版行为可以把它设置为 1.0如果升级后发现内存压力上升先检查这个参数是否叠加了并发/并行效果生产升级后应结合并发数、SQL 形态、work_mem 以及内存总量综合评估是否需要调整这个参数的存在本身就是一个安全阀——提升默认体验的同时给你保留了回退空间。设计考量四对并行哈希更友好但总内存会随并行度放大在并行执行场景下Parallel Hash Join每个参与进程都会按照哈希预算基准运行。单个哈希算子在单进程上的预算基准单进程哈希预算 work_mem × hash_mem_multiplier并行时leader workers 会导致整体预算随并行度放大近似估算并行哈希总预算 ≈ (workers 1) × work_mem × hash_mem_multiplier调大 hash_mem_multiplier 在提升哈希性能的同时必须把并行度与并发数一起纳入总内存上限评估否则高并发下的内存压力可能超出预期。3Part 3关键设计权衡与注意事项⚠️ 注意一每个哈希节点独立计算乘法效应必须警惕一条 SQL 里可能有多个 Hash Join / Hash Aggregate 节点每个节点都各自按 work_mem × hash_mem_multiplier 独立计算预算。再叠加上总内存占用会出现非常明显的乘法效应。举例work_mem64MBhash_mem_multiplier4.0并发 50 个连接每个连接的 SQL 有 3 个哈希节点2 个 workers极端峰值内存 ≈ 50 × 3 × 3 × 64MB × 4.0 115,200MB ≈ 112GB这个数字在真实场景下不会完全跑满但足以说明不能无限制拉高倍率。⚠️ 注意二只影响哈希类算子其他操作一概不干扰hash_mem_multiplier 只对以下操作生效Hash Join哈希连接Hash Aggregate哈希聚合对于排序、归并连接、位图扫描等操作——完全没有直接影响。这是精细化控制的核心保障后面的实验会用数据直接证明这一点。⚠️ 注意三与 temp_file_limit 是不同层面的控制当哈希表内存占用超过 work_mem × hash_mem_multiplier 时PostgreSQL 会开始分批并写临时文件。temp_file_limit 则是限制单个会话允许使用的临时文件总量上限防止单个会话临时文件无限膨胀占满磁盘。两者的关系调好前者可以减少溢写发生设好后者可以防止溢写失控。两者应该配合使用而不是非此即彼。4Part 4PG18 真实实验用数据说话实验口径全程固定 work_mem 16MB只调整 hash_mem_multiplier。同时禁用 nestloop 和 mergejoin避免计划漂移SET enable_nestloop off; SET enable_mergejoin off; SET work_mem 16MB; SET track_io_timing on;实验基线pg_stat_database 初始状态SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname current_database(); temp_files | temp_bytes ------------------------ 7 | 160930944⚠️ 重要说明pg_stat_database 的 temp_files/temp_bytes 是累计值不会自动回落。本实验用「前后差值」来判断某次测试是否新增了 temp 文件这是正确的对比口径。实验一Parallel Hash Join❌ hash_mem_multiplier 1.0对照组SET hash_mem_multiplier 1.0; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT count(*) FROM hj_a a JOIN hj_b b USING (id);关键输出- Parallel Hash Join (actual rows666666.67 loops3) Hash Cond: (a.id b.id) - Parallel Hash (actual rows666666.67 loops3) Buckets: 524288 Batches: 8 Memory Usage: 13920kB Buffers: shared hit642 read16043, temp written5172 I/O Timings: shared read7.697, temp write95.461 Buffers: shared hit1209 read32161 written1, temp read11013 written11084 I/O Timings: shared read21.494 write0.019, temp read47.015 write757.825 Execution Time: 2693.475 ms跑完后查 pg_stat_databasetemp_files | temp_bytes ------------------------ 52 | 251731072前后差值数据交叉验证EXPLAIN 里 temp written 11084 blocks按 PG 默认 8KB/block11084 × 8KB 88,672KB ≈ 86.6MB和 pg_stat_database 的增量完全吻合溢写是真实发生的证据链闭合。✅ hash_mem_multiplier 4.0只改这一个参数其他全部不变SET hash_mem_multiplier 4.0; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT count(*) FROM hj_a a JOIN hj_b b USING (id);关键输出- Parallel Hash Join (actual rows666666.67 loops3) Hash Cond: (a.id b.id) - Parallel Hash (actual rows666666.67 loops3) Buckets: 2097152 Batches: 1 Memory Usage: 94656kB Buffers: shared hit738 read15947 Buffers: shared hit1110 read32260 Execution Time: 1840.103 ms跑完后查 pg_stat_databasetemp_files | temp_bytes ------------------------ 52 | 251731072 与上次完全相同没有任何新增前后对比来一波精准推算——为什么 1.0 不够4.0 就够实验中的执行计划显示Workers Planned: 2即 leader 2 workers 3 个进程参与。并行哈希预算近似公式总哈希预算 ≈ (workers 1) × work_mem × hash_mem_multiplier而 EXPLAIN 告诉我们Batches1 时实际 Memory Usage ≈94,656kB ≈ 93MB。48MB 93MB → 内存不足 → 溢写 → Batches8 192MB 93MB → 内存充足 → 单遍完成 → Batches1数字完美对上理论与实测高度吻合。⚠️ 一个重要补充当发生 batching 时EXPLAIN 里的 Memory Usage 表示的是单个 batch 的内存使用量而不是如果不分批需要多少。所以判断内存够不够最直接的方式是看 Batches 是否为 1、temp I/O 是否消失而不是直接读 Memory Usage 的数字。实验二Hash Aggregate——倍率变化的完整曲线同样固定 work_mem 16MB依次测试 4 个倍率 hash_mem_multiplier 1.0Batches 高达 41Disk Usage 64MBtemp 读写极为密集。 hash_mem_multiplier 4.0HashAggregate Planned Partitions: 4 Batches: 5 Memory Usage: 75833kB Disk Usage: 31592kB Buffers: temp read3608 written6631 I/O Timings: temp read8.016 write28.328 Execution Time: 3683.933 msBatches 从 41 降到 5Disk Usage 减半执行时间降低 17.6%。 hash_mem_multiplier 8.0HashAggregate Batches: 5 Memory Usage: 150585kB Disk Usage: 224kB Buffers: temp read16 written38 I/O Timings: temp read0.103 write0.259 Execution Time: 3022.944 msDisk Usage 从 31,592kB 骤降到 224kBtemp I/O 几乎消失执行时间最短hash_mem_multiplier 10.0HashAggregate Batches: 1 Memory Usage: 150553kB Buffers: shared hit1516 read11767 无任何 temp I/O Execution Time: 3090.094 msBatches 终于降到 1临时文件完全消失——但执行时间反而比 8.0略高了 67ms。 关键洞察Batches1 不是终极目标注意这个非常有价值的工程现象hash_mem_multiplier 8.0Batches5但 Disk Usage 仅 224kB执行时间 3022ms ⭐最快 hash_mem_multiplier 10.0Batches1完全不溢写执行时间 3090ms反而略慢为什么会这样当溢写量已经极小224kB几乎可以忽略把 batches 从 5 压到 1 需要分配更多内存、维护更大的哈希表结构这部分额外开销未必比偶尔小量溢写更低。工程结论性能最优点不一定是 Batches1 的极限点。当 Disk Usage 已经趋近于零时继续拉高倍率的边际收益会快速递减甚至出现轻微的性能回退。5反推需要多少内存才能不溢写这是这组实验最有价值的地方——我们可以从实测数据直接反推所需倍率Batches1 时Memory Usage ≈ 150,553 kB ≈ 147 MB work_mem 16MB固定不变 需要的倍率 ≈ 150MB ÷ 16MB ≈ 9.4所以在 hash_mem_multiplier 10.0 时恰好拿到 Batches: 1完全符合计算预期这个反推公式在生产环境同样适用所需倍率 ≈ 实际哈希表峰值内存需求 ÷ work_mem6Sort 对照实验精细化控制的铁证同样的数据排序 SQL 在 hash_mem_multiplier 1.0 和 4.0 下的结果-- multiplier1.0 Sort Method: external merge Disk: 34000kB -- multiplier4.0 Sort Method: external merge Disk: 28992kB 差异属于运行噪声/缓存状态变化本质完全相同Sort 依然是外部排序丝毫不受 hash_mem_multiplier 影响。这就是精细化控制的直接铁证✅ 哈希类算子预算可独立放大✅ 排序预算纹丝不动✅ 不会触发全局 work_mem 放大导致的 OOM 乘法风险如果这里 Sort 也因为 multiplier4.0 而进入内存排序那这个参数就失去了精细化的意义。但实测证明它确实做到了精准隔离。02全部实验数据汇总03设计理念总结04生产环境调参建议诊断流程-- 第一步看 EXPLAIN ANALYZE 里有没有这些信号 -- Batches: 1 → 哈希表内存不足正在分批 -- temp written xxx → 溢写到临时文件 -- temp write xxx ms → 大量时间花在写磁盘 -- 第二步确认当前配置 SHOW work_mem; SHOW hash_mem_multiplier; -- 第三步反推所需倍率 -- 从 EXPLAIN 中找 Batches1 时的 Memory Usage -- 所需倍率 ≈ Memory Usage ÷ work_mem调参思路1. 先确认是哈希操作的问题看 Batches 和 temp I/O 2. 估算所需倍率所需倍率 ≈ 哈希表峰值内存 ÷ work_mem 3. 考虑并行度总预算 ≈ (workers1) × work_mem × multiplier 4. 考虑乘法效应乘以并发数 × 单 SQL 哈希节点数 5. 设置倍率验证 Batches 和 temp I/O 变化 6. 注意追求 Batches1 不一定是最优当 Disk Usage 极小时可以停下来安全边界估算安全上限 ≈ 系统可用内存 ÷ (最大并发数 × 并行度 × 单SQL哈希节点数 × work_mem × multiplier) 升级 PG13 的用户必看Ansible 的 SSH 参数中 PasswordAuthenticationno 明确禁用了密码认证只接受公钥认证方式。写在最后hash_mem_multiplier 的本质是给内存更饿的哈希操作一张单独的粮票——让它吃饱同时不抢排序和其他算子的口粮在可控的资源边界内尽量减少不必要的分批和外排从而提升性能与稳定性。下次遇到 Batches: 1 和大量 temp I/O在动 work_mem 之前先试试调 hash_mem_multiplier。觉得有收获转发给你的 DBA 同事 你们生产环境的 work_mem 设的多少有没有遇到过 Hash 溢写的惨案欢迎评论区分享作者介绍大家好我是刘峰安丫科技创始人 数据库技术高级讲师专注于PostgreSQL、国产数据库运维与迁移、数据库性能优化等方向。作为 PG中国分会官方授权讲师、PostgreSQL ACE 讲师认证专家我长期活跃在****一线项目实战中拥有10年以上大型数据库管理与优化经验曾深度参与电信、金融、政务等多个行业的数据库性能调优与迁移项目。欢迎关注我一起深入探索数据库的无限可能技术交流不设限 觉得有收获的话记得点赞、收藏、转发支持一下哦别忘了关注我获取更多数据库干货~安呀智数据坊我们能做什么无论你是业务系统的技术负责人还是数据部门的第一响应人我们都能为你提供可靠的支持数据库类型支持Oracle / MySQL / PostgreSQL / SQL Server 等主流数据库核心服务内容性能优化 / 故障处理 / 数据迁移 / 备份恢复 / 版本升级 / 补丁管理系统性支持深度巡检 / 高可用架构设计 / 应用层兼容评估 / 运维工具集成专项能力补充定制课程培训 / 甲方团队辅导 / 复杂问题协作排查 / 紧急救援支持原文链接https://mp.weixin.qq.com/s/3-9jVXOG1LgjsHZVBajr-w