51工具盒子
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
