监控好临时文件的使用,让数据库运行更高效

数据库 PostgreSQL
由于 work_mem 限制,当 PostgreSQL 后端执行的操作,需要使用比其可以使用的内存更多的内存时,就会创建这些文件。排序操作用于ORDER BY、DISTINCT​和合并连接。

介绍

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

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

责任编辑:武晓燕 来源: 红石PG
相关推荐

2009-08-26 17:11:03

MySQL临时文件

2009-08-10 09:57:05

2017-09-06 14:58:57

Oracle数据文件临时文件

2019-04-19 08:47:00

前端监控数据

2021-04-14 10:25:19

电脑磁盘微软

2021-07-30 07:57:53

Windows 操作系统微软

2009-10-15 15:14:54

VB.NET临时文件

2022-07-01 15:33:53

LinuxBash

2010-10-14 15:29:56

MySQL服务器

2024-03-19 00:10:00

aiofilesPython开发

2010-01-15 17:51:51

VB.NET创建临时文

2009-11-02 10:03:28

VB.NET临时文件

2010-10-18 14:59:05

电子政务平台

2011-08-29 09:33:48

2015-04-02 12:42:26

HDFS分层存储高效

2018-05-08 14:58:07

戴尔

2023-06-08 15:15:45

Windows 10Windows 11微软

2015-09-30 14:22:44

Qlik数据

2024-01-25 08:30:03

图数据库存算分离架构快手

2024-03-19 14:23:37

FakerPython开发
点赞
收藏

51CTO技术栈公众号