七月 31, 2024
摘要：在本教程中，您将学习如何检查 PostgreSQL 中临时文件的使用情况。
目录
介绍
如您所知，PostgreSQL 中的某些操作（例如排序或哈希表）会创建临时文件。由于 work_mem 限制，当 PostgreSQL 后端执行的操作，需要使用比其可以使用的内存更多的内存时，就会创建这些文件。排序操作用于ORDER BY、DISTINCT和合并连接。哈希表用于哈希连接、基于哈希的聚合、结果缓存节点和对IN子查询的基于哈希的处理。
示例
首先，我们可以将 work_mem 设置为一个较小的值，例如 1MB：
SET work_mem = 1MB;
让我们做一些适合这个内存量（1MB）的事情，它不会使用临时文件：
EXPLAIN analyze SELECT COUNT(*) FROM (SELECT random() AS i FROM generate_series(1,1000) ORDER BY i) AS x;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------─
Aggregate (cost=77.33..77.34 rows=1 width=0) (actual time=1.308..1.308 rows=1 loops=1)
-> Sort (cost=62.33..64.83 rows=1000 width=0) (actual time=1.099..1.244 rows=1000 loops=1)
Sort Key: (random())
Sort Method: quicksort Memory: 71kB
-> Function Scan ON generate_series (cost=0.00..12.50 rows=1000 width=0) (actual time=0.184..0.380 rows=1000 loops=1)
Total runtime: 1.449 ms
(6 rows)
如您所见，排序仅使用了内存，但是当我们执行要求更高的操作时，它发生了变化：
EXPLAIN analyze SELECT COUNT(*) FROM (SELECT random() AS i FROM generate_series(1,20000) ORDER BY i) AS x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------─
Aggregate (cost=77.33..77.34 rows=1 width=0) (actual time=31.668..31.669 rows=1 loops=1)
-> Sort (cost=62.33..64.83 rows=1000 width=0) (actual time=27.529..30.368 rows=20000 loops=1)
Sort Key: (random())
Sort Method: external merge Disk: 352kB
-> Function Scan ON generate_series (cost=0.00..12.50 rows=1000 width=0) (actual time=4.822..10.117 rows=20000 loops=1)
Total runtime: 32.248 ms
(6 rows)
让我们来检查一下 PostgreSQL 日志：
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp22791.1", size 360448
STATEMENT: EXPLAIN analyze SELECT COUNT(*) FROM (SELECT random() AS i FROM generate_series(1,20000) ORDER BY i) AS x;
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp22791.0", size 280000
STATEMENT: EXPLAIN analyze SELECT COUNT(*) FROM (SELECT random() AS i FROM generate_series(1,20000) ORDER BY i) AS x;
LOG: duration: 32.506 ms statement: EXPLAIN analyze SELECT COUNT(*) FROM (SELECT random() AS i FROM generate_series(1,20000) ORDER BY i) AS x;
如上所示，explain analyze 仅显示了一部分的临时文件使用情况，这里的实际情况是，PostgreSQL 首先使用了 352kB 的临时文件，然后将其重写为 280kB 的文件，但它同时删除了 352 kB 的文件。所以，explain 显示的是峰值使用量，而不是总使用量。
查询 pg_stat_database 视图
您可以在视图pg_stat_database中看到数据库级别的统计信息，其中有temp_files和temp_bytes。这两列非常重要，因为它们会告诉您数据库是否必须向磁盘写入临时文件，这将不可避免地减慢操作速度。临时文件使用率高的原因可能有哪些？主要原因如下：
- 设置不当：如果您的 work_mem 设置太低，则无法在内存中执行任何操作，因此 PostgreSQL 将转到磁盘进行处理。
- 粗心的操作：人们经常用相当低效且毫无意义的查询，来折磨他们的系统。如果在 OLTP 系统上看到许多临时文件，请考虑检查那些执行低效的查询。
- 索引和其他管理任务：有时，可能会创建索引或运行 DDL。这些操作可能会导致发生临时文件的 I/O，但它们也不一定就是问题（在许多情况下）。
简而言之，即使您的系统完全正常，也可能会出现临时文件。不过，密切关注它们，并确保不会经常用到临时文件，绝对是有意义的。我们可以使用下面的查询，来检查临时文件的使用情况：
SELECT temp_files, temp_bytes FROM pg_stat_database
WHERE datname = current_database();
一旦你发现临时文件的使用率非常高，你就可以找到那些使用到临时文件的查询，并优化它们。