ReaderMX.
PostgreSQL学徒

临时表使用不规范，DBA哭死在厕所

Pxiongcc约 7 分钟

前言

在昨天的文章中，介绍了 Greenplum 中独特的临时表实现，今天在分析临时表的时候，又遇到了一些隐藏十分深的性能问题，在此也多谢家琪同志的帮助。

简而言之，如果在业务中使用了大量的 ON COMMIT DELETE ROWS 的临时表的话，使用不规范的话，那么你可能会面临严重的性能衰减以及锁冲突。

前因后果

临时表的好处不言而喻：

  1. 常规普通表的读写链路会有较多的常规锁保护，因为位于共享内存中，而临时表的生命周期是会话级，local buffer，不涉及到共享，因此也不需要锁参与，自然也不需要 invalid-message 发送给其他后端进程维护 syscache/relcache 的一致性。
  2. 其次便是不需要写 WAL，写入速度自然杠杠的。

但是彼之蜜糖，吾之砒霜，有好处自然也有危害。首当其冲的当然是系统表膨胀了，pg_atrribute、pg_class 的膨胀很容易理解，还有一个鲜为人知的是 pg_namespace，看个栗子：

postgres=# create database mydb;
CREATE DATABASE
postgres=# \c mydb 
You are now connected to database "mydb" as user "postgres".
mydb=# create temp table t1(id int) on commit drop;
CREATE TABLE
mydb=# \d t1
Did not find any relation named "t1".
mydb=# select * from pg_namespace ;
  oid   |      nspname       | nspowner |                            nspacl                             
--------+--------------------+----------+---------------------------------------------------------------
     99 | pg_toast           |       10 | 
     11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
   2200 | public             |     6171 | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}
  13324 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 158882 | pg_temp_4          |       10 | 
 158883 | pg_toast_temp_4    |       10 | 
(6 rows)

为了清晰可见，我新建了一个库，可以看到创建的模式是会伴随数据库的，比如此例的 pg_temp_4 和 pg_toast_temp_4 (临时表的TOAST)，这一点主要是基于一个会话内可能会创建多个临时表。

关于临时表的使用，有多种方式：

  1. ON COMMIT PRESERVE ROWS：表示临时表的数据在事务结束后保留，默认值
  2. ON COMMIT DELETE ROWS：表示临时表的数据在事务结束后删掉
  3. ON COMMIT DROP：表示临时表在事务结束后删除

那么问题出在了哪里？首先自然是 temp_buffers，需要遍历，清空所有的 BUFFER，那么 temp_buffers 越大，其性能自然就越低，让我们看一组数字：

[postgres@mypg ~]$ cat run.sql 
SET synchronous_commit TO off;
BEGIN;
CREATE TEMP TABLE if not exists x(id int);
INSERT INTO x VALUES (1);
truncate TABLE x;
COMMIT;
[postgres@mypg ~]$ ./test1.sh | grep tps
tps for 8 MB
tps = 832.937970 (without initial connection time)
tps for 32 MB
tps = 792.021925 (without initial connection time)
tps for 128 MB
tps = 711.783284 (without initial connection time)
tps for 256 MB
tps = 595.466552 (without initial connection time)
tps for 512 MB
tps = 443.828044 (without initial connection time)
tps for 1 GB
tps = 302.952138 (without initial connection time)
tps for 8 GB
tps = 55.827146 (without initial connection time)

十分明显。让我们再看一段代码，控制在事务提交之前需要做的一些前置动作：

 foreach(l, on_commits)
 {
  OnCommitItem *oc = (OnCommitItem *) lfirst(l);
  /* Ignore entry if already dropped in this xact */
  if (oc->deleting_subid != InvalidSubTransactionId)
   continue;
  switch (oc->oncommit)
  {
   case ONCOMMIT_NOOP:
   case ONCOMMIT_PRESERVE_ROWS:
    /* Do nothing (there shouldn't be such entries, actually) */
    break;
   case ONCOMMIT_DELETE_ROWS:
    /*
     * If this transaction hasn't accessed any temporary
     * relations, we can skip truncating ON COMMIT DELETE ROWS
     * tables, as they must still be empty.
     */
    if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
     oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
    break;
   case ONCOMMIT_DROP:
    oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
    break;
  }
 }

通过 MyXactFlags 这个全局变量，来判断是否访问过某个 namespace。注意这一段注释：

If this transaction hasn't accessed any temporary relations, we can skip truncating ON COMMIT DELETE ROWS tables, as they must still be empty.

如果该事务没有访问任何临时关系，我们就可以跳过截断 ON COMMIT DELETE ROWS 表，因为它们肯定还是空的。

这个优化是在 9.3 引入的，也就意味着在 9.2 以前的代码中，不管是什么情况，全部一股脑加入到 OID 列表中，然后每个临时表都需要去遍历，恶性循环。

