大量使用临时表带来的系统表如pg_attribute膨胀问题，替代方案，以及如何擦屁股 - Greenplum, PostgreSQL最佳实践

背景

以前有使用Greenplum的朋友遇到元表膨胀的问题，总结了原因写了一篇文章如下。

建议先阅读这篇文档，再阅读本文，里面有些原理我在本文就不讲了。

http://blog.163.com/digoal@126/blog/static/1638770402014616113353555

近日，又有使用PostgreSQL的朋友也遇到类似的问题，一些元表膨胀非常厉害。

用户大量的使用临时表，频繁的创建（PG的临时表是需要随时用随时建的，每个会话都要自己建，而且每个临时表会在pg_class,pg_attribute 中留下痕迹，用完还需要从元表中 delete 这些元数据），因此元表pg_attribute, pg_rewrite, pg_class 会出现大量的dead tuple。

同时用户的业务需要在数据库长期跑长事务，这个也是膨胀的关键点。

图说问题, 一针见血

目前PostgreSQL的版本，在回收垃圾时，只能回收比现存最早事务更老的垃圾tuple。

如图

这张图表示，当前XID=99999，当前数据库集群中未结束的事务是XID=1000的事务，假设在XID=1000后系统产生了100GB的垃圾数据（如UPDATE了一个大表，或者删除或更新了一大批数据）。

那么这100GB是没有办法被vacuum回收的，只能回收XID=1000以前的垃圾。

这就是膨胀的起因。 (长事务同时伴随大量更新或删除产生垃圾)

PostgreSQL 9.6会引入快照过旧的功能，有点类似于Oracle 的undo snapshot too old的玩意。来防止因存在很长的事务带来的膨胀问题。

如图

例如用户设置了只保留最近1000个事务产生的垃圾，当老的事务访问到已经被vacuum清除的数据时，会报snapshot too old的错误。 这个方法大大降低了膨胀的概率。

如何戒掉临时表(替代方案)

回到本文的主题，用户除了用临时表，还有什么方法？

因为PG的临时表是随用随建的（会话断开又得建）， 有什么好的替代方案，不要动到元数据吗?

答案是，当然有。

如果你是在存储过程中使用临时表，你可以建立composite type，然后用数组来存储这些临时数据，这样就不会动到元数据了。

例子 :

postgres=# create type t as (id int, info text, crt_time timestamp);  
CREATE TYPE  
  
create or replace function f() returns void as $$                                                                      
declare   
  tmp_t t[];  
  tmp t;  
begin  
  -- 产生一批临时记录，聚合后存入数组，原来你可能要存入临时表  
  select array_agg((t1)::t) into tmp_t from (select id,md5(random()::text),clock_timestamp() from generate_series(1,100) t(id)) t1;  
  
  -- 把数组当表使用, unnest数组,   
  -- select (unnest(tmp_t)).*;  
    
  -- 你可能会用临时数据做一些JOIN操作, 用unnest和subquery就能和其他表一样JOIN操作  
  -- select t2.* from t2 join (select (unnest(tmp_t)).*) t1 on t1.id=t2.id where ....;  
  -- update t2 set xx=xx from (select (unnest(tmp_t)).*) t1 where t2.xx=t1.xx and t1.xx=xx and t2.xx=xx;  
  
  -- 你可能会对临时数据做一些处理  
  for i in 1..array_length(tmp_t, 1) loop  
    raise notice '%', tmp_t[i];  
    tmp := tmp_t[i];  
    tmp.info := 'new '||tmp.info;  
    tmp_t[i] := tmp;  
    raise notice '%', tmp_t[i];  
    -- 逻辑处理可以放在这里做  
  end loop;  
end;  
$$ language plpgsql strict;  
  
...  
NOTICE:  (91,dc3606d6b017f60fc10b945ab00b02bd,"2016-06-15 17:53:41.749235")  
NOTICE:  (91,"new dc3606d6b017f60fc10b945ab00b02bd","2016-06-15 17:53:41.749235")  
...  

使用composite type的数组完全能满足原来使用临时表才能满足的需求。

但是也需要注意array是在内存中的，如果临时数据非常庞大，使用数组可能OOM。

