介绍
顾名思义,临时表空间是用于临时对象的。那么问题来了,究竟什么才算是临时对象呢?一个显而易见的对象是临时表。在没有任何临时表空间的情况下,创建临时表时,会在当前数据库的默认表空间中创建临时文件。在一个标准的 PostgreSQL 部署环境中,它看起来像这样:
postgres=# l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 872 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7393 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7393 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
所有数据库都有一个名为 “pg_default” 的默认表空间,这是一种伪表空间,因为它实际上并不存在。从系统表查询该表空间的位置时,会显示一个空位置:
SELECT spcname AS "Name"
, pg_catalog.pg_get_userbyid(spcowner) AS "Owner"
, pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
WHERE pg_catalog.pg_tablespace.spcname = 'pg_default'
ORDER BY 1;
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
(1 row)
如果我们创建临时对象,那么文件会创建到哪里?
CREATE TEMPORARY TABLE tmp1 ( a int, b text, c date );
SELECT pg_relation_filepath('tmp1');
pg_relation_filepath
----------------------
base/12732/t3_16436
(1 row)
这是一个 “postgres” 数据库的标准目录:
$ cd $PGDATA
$ oid2name
All databases:
Oid Database Name Tablespace
----------------------------------
12732 postgres pg_default
12731 template0 pg_default
1 template1 pg_default
$ ls -l base/12732/t3_16436
-rw-------. 1 postgres postgres 0 Mar 12 18:17 base/12732/t3_16436
因此,默认情况下,临时表所需的文件,会与组成特定数据库的所有其他文件位于同一位置。当然,如果我们填充临时表,文件会增长:
INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,100) i;
$ ls -la $PGDATA/base/12732/t3_16436
-rw-------. 1 postgres postgres 8192 Mar 12 18:41 /data/pgsql/base/12732/t3_16436
INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,1000) i;
$ ls -la $PGDATA/base/12732/t3_16436
-rw-------. 1 postgres postgres 49152 Mar 12 18:42 /data/pgsql/base/12732/t3_16436
使用默认表空间处理临时对象,会有哪些影响?
1. 临时表的 I/O 会和该 PostgreSQL 实例中所有其他对象的 I/O 竞争。
2. 临时表可能会填满您的文件系统,直到空间满后数据库服务器停止工作。这是创建一个或多个专用的临时表空间的首要原因:通过这样做,只要临时表空间位于其自己的文件系统上,就可以避免临时表疯狂地影响整个实例。
3. 减慢了临时表访问和排序操作。尽管可以增加temp_buffers
和work_mem
参数值,但这些值是会话级别的,过高的值可能会导致内存使用过多,和潜在的内存竞争问题。因此,我们可以选择更快的文件系统或者存储设备,创建单独的临时表空间。
临时表空间
创建临时表空间与创建普通表空间没有什么不同,因为它们实际上完全相同:
$ mkdir /var/tmp/tbstmp
CREATE TABLESPACE tbstmp LOCATION '/var/tmp/tbstmp';
postgres=# db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-----------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 886 MB |
pg_global | postgres | | | | 575 kB |
tbstmp | postgres | /var/tmp/tbstmp | | | 0 bytes |
(3 rows)
一旦我们有了新的表空间,我们就可以告诉 PostgreSQL 将其用作临时对象的默认表空间:
ALTER SYSTEM SET temp_tablespaces = 'tbstmp';
SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
SHOW temp_tablespaces;
temp_tablespaces
------------------
tbstmp
(1 row)
使用临时表
创建另一个临时表,表文件将会创建在新的位置:
CREATE TEMPORARY TABLE tmp2 ( a int, b text, c date );
SELECT pg_relation_filepath('tmp2');
pg_relation_filepath
------------------------------------------------
pg_tblspc/16442/PG_13_202003051/12732/t3_16443
(1 row)
$ ls -la $PGDATA/pg_tblspc/
total 4
drwx------. 2 postgres postgres 19 Mar 12 18:50 .
drwx------. 20 postgres postgres 4096 Mar 12 18:54 ..
lrwxrwxrwx. 1 postgres postgres 15 Mar 12 18:50 16442 -> /var/tmp/tbstmp
$ ls -la $PGDATA/pg_tblspc/16442/
total 0
drwx------. 3 postgres postgres 29 Mar 12 18:50 .
drwxrwxrwt. 7 root root 163 Mar 12 18:49 ..
drwx------. 3 postgres postgres 19 Mar 12 18:53 PG_13_202003051
$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/
total 0
drwx------. 3 postgres postgres 19 Mar 12 18:53 .
drwx------. 3 postgres postgres 29 Mar 12 18:50 ..
drwx------. 2 postgres postgres 54 Mar 12 18:53 12732
$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
total 8
drwx------. 2 postgres postgres 54 Mar 12 18:53 .
drwx------. 3 postgres postgres 19 Mar 12 18:53 ..
-rw-------. 1 postgres postgres 0 Mar 12 18:53 t3_16443
-rw-------. 1 postgres postgres 0 Mar 12 18:53 t3_16446
-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448
如果您想知道为什么会有三个文件,答案在这里:
SELECT relname FROM pg_class WHERE oid IN (16443,16446,16448);
relname
----------------------
pg_toast_16443
pg_toast_16443_index
tmp2
(3 rows)
由于临时表中有一个 “text” 列,因而还会创建 toast 对象。使用不需要 toast 对象的数据类型创建一个临时表,将只生成一个表文件:
CREATE TEMPORARY TABLE tmp3 ( a int, b date );
SELECT pg_relation_filepath('tmp3');
pg_relation_filepath
------------------------------------------------
pg_tblspc/16442/PG_13_202003051/12732/t3_16449
(1 row)
$ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
total 8
drwx------. 2 postgres postgres 70 Mar 12 19:07 .
drwx------. 3 postgres postgres 19 Mar 12 18:53 ..
-rw-------. 1 postgres postgres 0 Mar 12 18:53 t3_16443
-rw-------. 1 postgres postgres 0 Mar 12 18:53 t3_16446
-rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448
-rw-------. 1 postgres postgres 0 Mar 12 19:07 t3_16449
记录临时文件
至此,现在我们知道了,所有临时表都将转到新的临时表空间。从现在开始,还有哪些数据会去那里?有一个参数 log_temp_files 可用于将临时文件的使用情况报告到 PostgreSQL 日志文件中,如果您想知道临时表空间中有什么,这会非常方便。该参数默认设置为 “-1”,表示禁止记录任何内容,设为 “0” 表示记录所有内容,所有其他大于 “1” 的值表示记录超出该大小的临时文件。如前所述,将其设置为 “0” 将会在后台记录创建的所有临时文件,因此让我们这样做:
ALTER SYSTEM SET log_temp_files = 0;
SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
创建另一个临时表,然后检查日志文件,可以确认日志记录生效了,我们可以获得想要的信息:
CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);
日志文件中的记录会如下所示,它表明临时文件已写入到我们上面创建的临时表空间:
2020-03-13 02:33:35.658 [10535] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10535.0", size 14000000
2020-03-13 02:33:35.658 [10535] STATEMENT: CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);
但是,如果您使用 PostgreSQL 的默认配置创建此临时表:
CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,100000);
... 您不会在日志文件中看到任何记录。为什么?因为有一个参数 temp_buffers,而临时文件只有在超过该参数值时,才会出现在日志文件中。在默认配置中,该值为 “8MB”,对于较小的临时表不会记录日志。将参数调小,才会记录较小的临时表文件:
SET temp_buffers = '1024kB';
CREATE TEMPORARY TABLE tmp5 AS SELECT * FROM generate_series(1,100000);
排序
至此,现在我们知道了,如何将临时文件的创建记录到 PostgreSQL 日志文件中。还有哪些其他操作会导致创建临时文件?排序呢?
SET work_mem = '64kB';
SELECT * FROM generate_series(1,1000000) ORDER BY random();
是的,当然会:
2020-03-13 02:47:14.297 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.2", size 26083328
2020-03-13 02:47:14.297 [10609] STATEMENT: SELECT * FROM generate_series(1,1000000) ORDER BY random();
2020-03-13 02:47:14.298 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1", size 14000000
2020-03-13 02:47:14.298 [10609] STATEMENT: SELECT * FROM generate_series(1,1000000) ORDER BY random();
2020-03-13 02:47:14.298 [10609] LOG: duration: 2994.386 ms statement: SELECT * FROM generate_series(1,1000000) ORDER BY random();
创建索引
CREATE TABLE tt1 AS SELECT * FROM generate_series(1,1000000);
CREATE INDEX ii1 ON tt1(generate_series);
是的,这也会创建临时文件:
2020-03-13 02:54:00.933 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.0.sharedfileset/0.0", size 12394496
2020-03-13 02:54:00.933 [10609] STATEMENT: CREATE INDEX ii1 ON tt1(generate_series);
2020-03-13 02:54:00.934 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.0.sharedfileset/1.0", size 7659520
2020-03-13 02:54:00.934 [10609] STATEMENT: CREATE INDEX ii1 ON tt1(generate_series);
2020-03-13 02:54:00.948 [10609] LOG: duration: 1149.625 ms statement: CREATE INDEX ii1 ON tt1(generate_series);
添加外键
CREATE TABLE ttt1 AS SELECT * FROM generate_series(1,1000000) a;
CREATE UNIQUE INDEX iii1 ON ttt1(a);
INSERT INTO ttt2 SELECT a,a FROM generate_series(1,1000000) a;
ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);
是的,那也会:
2020-03-13 03:01:07.127 [10609] LOG: duration: 1127.768 ms statement: ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);
2020-03-13 03:01:15.375 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.6", size 67374
因此,相当多的操作会在后台生成临时文件。将其分离到单独的挂载目录实际上很有意义。从性能角度看(如果 I/O 也分布在存储层上),也可从安全角度来看,这样需要临时文件的大型操作不会影响到实例上的“正常”操作。
创建物化视图
还有另一种情况会生成临时文件,但可能不是每个人都清楚。考虑下这样的操作:
CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
这也会在后台创建许多临时文件:
...
2020-03-13 03:11:03.721 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1289", size 22704
2020-03-13 03:11:03.721 [10609] STATEMENT: CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.722 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.143", size 23136
2020-03-13 03:11:03.722 [10609] STATEMENT: CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.723 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.1166", size 23136
2020-03-13 03:11:03.723 [10609] STATEMENT: CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.b;
2020-03-13 03:11:03.723 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.792", size 23640
...
刷新物化视图
甚至刷新时也会产生临时文件:
CREATE UNIQUE INDEX mv_i1 ON mv1(a);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
看下日志文件:
...
2020-03-13 03:14:05.866 [10609] STATEMENT: REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
2020-03-13 03:14:05.866 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.8193", size 26768
2020-03-13 03:14:05.866 [10609] CONTEXT: SQL statement "CREATE TEMP TABLE pg_temp_3.pg_temp_16513_2 AS SELECT mv.ctid AS tid, newdata FROM public.mv1 mv FULL JOIN pg_temp_3.pg_temp_16513 newdata ON (newdata.a OPERATOR(pg_catalog.=) mv.a AND newdata OPERATOR(pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid"
2020-03-13 03:14:05.866 [10609] STATEMENT: REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
2020-03-13 03:14:05.866 [10609] LOG: temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10609.6147", size 28487
2020-03-13 03:14:05.866 [10609] CONTEXT: SQL statement "CREATE TEMP TABLE pg_temp_3.pg_temp_16513_2 AS SELECT mv.ctid AS tid, newdata FROM public.mv1 mv FULL JOIN pg_temp_3.pg_temp_16513 newdata ON (newdata.a OPERATOR(pg_catalog.=) mv.a AND newdata OPERATOR(pg_catalog.*=) mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid"
...
还有更多操作需要在后台使用临时文件,但对于本文的范围,我们到此为止。最后补充下:生成的临时文件数量还可能受到 temp_file_limit 的限制:
SET temp_file_limit = '1MB';
REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
ERROR: temporary file size exceeds temp_file_limit (1024kB)