work_mem 调优

实用指南 · 上游译文 #92

适用版本以正文为准，未声明统一最低版本。

来源与验证说明

原作者：Nikolay Samokhvalov · 英文原文 · 中文翻译与校订：xiongcc

本页目录

work_mem 用于查询执行期间排序和哈希操作的内存分配。默认情况下，其大小为 4MB。由于其性质，调优 work_mem 相对复杂。

这里介绍了一种可能的调优方法。

work_mem 的粗略调优和安全值

一个查询可能会多次"消耗" work_mem (用于多个操作)。但是，并不是为每个操作都完全分配 — 一些操作可能只需要较少的内存。

因此，很难可靠地预测处理工作负载时需要使用多少内存，而不经过对工作负载的实际观察。

PostgreSQL 13 引入了 hash_mem_multiplier，用于设置哈希操作相对于 work_mem 的内存倍数。单个哈希操作的内存上限为 work_mem * hash_mem_multiplier。

译者校订：原文将 PostgreSQL 13–16 的默认值统称为 2，并不准确。PostgreSQL 13 文档中的默认值为 1.0；实际配置请通过 SHOW hash_mem_multiplier 确认。只有该值为 2 时，上限才是 2 * work_mem。

值得一提的是，在 Linux 中了解会话使用了多少内存本身是非常困难的 — 可以参考Andres Freund 的精彩文章：Analyzing the Limits of Connection Scalability in Postgres (2020 年)。

一种安全的调优方法是：

估算可用内存 — 从中减去 shared_buffers、maintenance_work_mem 等
然后将估算的可用内存除以 max_connections 和一个附加值 (例如 4-5，或者更多，以确保安全) — 假设每个后端进程最多使用 4 * work_mem 或 5 * work_mem。当然，这个乘数本身只是一个粗略估计 — 实际上，OLTP 工作负载通常对内存的需求远远低于这个值 (例如，进行大量主键查找意味着平均内存消耗非常低)。

在实践中，调整 work_mem 至一个较高值是有意义的，但前提是理解了 PostgreSQL 在特定工作负载下的行为之后再进行。以下步骤是进一步调优的迭代方法的一部分。

监控临时文件的创建频率及其大小。相关信息来源：

进一步调优 work_mem 的目标是完全消除临时文件的创建，或尽量减少其创建频率和大小。

如果可能，考虑优化那些涉及临时文件创建的查询。为此投入适当的精力 — 如果没有明显的优化空间，继续执行下一步。

如果已经做了合理的优化工作，现在可以考虑进一步提高 work_mem。

然而，提升多少合适呢？答案取决于单个临时文件的大小 — 通过上面描述的监控，我们可以找到最大和平均临时文件大小，并从中估算所需的提升量。

🎯 TODO: 详细步骤

针对单个查询提高 work_mem 是有意义的。考虑两种方式：

在单个会话或事务中设置 work_mem (使用 set local ...)，或者
如果为不同的工作负载部分使用不同的数据库用户，考虑针对特定用户调整 work_mem (例如，针对那些执行分析类查询的用户)：alter user ... set work_mem ...。

只有在前述步骤不适用时 (例如，查询优化困难且无法针对部分工作负载调优 work_mem) 才考虑全局提升 work_mem，同时评估 OOM 的风险。

一段时间后，审查监控数据，确保情况有所改善，或决定进行下一次迭代。

在 PostgreSQL 14 中，新增了一个函数 pg_get_backend_memory_contexts() 及其对应的视图；请参考文档。这对于详细分析当前会话如何使用内存非常有帮助，但该功能的主要限制是它只能与当前会话一起使用。

🎯 TODO: 如何使用它。