监控好临时文件的使用，让数据库运行更高效-51CTO.COM

介绍

如您所知，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();

一旦你发现临时文件的使用率非常高，你就可以找到那些使用到临时文件的查询，并优化它们。