[postgres@mypg postgres]$ git show c9d7dbacd387ab3814bc6b38010a9e72a02ea4f5
commit c9d7dbacd387ab3814bc6b38010a9e72a02ea4f5
Author: Heikki Linnakangas <[email protected]>
Date:   Tue Jan 29 10:40:22 2013 +0200
    Skip truncating ON COMMIT DELETE ROWS temp tables, if the transaction hasn't
    touched any temporary tables.
        We could try harder, and keep track of whether we've inserted to any temp
    tables, rather than accessed them, and which temp tables have been inserted
    to. But this is dead simple, and already covers many interesting scenarios.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6bc056b..1d5e0c6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10124,7 +10124,13 @@ PreCommit_on_commit_actions(void)
                                /* Do nothing (there shouldn't be such entries, actually) */
                                break;
                        case ONCOMMIT_DELETE_ROWS:
-                               oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
+                               /*
+                                * If this transaction hasn't accessed any temporary
+                                * relations, we can skip truncating ON COMMIT DELETE ROWS
+                                * tables, as they must still be empty.
+                                */
+                               if (MyXactAccessedTempRel)
+                                       oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
                                break;
                        case ONCOMMIT_DROP:
                                {

那让我们验证一下，基于 16，创建一个 on commit delete rows 的临时表，并且在事务中访问一下

postgres=# create temp table tt1(id int) on commit delete rows;
CREATE TABLE
postgres=# begin;
BEGIN
postgres=*# select relfilenode from pg_class where relname = 'tt1';
 relfilenode 
-------------
      158890
(1 row)
postgres=*# select 1 from tt1;
 ?column? 
----------
(0 rows)

可以很清晰地看到，此临时表会被加入到 oids_to_truncate

Breakpoint 1, PreCommit_on_commit_actions () at tablecmds.c:16828
(gdb) n
(gdb) p oc->relid
$1 = 158890  ---👈🏻tt1临时表在这里

不难想象，如果事务内访问的临时表越多 ，虽然想不出什么场景下去访问一个已经是空的表，但是比如业务代码中必须关联的迷惑行为？

  1. 如果是其他事务，那么不管提交与否都已经是空的了
  2. 如果是自己的事务，那么事务提交之后，自然需要去 truncate

也正如注释所说，如果该事务没有访问任何临时关系，我们就可以跳过截断 ON COMMIT DELETE ROWS 表，因为它们肯定还是空的。

那么让我们再看一下最新的 Greenplum 代码，尴尬的是，这一段被完全注释了，也就意味着，不管什么情况下，只要你在 Greenplum 中用到了 ONCOMMIT_DELETE_ROWS 的临时表，即使没有访问过，也需要全部 TRUNCATE，再叠加 Greenplum 中的临时表是放在 shared_buffers 中的...

  switch (oc->oncommit)
  {
   case ONCOMMIT_NOOP:
   case ONCOMMIT_PRESERVE_ROWS:
    /* Do nothing (there shouldn't be such entries, actually) */
    break;
   case ONCOMMIT_DELETE_ROWS:
#if 0
    /*
     * If this transaction hasn't accessed any temporary
     * relations, we can skip truncating ON COMMIT DELETE ROWS
     * tables, as they must still be empty.
     */
    if ((MyXactFlags & XACT_FLAGS_ACCESSEDTEMPNAMESPACE))
#endif
    oids_to_truncate = lappend_oid(oids_to_truncate, oc->relid);
    break;
   case ONCOMMIT_DROP:
    oids_to_drop = lappend_oid(oids_to_drop, oc->relid);
    break;
  }

小结

让我们小结一下

  1. 临时表和普通表一样，删除和 TRUNCATE 需要去遍历对应的缓冲区，所以 BUFFER 越大，性能就越低，如果真有大量 TRUNCATE、DROP 的场景，一定要注意其危害
  2. 其次，不要骚操作去访问一些其他的 ON COMMIT DELETE ROWS 的临时表，访问一个空表没有什么意义，反而会损耗性能，因为需要去遍历
  3. 在 Greenplum，不管如何，都需要去 TRUNCATE，所以在 Greenplum 中，要切忌不要滥用 ON COMMIT DELETE ROWS 的临时表，使用 perf 你可以很轻易看到会成为瓶颈所在

参考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c9d7dbacd387ab3814bc6b38010a9e72a02ea4f5

 Image

推荐阅读

📙从Greenplum中独特的临时表说起

Feel free to contact me 

  • 微信公众号：PostgreSQL学徒
  • Github：https://github.com/xiongcccc
  • 微信：_xiongcc
  • 知乎：xiongcc
  • 墨天轮：https://www.modb.pro/u/39588

KEEP THE THREAD GOING

更多来源文章