虽然临时表也是在内存中的，但是它太大的时候可以落盘，不会OOM。

数组求记录数，使用 array_length就可以了，千万不要unnest转成行再count。

学会擦屁股

如果元数据已经膨胀了怎么处理？该擦的屁股还得擦。

对于PostgreSQL 9.5 你可以放心大胆的擦，（其他版本暂时没有验证过）。

可选的方法有 vacuum full, cluster, reindex.

如果要回收空间，同时重建索引，使用vacuum full。

如果要重排元表顺序，使用cluster。

如果要重新建索引，使用reindex 。

根据你自己的情况选择就可以了。

当你使用的是Greenplum时，要特别注意。

就是pg_class，这个元表是比较特殊的，因为其他元表的记录也记录在这里，包括索引的元表也记录在pg_class里面。

对它进行vacuum full, reindex, cluster都会导致有几个元表的记录会排到这张表的最后面去。

例如我的系统中有几百万张表，这样快速生成几百万表

CREATE OR REPLACE FUNCTION public.f(id integer)  
 RETURNS void  
 LANGUAGE plpgsql  
 STRICT  
AS $function$   
declare   
  sql text;  
  i int;   
begin   
  for i in id..id+100 loop   
    sql='create table if not exists tbl'||i||' (id int)';  
    execute sql;  
  end loop;  
end;  
$function$;  
  
vi test.sql  
\setrandom id 1 1000000000  
select f(:id);  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 20 -j 20 -T 1000  

对pg_class这样操作后，有几条元数据就会跑到pg_class的最后面去。

1.

vacuum full pg_class;    
  
然后你会发现这几个小家伙排到最后了  
postgres=# select oid,relname,ctid from pg_class order by ctid desc limit 10;  
   oid    |              relname              |    ctid      
----------+-----------------------------------+------------  
     1259 | pg_class                          | (92424,30)  
     3455 | pg_class_tblspc_relfilenode_index | (92424,29)  
     2663 | pg_class_relname_nsp_index        | (92424,28)  
     2662 | pg_class_oid_index                | (92424,27)  

2.

cluster pg_class using pg_class_oid_index;    

– 你可能会想，oid=这几个的是小的，应该到前面的行了吧。 但实际并不是如此。
这几个小家伙排到最后了

postgres=# select oid,relname,ctid from pg_class order by ctid desc limit 10;  
   oid    |              relname              |    ctid      
----------+-----------------------------------+------------  
     1259 | pg_class                          | (92424,30)  
     3455 | pg_class_tblspc_relfilenode_index | (92424,29)  
     2663 | pg_class_relname_nsp_index        | (92424,28)  
     2662 | pg_class_oid_index                | (92424,27)  
 13089945 | test_pkey                         | (92424,25)  
 13089944 | pg_toast_13089939_index           | (92424,24)  
 13089942 | pg_toast_13089939                 | (92424,23)  
 13089939 | test                              | (92424,22)  

3.

reindex table pg_class;    

– 别看它只是重建索引，但是实际上它也会影响 pg_class_oid_index , pg_class_relname_nsp_index , pg_class_tblspc_relfilenode_index 这三在pg_class的顺序。
这几个小家伙又奇迹般的排到最后了

postgres=# select oid,relname,ctid from pg_class order by ctid desc limit 10;  
   oid    |              relname              |    ctid      
----------+-----------------------------------+------------  
     3455 | pg_class_tblspc_relfilenode_index | (92424,33)  
     2663 | pg_class_relname_nsp_index        | (92424,32)  
     2662 | pg_class_oid_index                | (92424,31)  
     1259 | pg_class                          | (92424,30)  
 13089945 | test_pkey                         | (92424,25)  

以上诡异的问题，在Greenplum数据库中就是致命的（不知道现在的版本改进了没有，反正PostgreSQL 9.5是没这个问题的）。

如果你在GP中有几百万的表，这几条元数据因以上原因跑pg_class 的最后几页去了，那问题就大了(后面会讲回天的方法)。

