使用 PostgreSQL 管理临时文件

在 PostgreSQL 中，执行排序和哈希操作的查询使用实例内存，来存储不超过在 work_mem 参数中指定的值的结果。当实例内存不足时，会创建临时文件来存储结果。这些文件写入磁盘以完成查询执行。稍后，将在查询完成后自动删除这些文件。在 RDS for PostgreSQL 中，这些文件存储在数据卷上的 Amazon EBS 中。有关更多信息，请参阅 Amazon RDS 数据库实例存储。您可以监控 CloudWatch 内发布的 FreeStorageSpace 指标，以确保数据库实例具有足够的可用存储空间。有关更多信息，请参阅 FreeStorageSpace。

对于涉及多个并发查询的工作负载（这会增加临时文件的使用量），我们建议使用 Amazon RDS 优化型读取功能。这些实例使用基于本地非易失性存储规范（NVMe）的固态硬盘（SSD）块级存储来存放临时文件。有关更多信息，请参阅 Amazon RDS 优化型读取功能。

您可以使用下面的参数和函数来管理实例中的临时文件。

temp_file_limit – 此参数取消任何超过 temp_files 大小（以 KB 为单位）的查询。此限制可防止任何查询无休止地运行并使用临时文件消耗磁盘空间。您可以使用来自 log_temp_files 参数的结果来估计该值。作为最佳实践，请检查工作负载行为并根据估计值设置限制。以下示例显示了当查询超过限制时如何取消查询。
```
postgres=> select * from pgbench_accounts, pg_class, big_table;
            
```
```
ERROR: temporary file size exceeds temp_file_limit (64kB)
```

log_temp_files – 当删除会话的临时文件时，此参数会向 postgresql.log 发送消息。此参数在查询成功完成后生成日志。因此，它可能无助于对长时间运行的活跃查询进行故障排除。

以下示例显示，当查询成功完成后，条目将记录在 postgresql.log 文件中，同时清理临时文件。


                    
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT:  select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT:  select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;

pg_ls_tmpdir – 此函数在 RDS for PostgreSQL 13 及更高版本中提供，可让用户了解当前临时文件使用情况。完成的查询不会出现在该函数的结果中。在以下示例中，您可以查看此函数的结果。


postgres=> select * from pg_ls_tmpdir();



      name       |    size    |      modification
-----------------+------------+------------------------
 pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00
 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00
 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00
 pgsql_tmp8351.1 |  703168512 | 2023-02-06 22:54:56+00
 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00
 pgsql_tmp8328.1 |  835031040 | 2023-02-06 22:54:56+00
 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00
(7 rows)


postgres=> select query from pg_stat_activity where pid = 8355;
                
query
----------------------------------------------------------------------------------------
select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid
(1 row)

文件名包括生成了临时文件的会话的处理 ID（PID）。更高级的查询（如以下示例所示）对每个 PID 的临时文件执行总和。


postgres=> select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;



 pid  | count |   sum
------+-------------------
 8355 |     2 | 2144501760
 8351 |     2 | 2090770432
 8327 |     1 | 1072250880
 8328 |     2 | 2144501760
(4 rows)

pg_stat_statements – 如果您激活 pg_stat_sat_statements 参数，则可以查看每个调用的平均临时文件使用量。您可以识别查询的 query_id 并使用它来检查临时文件使用情况，如以下示例所示。


postgres=> select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';



       queryid
----------------------
 -7170349228837045701
(1 row)


postgres=> select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;



       queryid        |          substr           | calls | temp_blks_read_per_call | temp_blks_written_per_call
----------------------+---------------------------+-------+-------------------------+----------------------------
 -7170349228837045701 | select a.aid from pgbench |    50 |                  239226 |                     388678
(1 row)

Performance Insights – 在性能详情控制面板中，可以通过开启指标 temp_bytes 和 temp_files 来查看临时文件使用情况。然后，您可以看到这两个指标的平均值，并查看它们与查询工作负载的对应关系。性能详情中的视图并未专门显示正在生成临时文件的查询。但是，当您将性能详情与针对 pg_ls_tmpdir 显示的查询相结合时，您可以排查、分析并确定查询工作负载的变化。

有关如何使用性能详情分析指标和查询的更多信息，请参阅使用性能详情控制面板分析指标。
使用性能详情查看临时文件使用情况
1. 在性能详情控制面板中，选择管理指标。
2. 选择数据库指标，然后选择 temp_bytes 和 temp_files 指标，如下图所示。
3. 在 Top SQL 选项卡中，选择首选项图标。
4. 在首选项窗口中，打开 Top SQL 选项卡中显示的以下统计数据，然后选择继续。
  - 临时写入次数/秒
  - 临时读取次数/秒
  - 临时批量写入/调用
  - 临时批量读取/调用
5. 当临时文件与针对 pg_ls_tmpdir 显示的查询相组合时，临时文件将被分解，如以下示例所示。

当您的工作负载中排名靠前的查询经常创建临时文件时，就会发生 IO:BufFileRead 和 IO:BufFileWrite 事件。通过查看“数据库负载”和“热门 SQL”部分中的平均活动会话（AAS），您可以使用性能详情来确定在 IO:BufFileRead 和 IO:BufFileWrite 上等待的热门 SQL。

有关如何使用性能详情按等待事件分析热门查询和负载的更多信息，请参阅“Top SQL”（主要 SQL）选项卡概览。您应该识别和调整导致临时文件使用量和相关等待事件增加的查询。有关这些等待事件和补救措施的更多信息，请参阅 IO:BufFileRead 和 IO:BufFileWrite。

注意

work_mem 参数控制排序操作何时耗尽内存以及结果何时写入临时文件中。我们建议您不要将此参数的设置更改为高于原定设置值，因为这将允许每个数据库会话消耗更多内存。此外，执行复杂联接和排序的单个会话可以执行并行操作，其中每个操作都会消耗内存。

作为最佳实践，当您有一个包含多个联接和排序的大型报告时，请使用 SET work_mem 命令在会话级别设置此参数。然后，更改仅应用于当前会话，而不会全局更改该值。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

使用 PostgreSQL autovacuum

使用参数

使用 PostgreSQL 管理临时文件

使用性能详情查看临时文件使用情况

注意