介绍
如您所知,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();
一旦你发现临时文件的使用率非常高,你就可以找到那些使用到临时文件的查询,并优化它们。