如何根据临时文件的日志优化work_mem？

Question

在一个文章中，我发现调整work_mem的一个启发是：

1. 以低值开始:32-64 32
2. 然后在日志中查找“临时文件”行
3. 设置为最大临时文件的2-3倍。

跑步：

SHOW log_temp_files; -- res: 0

我发现启用了日志记录临时文件。

1. 记录temp_files的缺点是什么？
2. 如何查询temp_file日志？
3. 是否有更好的启发式方法来估计work_mem的正确值？

Answer 1

启发式很好。基本上，如果您得到的临时文件“太频繁”(故意模糊)，这可能是一个双赢的增加work_mem。

如果您更改了log_temp_files，您将在日志文件中获得消息。您需要操作系统访问数据库服务器来读取这些数据。

但是还有一种选择:统计数据视图pg_stat_database有两列：

• 由此数据库中的查询创建的临时文件的temp_files bigint数量。
• temp_bytes bigint数据库中查询写入临时文件的总数据量。

这些统计数据是累积的，因此您必须定期查询这些值，并查看它们是否会大量增加。如果是的话，为work_mem尝试更高的设置可能是个好主意。

Answer 2

在我看来，这个建议很有问题。它的前提是使用临时文件是不好的。他们不坏，他们肯定比崩溃或被遗忘更好。但是，如果你接受了这个前提，为什么从低开始，然后爬上“正确”的值呢？只需将work_mem设置为一开始就高得离谱，并完成它。(直到你意识到这是一个错误的前提。)

而且，任何一个临时文件都限制在1GB以内。如果您需要更多的临时空间，它将使用多个文件，但每个文件将被单独记录。因此，只要查看最大的日志记录行，就不会显示任何一条语句所使用的最大临时空间数量。(这个事实确实在一定程度上限制了这个建议可能造成的损害，因为您至少不会将其设置为3GB以上)。

显示log_temp_files；-- res: 0 我发现日志记录临时文件是禁用的。

不，0意味着记录一切。-1指残疾。

Answer 3

您可以在postgres配置文件中启用临时文件的日志记录(请参阅文件，然后您将在日志中看到哪些操作“溢出”到磁盘(排序、散列)。

话虽如此，我不认为这是确定好的work_mem值的灵丹妙药。您不能只将日志中的一些值乘以3，并将其用作work_mem。重要的是要理解work_mem是如何工作的--它是每个后端(每个连接)内存和每个操作的事情。

所以您应该这样想：“在典型的场景中，有多少并发用户同时运行内存密集型的东西？”然后，您应该为work_mem“预留”内存数量(不包括共享缓冲区、其他进程、一些合理的内核缓存等)。是的，这不是精确的科学。如果通常有一两个并发用户运行大量排序，则可以轻松地将work_mem设置为1GB。如果有500个并发用户运行轻量级查询，则可以(而且应该)设置更小的work_mem。