重庆思庄Oracle、Redhat认证学习论坛

标题: Pg 临时表空间 怎么用 [打印本页]
作者: 郑全    时间: 2024-12-4 12:55
标题: Pg 临时表空间 怎么用
本帖最后由 郑全 于 2024-12-4 13:15 编辑

介绍
    顾名思义，临时表空间是用于临时对象的。
    那么问题来了，究竟什么才算是临时对象呢？一个显而易见的对象是临时表。
     在没有任何临时表空间的情况下，创建临时表时，会在当前数据库的默认表空间中创建临时文件。
     在一个标准的 PostgreSQL 部署环境中，它看起来像这样：
     

  2.    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
  3. -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
  4. postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 872 MB  | pg_default | default administrative connection database
  5. template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | unmodifiable empty database
  6.            |          |          |             |             | postgres=CTc/postgres |         |            |
  7. template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7393 kB | pg_default | default template for new databases
  8.            |          |          |             |             | postgres=CTc/postgres |         |            |
复制代码

    所有数据库都有一个名为 “pg_default” 的默认表空间，这是一种伪表空间，因为它实际上并不存在。从系统表查询该表空间的位置时，会显示一个空位置：

        
  1. SELECT spcname AS "Name"
  2.      , pg_catalog.pg_get_userbyid(spcowner) AS "Owner"
  3.      , pg_catalog.pg_tablespace_location(oid) AS "Location"
  4.   FROM pg_catalog.pg_tablespace
  5. WHERE pg_catalog.pg_tablespace.spcname = 'pg_default'
  6. ORDER BY 1;

  8.     Name    |  Owner   | Location
  9. ------------+----------+----------
  10. pg_default | postgres |
  11. (1 row)
复制代码

如果我们创建临时对象，那么文件会创建到哪里？

  1. CREATE TEMPORARY TABLE tmp1 ( a int, b text, c date );

  3. SELECT pg_relation_filepath('tmp1');
  4. pg_relation_filepath
  5. ----------------------
  6. base/12732/t3_16436
  7. (1 row)
复制代码

这是一个 “postgres” 数据库的标准目录：

  1. $ cd $PGDATA

  3. $ oid2name
  4. All databases:
  5.     Oid  Database Name  Tablespace
  6. ----------------------------------
  7.   12732       postgres  pg_default
  8.   12731      template0  pg_default
  9.       1      template1  pg_default

  11. $ ls -l base/12732/t3_16436
  12. -rw-------. 1 postgres postgres 0 Mar 12 18:17 base/12732/t3_16436
复制代码

因此，默认情况下，临时表所需的文件，会与组成特定数据库的所有其他文件位于同一位置。当然，如果我们填充临时表，文件会增长：

  1. INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,100) i;
复制代码

  1. $ ls -la $PGDATA/base/12732/t3_16436
  2. -rw-------. 1 postgres postgres 8192 Mar 12 18:41 /data/pgsql/base/12732/t3_16436
复制代码


  1. INSERT INTO tmp1 (a,b,c) SELECT i, i::text, now() FROM generate_series(1,1000) i;
复制代码
  1. $ ls -la $PGDATA/base/12732/t3_16436
  2. -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参数值，但这些值是会话级别的，过高的值可能会导致内存使用过多，和潜在的内存竞争问题。因此，我们可以选择更快的文件系统或者存储设备，创建单独的临时表空间。
临时表空间
创建临时表空间与创建普通表空间没有什么不同，因为它们实际上完全相同：

  1. $ mkdir /var/tmp/tbstmp
复制代码
  1. CREATE TABLESPACE tbstmp LOCATION '/var/tmp/tbstmp';
复制代码

  1. postgres=# db+
  2.                                       List of tablespaces
  3.     Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description
  4. ------------+----------+-----------------+-------------------+---------+---------+-------------
  5. pg_default | postgres |                 |                   |         | 886 MB  |
  6. pg_global  | postgres |                 |                   |         | 575 kB  |
  7. tbstmp     | postgres | /var/tmp/tbstmp |                   |         | 0 bytes |
  8. (3 rows)
复制代码

