How to tune work_mem.md

How to tune work_mem

我每天都会发布一篇新的 PostgreSQL "howto" 文章。加入我的旅程吧 — 订阅、提供反馈、分享！

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

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

work_mem 的粗略调优和安全值

首先，按照 Day 89: Rough configuration tuning (80/20 rule; OLTP) 中的描述进行粗略优化。

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

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

此外，在 PostgreSQL 13 中，新增了一个参数 hash_mem_multiplier，调整了相关逻辑。PG 13-16 版本中的默认值为 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 在特定工作负载下的行为之后再进行。以下步骤是进一步调优的迭代方法的一部分。

临时文件监控

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

• pg_stat_database，temp_files 和 temp_bytes 列。
• PostgreSQL 日志 — 设置 log_temp_files 为一个较低的值，甚至为 0 (需要注意观察者效应)。
• pg_stat_statements 中的 temp_blks_read 和 temp_blks_written。

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

优化查询

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

进一步提升work_mem：多少合适？

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

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

🎯 TODO: 详细步骤

针对部分工作负载提升work_mem

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

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

全局提升work_mem

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

迭代优化

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

额外内容：pg_get_backend_memory_contexts

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

🎯 TODO: 如何使用它。