排查临时文件使用率过高问题

当排序、哈希或类似操作需要比允许更多的 work_mem 内存时，PostgreSQL 会将其作为临时文件溢出到磁盘。 该操作仍可完成，但其运行速度会慢得多，同时还会占用其他任何内容都无法使用的存储和 I/O 资源。

临时文件是内存大小不匹配的症状。 解决办法几乎总是要么是更好的查询计划，要么是大小更合适的 work_mem。 鲜有更大存储空间。

本指南介绍的症状

• 突然的存储高峰会自行恢复
• 处理小规模输入时很快、但处理大规模输入时会慢得不成比例的查询
• 报告或批处理时段期间的存储利用率攀升
• 排序或哈希操作出现缓慢且没有明显原因

开始之前

启用会话数据、查询存储 运行时和查询存储 等待统计信息；将pg_qs.query_capture_mode设置为TOP或ALL，并将pgms_wait_sampling.query_capture_mode设置为ALL；并启用metrics.collector_database_activity。 请参阅 “使用故障排除指南”。

本指南的组织方式

本指南是故意简短的。 有了查询后，接下来的工作就转到 EXPLAIN 和 work_mem。

演练：每晚存储使用量激增

1. 找出规律。 “ 存储 ”选项卡显示每天晚上 02：00 左右的利用率急剧上升，并在一小时内返回到基线。 会自行释放的存储空间是临时文件的典型特征，因为真实数据的增长不会自行回落。

2. 确认原因。 临时文件选项卡显示，在同一时间窗口内，文件数量和临时文件总字节数均出现峰值，从而确认二者存在相关性。

3. 排除工作负荷。工作负荷 不显示异常元组活动。 这不是工作变多了；而是同一个夜间作业拖延到了别的时段。

4. 找到查询信息。 “ 查询 ”选项卡按临时文件大小的总大小进行排名。 一个查询 ID 占主导地位。 其按桶划分的详细信息显示，每次执行都会写入大量临时数据块，而返回的行数并不多，因此它是在对大型中间结果进行排序，以生成较小的结果集。

5. 在两种修复方案中做出选择。 获取 SQL 并运行 EXPLAIN (ANALYZE, BUFFERS)。 该计划显示，在一个没有相应索引支持的列上执行了外部归并排序。 两个选项：添加索引，这样就完全不需要排序了；或者提高 work_mem，使其能够在内存中完成。 索引是更好的解决办法，因为它消除了这部分工作量，而不是为其买单，因此你可以添加该索引，并仅针对该作业的角色将 work_mem 设高一些，作为安全网。

选项卡参考

存储和临时文件

从 存储 入手，查看是否有峰值。 然后检查 临时文件 ，了解同一窗口中的文件计数和临时字节总数。

• 恢复的峰值 表示临时文件。 请继续阅读本指南。
• 持续增长 表示实际数据增长或膨胀。 本指南无法提供帮助；请参阅 监控 autovacuum。

许多小型临时文件和少数几个超大文件都会造成峰值，但所代表的含义不同。 许多小文件表明有大量操作，每次都略高于 work_mem。 少数几个较大的值表明，某个单一查询远远超出了该上限。

工作量

读取和写入元组活动，以检查溢出是否附带真正增加的工作或相同的工作溢出。

Queries

按生成的临时文件大小对查询进行排名。 为每个存储桶详细信息选择查询 ID：写入和读取的平均值、最小和最大临时块、平均值和总行数、总调用数和执行时间。

需要关注的比率是 临时字节数与返回行数之比。 小型结果集的大型溢出意味着查询具体化和排序的数据远多于最终需要的数据。 通常可以在查询或索引中修复此问题，而不是内存。

如何对找到的查询执行操作：

1. 运行 EXPLAIN (ANALYZE, BUFFERS) 并查找报告磁盘使用情况的外部合并排序或哈希操作。 执行计划会指出发生溢出的具体操作。
2. 添加一个能够提供所需排序或分组顺序的索引，这样就无需再执行排序操作，也就不必承担其开销。
3. 仅选择所需的列。 宽行填充 work_mem 速度更快， SELECT * 通过排序是一个常见原因。
4. 移除不会缩小结果集的联接，并检查是否存在意外的交叉联接。
5. 只有到那时，才考虑参考下一节中的指导来提高 work_mem。
6. 使用 statement_timeout 或 idle_in_transaction_session_timeout 来限制其影响范围，以免失控的查询占满存储空间。

大小 work_mem

work_mem 是每个内部排序或哈希操作在溢出之前可用的内存。 关键在于 每个 这个词。 它适用于每个操作，而不是每个查询，具有多个排序和哈希联接的复杂查询可以同时使用它几次。 将其乘以正在运行这类查询的连接数，真正的暴露程度就会变得清楚。

一个保守的起点是 Total RAM / max_connections / 16。 PostgreSQL 默认值为 4 MB。

与其在整个服务器范围内提高它，不如优先为需要它的特定角色或会话设置 work_mem。 服务器范围的增加将乘数应用于每个连接，这就是临时文件问题变成内存不足问题的方式。 请参阅 “排查高内存问题”。

其他缓解措施

本地 SSD 上的临时表空间。 允许 azure.enable_temp_tablespaces_on_local_ssd 将临时文件移动到本地 SSD，从预配的存储中移出。 启用后，授予访问权限：

GRANT CREATE ON TABLESPACE temptblspace TO public;  -- or a specific role

有关每个 SKU 的本地 SSD 容量，请参阅 Edv4 和 Edsv4 系列 。 此更改会重新定位 I/O，而不是消除它。 仍然值得一试，不过先修正查询。

更多存储。 最后的手段，只有当泄漏是不可避免的和合法的。

优秀的标准是什么样的

本指南无法告诉你的内容

• 哪个计划节点溢出。 指南将查询命名; EXPLAIN (ANALYZE, BUFFERS) 为操作命名。
• 适合您的工作负载的work_mem。 这取决于查询结构和并发情况。 上述指南是衡量起点，而不是答案。
• 触发 work_mem 是否会导致 OOM。 在增加内存容量之前，请先参阅 高内存占用故障排查。
• 维护过程中产生的临时文件。 索引生成和类似的操作使用 maintenance_work_mem，后者单独管理。

相关内容

• 使用Azure Database for PostgreSQL灵活服务器中的故障排除指南
• 故障排除指南的遥测参考
• 排查内存使用率过高的问题
• 列出Azure Database for PostgreSQL灵活服务器中的所有参数
• Azure Database for PostgreSQL 灵活服务器中的自动优化