一旦我们有了新的表空间，我们就可以告诉 PostgreSQL 将其用作临时对象的默认表空间：
  1. ALTER SYSTEM SET temp_tablespaces = 'tbstmp';

  3. SELECT pg_reload_conf();
  4. pg_reload_conf
  5. ----------------
  6. t
  7. (1 row)

  9. SHOW temp_tablespaces;
  10. temp_tablespaces
  11. ------------------
  12. tbstmp
  13. (1 row)
复制代码

使用临时表

创建另一个临时表，表文件将会创建在新的位置：
  1. CREATE TEMPORARY TABLE tmp2 ( a int, b text, c date );

  3. SELECT pg_relation_filepath('tmp2');
  4.               pg_relation_filepath
  5. ------------------------------------------------
  6. pg_tblspc/16442/PG_13_202003051/12732/t3_16443
  7. (1 row)
复制代码


  1. $ ls -la $PGDATA/pg_tblspc/
  2. total 4
  3. drwx------.  2 postgres postgres   19 Mar 12 18:50 .
  4. drwx------. 20 postgres postgres 4096 Mar 12 18:54 ..
  5. lrwxrwxrwx.  1 postgres postgres   15 Mar 12 18:50 16442 -> /var/tmp/tbstmp

  7. $ ls -la $PGDATA/pg_tblspc/16442/
  8. total 0
  9. drwx------. 3 postgres postgres  29 Mar 12 18:50 .
  10. drwxrwxrwt. 7 root     root     163 Mar 12 18:49 ..
  11. drwx------. 3 postgres postgres  19 Mar 12 18:53 PG_13_202003051

  13. $ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/
  14. total 0
  15. drwx------. 3 postgres postgres 19 Mar 12 18:53 .
  16. drwx------. 3 postgres postgres 29 Mar 12 18:50 ..
  17. drwx------. 2 postgres postgres 54 Mar 12 18:53 12732

  19. $ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
  20. total 8
  21. drwx------. 2 postgres postgres   54 Mar 12 18:53 .
  22. drwx------. 3 postgres postgres   19 Mar 12 18:53 ..
  23. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443
  24. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446
  25. -rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448
复制代码

如果您想知道为什么会有三个文件，答案在这里：
  1. SELECT relname FROM pg_class WHERE oid IN (16443,16446,16448);

  3.        relname
  4. ----------------------
  5. pg_toast_16443
  6. pg_toast_16443_index
  7. tmp2
  8. (3 rows)
复制代码


由于临时表中有一个 “text” 列，因而还会创建 toast 对象。使用不需要 toast 对象的数据类型创建一个临时表，将只生成一个表文件：
  1. CREATE TEMPORARY TABLE tmp3 ( a int, b date );

  3. SELECT pg_relation_filepath('tmp3');
  4.               pg_relation_filepath
  5. ------------------------------------------------
  6. pg_tblspc/16442/PG_13_202003051/12732/t3_16449
  7. (1 row)
复制代码


  1. $ ls -la $PGDATA/pg_tblspc/16442/PG_13_202003051/12732/
  2. total 8
  3. drwx------. 2 postgres postgres   70 Mar 12 19:07 .
  4. drwx------. 3 postgres postgres   19 Mar 12 18:53 ..
  5. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16443
  6. -rw-------. 1 postgres postgres    0 Mar 12 18:53 t3_16446
  7. -rw-------. 1 postgres postgres 8192 Mar 12 18:53 t3_16448
  8. -rw-------. 1 postgres postgres    0 Mar 12 19:07 t3_16449
复制代码

记录临时文件
至此，现在我们知道了，所有临时表都将转到新的临时表空间。从现在开始，还有哪些数据会去那里？
有一个参数 log_temp_files 可用于将临时文件的使用情况报告到 PostgreSQL 日志文件中，如果您想知道临时表空间中有什么，
这会非常方便。该参数默认设置为 “-1”，表示禁止记录任何内容，设为 “0” 表示记录所有内容，所有其他大于 “1” 的值表示记录超出该大小的临时文件。
如前所述，将其设置为 “0” 将会在后台记录创建的所有临时文件，因此让我们这样做：

  1. ALTER SYSTEM SET log_temp_files = 0;

  3. SELECT pg_reload_conf();
  4. pg_reload_conf
  5. ----------------
  6. t
  7. (1 row)
复制代码

创建另一个临时表，然后检查日志文件，可以确认日志记录生效了，我们可以获得想要的信息：
  1. CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);
复制代码