http://cncc.bingj.com/cache.aspx?q=osdba+pg_index+reindex&d=5025474051580513&mkt=zh-CN&setlang=zh-CN&w=pa4BQX_-Pu7tUlh6kM34Dv-xu2uzi4m6

以前老唐在Greenplum上对元表做了这样的操作，结果很感人。

greenplum中reindex pg_class后发生狂读pg_class的问题分析

当访问任何一个表时，需要从pg_class表中提取这个表的元数据信息，而在pg_class中查找表的原数据信息，需要访问索引pg_class_oid_index，而访问索引pg_class_oid_index，也需要从pg_class表中获得索引pg_class_oid_index的元数据信息，而获得索引pg_class_oid_index自己的元数据信息，就不能再通过索引自己去查找自己的信息了，这里就只能从头扫描表pg_class来获得，而由于索引pg_class_oid_index的条目被移到了最后的位置，所以导致需要几乎把pg_class从头 扫描到尾才能找到pg_class_oid_index的条目，这样就大大的降低了数据库的性能。

以前也做过pg_class，为什么没有出来这个问题呢？我和任振中也做了测试发现，当原先的0号块中有空闲空间时，做reindex时，索引pg_class_oid_index的 条目仍会在0号块中，这样就不会出现上面的问题了。

由此可知，在greenplum中是不能随便对pg_class表做reindex了。

如果不小心把pg_class_oid_index等几个元表弄到pg_class 的末端了咋办

其实还是可以回天的，而且方法依旧很简单。

把头几个页的数据更新掉，然后用 vacuum回收前几页，然后把要挪动的记录更新一下，FSM会随机选择空页面给你放，就有机会回到前面的页。

例子

当前他们在这里

postgres=# select ctid,oid,relname from pg_class where oid in (1259,3455,2663,2662);  
    ctid    | oid  |              relname                
------------+------+-----------------------------------  
 (92424,42) | 1259 | pg_class  
 (92424,43) | 2662 | pg_class_oid_index  
 (92424,44) | 2663 | pg_class_relname_nsp_index  
 (0,1)      | 3455 | pg_class_tblspc_relfilenode_index  
(4 rows)  

把前面5页的记录都更新一遍

postgres=# update pg_class set relkind=relkind where ctid::text ~ '^\(0,' or ctid::text ~ '^\(1,' or ctid::text ~ '^\(2,' or ctid::text ~ '^\(3,' or ctid::text ~ '^\(4,' or ctid::text ~ '^\(5,';  
UPDATE 207  

回收垃圾

postgres=# vacuum verbose pg_class;  

更新要挪动的记录

postgres=# update pg_class set relkind=relkind where oid in (1259,3455,2663,2662);  
UPDATE 4  

已经挪到前面的页了，恭喜你

postgres=# select ctid,oid,relname from pg_class where oid in (1259,3455,2663,2662);  
  ctid  | oid  |              relname                
--------+------+-----------------------------------  
 (0,46) | 1259 | pg_class  
 (0,48) | 2662 | pg_class_oid_index  
 (1,1)  | 2663 | pg_class_relname_nsp_index  
 (1,2)  | 3455 | pg_class_tblspc_relfilenode_index  
(4 rows)  

参考

src/backend/catalog/postgres.bki

create pg_proc 1255 bootstrap rowtype_oid 81  
...  
insert OID = 1242 ( boolin 11 10 12 1 0 0 0 f f f f t f i 1 0 16 "2275" _null_ _null_ _null_ _null_ _null_ boolin _null_ _null_ _null_ )  
insert OID = 1243 ( boolout 11 10 12 1 0 0 0 f f f f t f i 1 0 2275 "16" _null_ _null_ _null_ _null_ _null_ boolout _null_ _null_ _null_ )  
...  

注意事项

1. 因为vacuum full和cluster都是DDL操作，需要注意锁等待和本身需要运行的时间，建议空闲时间操作。

并且设置语句超时或锁超时。

2. 对于Greenplum 千万别对pg_class执行vacuum full, reindex, cluster操作，否则可能是致命伤害。

万一你不小心这么做了，可以使用我上面教你的挪动记录的方法来补救。

digoal’s 大量PostgreSQL文章入口