优化技法：使用In-memory Disk代替PostgreSQL中的临时文件

1.前言

下边的内容来自技术博客，感觉内容真心不错。完整的介绍到这里，与大家共享。

最近，在调试一个CREATE INDEX操作的性能问题时，我被提醒，PostgreSQL在执行并行查询(包括并行索引创建)时可能会产生临时文件，因为每个工作进程都有自己的内存，可能需要使用磁盘空间来排序或散列表。

所以，为了尝试加快索引创建，我认为使用tmpfs将这些临时文件直接移动到内存中是有益的，并想测试这个理论，写这篇博客文章:-)

2.实例与分析

2.1实际栗子

让我们首先启用临时文件的日志记录，以评估我们计划进行的更改:

ALTER SYSTEM SET log_temp_files TO 0;
ALTER SYSTEM SET log_min_duration_statement TO 0;
SELECT pg_reload_conf();

使用pgbench创建一个测试数据库，并在pgbench_accounts表上创建一个索引。

$ createdb bench
$ /usr/pgsql-16/bin/pgbench -i -s 100 bench
$ psql bench -c "CREATE INDEX ON pgbench_accounts (aid, filler);"

我们可以从日志中看到构建包含临时文件的索引所花费的时间(~5.9s):

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp28501.0.fileset/0.0", size 541376512
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp28501.0.fileset/1.0", size 541024256
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  duration: 5936.468 ms  statement: CREATE INDEX ON pgbench_accounts (aid, filler);

让我们尝试用更高的maintenance_work_mem重新创建索引，以摆脱临时文件:

DROP INDEX pgbench_accounts_aid_filler_idx;
SET maintenance_work_mem TO '2GB';
CREATE INDEX ON pgbench_accounts (aid, filler);

但是临时文件并没有消失，正如我们在上面提到的pgsql-admin邮件里的注释中所期望的那样。

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp28501.10.fileset/0.0", size 365936640
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp28501.10.fileset/2.0", size 354754560
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp28501.10.fileset/1.0", size 361439232
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  duration: 4541.701 ms  statement: CREATE INDEX ON pgbench_accounts (aid, filler);

因此，让我们禁用并行查询执行来检查:

DROP INDEX pgbench_accounts_aid_filler_idx;
SET maintenance_work_mem TO '2GB';
SET max_parallel_workers TO 0;
CREATE INDEX ON pgbench_accounts (aid, filler);

瞧! 临时文件这下消失了:

LOG:  duration: 4348.098 ms  statement: CREATE INDEX ON pgbench_accounts (aid, filler);

我们得到了大致相同的创建时间——4.5s vs 4.3s。但是我们怎样才能同时使用内存和并行执行呢? 使用tmpfs目录将这些临时文件移动到内存中!

2.2配置PostgreSQL使用tmpfs目录

要将临时文件移动到内存中，我们需要使用temp_tablespaces设置，这需要创建一个表空间。因此，我们将首先创建该表空间，然后将其移动到tmpfs位置。

首先为表空间创建一个根目录:

sudo mkdir /var/pgsql_tmp

现在，我们必须为这些PostgreSQL临时文件创建一个表空间:

CREATE TABLESPACE tbstmp location '/var/pgsql_tmp';

PostgreSQL将在/var/pgsql_tmp中创建一个子目录，如果我们不想每次重启后都重新创建表空间，我们需要将该子目录永久保存:

$ ls /var/pgsql_tmp/
PG_16_202307071

最后，要使tmpfs持久挂载，将其添加到/etc/fstab中:

tmpfs   /var/pgsql_tmp/PG_16_202307071  tmpfs   rw,size=2G,uid=postgres,gid=postgres    0 0

在本例中，size=2G配置tmpfs实例使用最多2GB的RAM。

配置/etc/fstab后，重新加载systemd并挂载tmpfs实例:

sudo systemctl daemon-reload
sudo mount /var/pgsql_tmp/PG_16_202307071

回到我们最初的例子，现在让我们尝试设置temp_tablespaces并再次创建索引:

DROP INDEX pgbench_accounts_aid_filler_idx;
SET maintenance_work_mem TO '2GB';
RESET max_parallel_workers;
SET temp_tablespaces TO 'tbstmp';
CREATE INDEX ON pgbench_accounts (aid, filler);

这些临时文件使用内存中的TMPFS目录，这确实加快了索引创建的速度(到3.9秒):

LOG:  temporary file: path "pg_tblspc/16448/PG_16_202307071/pgsql_tmp/pgsql_tmp28501.11.fileset/1.0", size 361865216
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  temporary file: path "pg_tblspc/16448/PG_16_202307071/pgsql_tmp/pgsql_tmp28501.11.fileset/0.0", size 364134400
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  temporary file: path "pg_tblspc/16448/PG_16_202307071/pgsql_tmp/pgsql_tmp28501.11.fileset/2.0", size 356122624
STATEMENT:  CREATE INDEX ON pgbench_accounts (aid, filler);
LOG:  duration: 3977.606 ms  statement: CREATE INDEX ON pgbench_accounts (aid, filler);

要使所有临时文件永久保存，请在系统范围内更改temp_tablespaces设置:

ALTER SYSTEM SET temp_tablespaces TO 'tbstmp';

最后，重新加载PostgreSQL配置以使更改生效:

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# SELECT * FROM pg_settings WHERE name = 'temp_tablespaces';
-[ RECORD 1 ]---+-------------------------------------------------------------------
name            | temp_tablespaces
setting         | tbstmp
unit            |
category        | Client Connection Defaults / Statement Behavior
short_desc      | Sets the tablespace(s) to use for temporary tables and sort files.
extra_desc      |
context         | user
vartype         | string
source          | session
min_val         |
max_val         |
enumvals        |
boot_val        |
reset_val       | "tbstmp"
sourcefile      |
sourceline      |
pending_restart | f

3.总结

通过以下步骤，您可以在内存中为PostgreSQL临时文件配置一个tmpfs目录，从而可能提高某些操作的性能!显然，您需要根据系统的容量和需求调整tmpfs的大小……

PGer们，你觉得这个小招怎么样?

4.原文参考：

https://pgstef.github.io/2024/05/20/in_memory_tmp_files.html