日志文件中的记录会如下所示，它表明临时文件已写入到我们上面创建的临时表空间：
  1. 020-03-13 02:33:35.658 [10535] LOG:  temporary file: path "pg_tblspc/16442/PG_13_202003051/pgsql_tmp/pgsql_tmp10535.0", size 14000000
  2. 2020-03-13 02:33:35.658 [10535] STATEMENT:  CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,1000000);
复制代码

但是，如果您使用 PostgreSQL 的默认配置创建此临时表：
  1. CREATE TEMPORARY TABLE tmp4 AS SELECT * FROM generate_series(1,100000);
复制代码

... 您不会在日志文件中看到任何记录。为什么？因为有一个参数 temp_buffers，而临时文件只有在超过该参数值时，才会出现在日志文件中。
在默认配置中，该值为 “8MB”，对于较小的临时表不会记录日志。
将参数调小，才会记录较小的临时表文件：
  1. SET temp_buffers = '1024kB';

  3. CREATE TEMPORARY TABLE tmp5 AS SELECT * FROM generate_series(1,100000);
复制代码

排序
至此，现在我们知道了，如何将临时文件的创建记录到 PostgreSQL 日志文件中。还有哪些其他操作会导致创建临时文件？排序呢？
  1. SET work_mem = '64kB';

  3. SELECT * FROM generate_series(1,1000000) ORDER BY random();
复制代码

是的，当然会：
  1. 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
  2. 2020-03-13 02:47:14.297 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
  3. 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
  4. 2020-03-13 02:47:14.298 [10609] STATEMENT:  SELECT * FROM generate_series(1,1000000) ORDER BY random();
  5. 2020-03-13 02:47:14.298 [10609] LOG:  duration: 2994.386 ms  statement: SELECT * FROM generate_series(1,1000000) ORDER BY random();
复制代码

创建索引
  1. CREATE TABLE tt1 AS SELECT * FROM generate_series(1,1000000);

  3. CREATE INDEX ii1 ON tt1(generate_series);
复制代码


是的，这也会创建临时文件：

  1. 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
  2. 2020-03-13 02:54:00.933 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
  3. 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
  4. 2020-03-13 02:54:00.934 [10609] STATEMENT:  CREATE INDEX ii1 ON tt1(generate_series);
  5. 2020-03-13 02:54:00.948 [10609] LOG:  duration: 1149.625 ms  statement: CREATE INDEX ii1 ON tt1(generate_series);
复制代码


添加外键


  1. CREATE TABLE ttt1 AS SELECT * FROM generate_series(1,1000000) a;

  3. CREATE UNIQUE INDEX iii1 ON ttt1(a);

  5. INSERT INTO ttt2 SELECT a,a FROM generate_series(1,1000000) a;

  7. ALTER TABLE ttt2 ADD CONSTRAINT fk_t FOREIGN KEY (b) REFERENCES ttt1(a);
复制代码


是的，那也会：
  1. 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);
  2. 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 也分布在存储层上），也可从安全角度来看，这样需要临时文件的大型操作不会影响到实例上的“正常”操作。

创建物化视图
还有另一种情况会生成临时文件，但可能不是每个人都清楚。考虑下这样的操作：
  1. CREATE MATERIALIZED VIEW mv1 AS SELECT ttt1.a, ttt2.b FROM ttt1, ttt2 WHERE ttt1.a = ttt2.
复制代码

这也会在后台创建许多临时文件：
  1. ...
  2. 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
  3. 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;
  4. 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
  5. 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;
  6. 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
  7. 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;
  8. 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
  9. ...
复制代码

刷新物化视图
甚至刷新时也会产生临时文件：
  1. CREATE UNIQUE INDEX mv_i1 ON mv1(a);

  3. REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
复制代码

看下日志文件：
  1. ...
  2. 2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
  3. 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
  4. 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"
  5. 2020-03-13 03:14:05.866 [10609] STATEMENT:  REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
  6. 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
  7. 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"
  8. ...
复制代码

还有更多操作需要在后台使用临时文件，但对于本文的范围，我们到此为止。
最后补充下：生成的临时文件数量还可能受到 temp_file_limit 的限制：
  1. SET temp_file_limit = '1MB';

  3. REFRESH MATERIALIZED VIEW CONCURRENTLY mv1;
  4. ERROR:  temporary file size exceeds temp_file_limit (1024kB)
复制代码







欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2