【PGCCC】PostgreSQL 临时文件的使用

临时文件

某些查询操作（例如sort或hash表）需要一些内存功能。此内存由运行时配置提供work_mem。
来自官方文档work_mem

work_mem (整数)

设置在写入临时磁盘文件之前查询操作（例如排序或哈希表）使用的基本最大内存量。
请注意，对于复杂查询，可能会并行运行多个排序或哈希操作；在开始将数据写入临时文件之前，通常允许每个操作使用此值指定的内存量。排序操作用于
ORDER BY、DISTINCT 和合并连接。哈希表用于哈希连接、基于哈希的聚合、结果缓存节点和基于哈希的 IN 子查询处理。

一旦超过设置值work_mem，操作就会开始写入临时磁盘文件。

另一个可以写入临时文件的操作是CREATE INDEX。这由不同的运行时配置控制maintenance_work_mem。
来自官方文档maintenance_work_mem

Maintenance_work_mem（整数）

指定维护操作（例如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）使用的最大内存量。

这些临时磁盘文件的具体写入位置由运行时配置控制，temp_tablespaces
同样来自官方文档temp_tablespaces

temp_tablespaces (字符串)

当 CREATE 命令未明确指定表空间时，此变量指定在其中创建临时对象（临时表和临时表上的索引）的表空间。这些表空间中还会创建用于排序大型数据集等目的的临时文件。
该值是表空间名称的列表。当列表中有多个名称时，PostgreSQL每次要创建临时对象时都会从列表中随机选择一个成员；但在事务中，连续创建的临时对象将放置在列表中连续的表空间中。如果列表中的选定元素是空字符串，PostgreSQL 将自动使用当前数据库的默认表空间。
当以temp_tablespaces交互方式设置时，指定不存在的表空间是错误的，指定用户没有 CREATE 权限的表空间也是如此。但是，当使用以前设置的值时，**不存在的表空间将被忽略，用户缺少 CREATE 权限的表空间也将被忽略。**具体来说，此规则适用于使用中设置的值的情况postgresql.conf。
默认值为空字符串，这会导致所有临时对象都在当前数据库的默认表空间中创建。

默认表空间与特定临时表空间

ostgreSQL 的默认安装将默认表空间 ( pg_default ) 设置为temp_tablespaces。
您可能希望将实际数据的写入与临时文件分开，以改善磁盘 IO。这是temp_tablespaces最有用的地方。
此外，您可以将您的指向temp_tablespaces更快的磁盘，例如速度更快的 nvme。

为临时文件设置表空间

您可以使用以下示例代码来设置 temp_tablespaces

创建新的表空间：

CREATE TABLESPACE temp LOCATION 'actual_location';

设置集群范围temp_tablespaces

ALTER SYSTEM SET temp_tablespaces = 'temp';
SELECT pg_reload_conf();

临时文件的注意事项

当查询需要创建大量临时文件时，临时文件可能会耗尽可用的磁盘空间，PostgreSQL 会报告错误，例如 could not write block … of temporary file no space left on device …查询被取消。
临时文件仅在查询期间保留。查询完成或取消后，临时文件将被清除。DBA 偶尔会遇到(1)，当他们检查磁盘使用情况时，他们会发现仍有空间，并开始排除故障以找出问题所在。该问题在 (2)中描述。发生这种情况时，可能是时候优化查询了。另一种解决方法是使用更大的可用磁盘作为temp_tablespaces.

监控临时文件的使用情况

我们可以借助运行时配置log_temp_files来记录临时文件

log_temp_files (integer)
控制临时文件名和大小的日志记录。可以为排序、哈希和临时查询结果创建临时文件。如果通过此设置启用，则在删除每个临时文件时都会发出一个日志条目。零值记录所有临时文件信息，而正值仅记录大小大于或等于指定数据量的文件。如果指定此值时没有单位，则以千字节为单位。默认设置为 -1，禁用此类日志记录。只有超级用户可以更改此设置。

虽然log_temp_files在删除临时文件时会记录有关临时文件的信息，但您可能还想在创建临时文件时对其进行监控，密切关注。
此外，您可能想知道哪些查询正在使用临时文件，并可能优化它们以加快速度work_mem。

我们将通过 SQL 执行此操作。
查询：

WITH tablespaces AS (SELECTspcname AS tbl_name,coalesce(nullif(pg_tablespace_location(oid), ''), (current_setting('data_directory') || '/base')) AS tbl_locationFROM pg_tablespace
),
tablespace_suffix AS (SELECTtbl_name,tbl_location || '/pgsql_tmp' AS pathFROM tablespacesWHERE tbl_name = 'pg_default'UNION ALLSELECTtbl_name,tbl_location || '/' || path || '/pgsql_tmp'FROM tablespaces, LATERAL pg_ls_dir(tbl_location) AS pathWHERE path ~ ('PG_' || substring(current_setting('server_version') FROM '^(?:\d\.\d\d?|\d+)'))
),
stat AS (SELECTsubstring(file from '\d+\d') AS pid,tbl_name AS temp_tablespace,pg_size_pretty(sum(pg_size_bytes(size))) AS sizeFROM tablespace_suffix, LATERAL pg_ls_dir(path, true, false) AS file,LATERAL pg_size_pretty((pg_stat_file(path || '/' || file, true)).size) AS sizeGROUP BY pid, temp_tablespace
)
SELECTa.datname,a.pid,coalesce(size, '0 MB') AS temp_size_written,coalesce(temp_tablespace, 'not using temp files') AS temp_tablespace,a.application_name,a.client_addr,a.usename,(clock_timestamp() - a.query_start)::interval(0) AS duration,(clock_timestamp() - a.state_change)::interval(0) AS duration_since_state_change,trim(trailing ';' FROM left(query, 1000)) AS query,a.state,a.wait_event_type || ':' || a.wait_event AS wait
FROM pg_stat_activity AS a
LEFT JOIN stat ON a.pid = stat.pid::int
WHERE a.pid != pg_backend_pid()
ORDER BY temp_size_written DESC;

示例输出：

datname | pid  | temp_size_written |   temp_tablespace    | application_name | client_addr | usename | duration | duration_since_state_change |                        query                        | state  |             wait
---------+------+-------------------+----------------------+------------------+-------------+---------+----------+-----------------------------+-----------------------------------------------------+--------+------------------------------bolaji  | 4943 | 1911 MB           | temp                 | bolaji-psql      | <null>      | bolaji  | 00:00:51 | 00:00:51                    | CREATE INDEX CONCURRENTLY ON folder (id)            | active | <null>bolaji  | 7518 | 1338 MB           | pg_default           | bolaji-psql      | <null>      | bolaji  | 00:00:48 | 00:00:48                    | select generate_series(1,100000000) as a order by a | active | <null><null>  | 3819 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:AutoVacuumMain<null>  | 3818 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:WalWriterMain<null>  | 3816 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:CheckpointerMain<null>  | 3821 | 0 MB              | not using temp files |                  | <null>      | bolaji  | <null>   | <null>                      |                                                     | <null> | Activity:LogicalLauncherMainbolaji  | 5935 | 0 MB              | not using temp files | bolaji-psql      | <null>      | bolaji  | 00:31:39 | 00:31:39                    | show work_mem                                       | idle   | Client:ClientRead<null>  | 3817 | 0 MB              | not using temp files |                  | <null>      | <null>  | <null>   | <null>                      |                                                     | <null> | Activity:BgWriterHibernate
(8 rows)Time: 2.960 ms