介绍
顾名思义,临时表空间是用于临时对象的。那么问题来了,究竟什么才算是临时对象呢?一个显而易见的对象是临时表。在没有任何临时表空间的情况下,创建临时表时,会在当前数据库的默认表空间中创建临时文件。在一个标准的 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)
原文地址:https://mp.weixin.qq.com/s/ICRvQ5LhMKbavPJX4eZQIQ