PgSQL表膨胀处理（转）

原文地址： https://blog.csdn.net/qq_18863573/article/details/103603175

背景

最近处理了几起线上实例表膨胀的问题。表膨胀是指表的数据和索引所占文件系统的空间，在有效数据量并未发生大的变化的情况下，不断增大。PG使用过程中需要特别关注这方面，我们来给大家解析一下表膨胀的原因。

表膨胀的直接触发因素是表上的大量更新，如全表的update操作、大量的insert+delete操作等。而我们知道，PG在更新数据时，是不直接删除老数据的。一个update操作执行后，被更改的数据的旧版本也被保留下来，直到对表做vacuum操作的时候，才考虑回收旧版本。做数据更新时，这些旧版本不及时回收就会造成表膨胀。

线上实例都配置了autovacuum，有了autovacuum，PG会定期自动启动autovacuum worker进程，执行vacuum回收旧版本，防止表膨胀。但在我们看到的几起表膨胀问题里面，autovacuum几乎每分钟运行一次，仍然没有避免表膨胀，这是为什么呢？

表膨胀问题的重现

从问题实例的pg_stat_activity视图里面，可以发现它们有一个特点，就是有长时间未提交或终止的事务。

我们用下面的例子简单模拟一下。先创建一张表，插入一条数据。再建立两个连接，其中一个开启一个事务，执行插入和查询，但不提交；另一个不断执行update操作。

控制台A：

postgres=# begin;
BEGIN
postgres=# insert into test_bloat values(1);
INSERT 0 1
postgres=# select * from test_bloat;
a
---
1
1
(2 rows)

postgres=#
 

控制台B：

postgres=# update test_bloat set a = 1;
UPDATE 1
postgres=# \watch 1
Watch every 1s Wed Nov 11 17:04:31 2015
....

过不多久，查看表的大小就会发现它不断增大：

postgres=# \dt+ test_bloat
List of relations`
Schema | Name | Type | Owner | Size | Description
--------+------------+-------+---------------+------------+-------------
public | test_bloat | table | guangzhou.zgz | 8192 bytes |
(1 row)

...

postgres=# \dt+ test_bloat
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------+-------+---------------+-------+-------------
public | test_bloat | table | guangzhou.zgz | 40 kB |

查看第一个连接中的事务状态如下：

postgres=# select * from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid | 13003
datname | postgres
pid | 113306
usesysid | 10
usename | guangzhou.zgz
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2015-11-11 16:45:38.452728+08
xact_start | 2015-11-11 16:47:00.998929+08
query_start | 2015-11-11 16:47:43.657035+08
state_change | 2015-11-11 16:47:43.658001+08
waiting | f
state | idle in transaction
backend_xid | 2431157
backend_xmin |
query | select * from test_bloat;

可以发现，它有两个特点：

1. 它的状态为“idle in transaction”，这是因为它未提交，又没有正在进行的查询；
2. 它的backend_xid不为空，即它有事务号，这是因为它执行了更新操作，插入了一条数据。注意，PG只在发生更新的时候才分配事务ID，没有执行更新操作的事务（即只读事务）是没有backend_xid的。

测试发现，如果及时将此种事务提交，并不会造成表膨胀。可见正是这些事务导致了旧版本无法回收！

为什么旧版本没有被回收

我们从代码里面看看，为什么长事务阻止了版本回收？先看看vacuum回收旧版本的代码。vacuum操作由autovacuum worker执行，其调用顺序如下：

vacuum->vacuum_rel->lazy_vacuum_rel->lazy_scan_heap->heap_page_prune -> heap_prune_chain

vacuum每次处理一个表；每个表同时只有一个进程进行vacuuum。其中，lazy_scan_heap扫描一个表的所有页面，对每个页面调用heap_page_prune进行处理。heap_page_prune调用heap_prune_chain函数来判断，一个旧版本是否可以被回收（即删除）。这里要考虑的一个重要因素是，旧版本是否可能被当前系统里正在进行的事务（活跃事务，即已开始但未提交或终止的事务）需要。heap_prune_chain调用了HeapTupleSatisfiesVacuum来对一个数据记录的旧版本（tuple）做这个判断。HeapTupleSatisfiesVacuum里面最重要的一个判断如下：

if (!TransactionIdPrecedes(HeapTupleHeaderGetRawXmax(tuple), OldestXmin))
 return HEAPTUPLE_RECENTLY_DEAD;

这个判断实际上是计算当前tuple的xmax是否大于或等于OldestXmin。我们知道，xmax是删除这个tuple的事务ID，而OldestXmin由GetOldestXmin函数计算，是所有活跃事务的ID，以及所有事务的xmin 组成的集合中最小的事务ID。所有ID大于这个OldestXmin的事务，都是“新近”开启的事务，其他事务可能需要读取这个旧版本用于查询，所以不能物理删除，则返回HEAPTUPLE_RECENTLY_DEAD，保留此tuple（即不回收）。但如果系统中含有很久之前开启而未提交的事务，并且这个事务由于执行过更新，创建了事务ID（成为“长事务”），那么OldestXmin会非常小，vacuum做上述这个判断时，结果通常为true，即返回HEAPTUPLE_RECENTLY_DEAD，这样heap_prune_chain将会保留此tuple（旧版本），导致回收无法完成，表膨胀由此发生。

需要注意的是，并不是只有更新过数据的事务，长时间不提交会造成表膨胀，只读的事务也是同样的！看下面的

case：

控制台A：

postgres=# begin;
BEGIN
postgres=# declare c1 cursor for select * from test_bloat for read only;
DECLARE CURSOR
postgres=#

控制台B:

postgres=# select * from pg_stat_activity;`

-[ RECORD 1 ]----+--------------------------------------------------------------
datid | 13003
datname | postgres
pid | 110811
usesysid | 10
usename | guangzhou.zgz
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2015-11-12 11:01:00.880549+08
xact_start | 2015-11-12 11:01:32.843927x+08
query_start | 2015-11-12 11:01:33.87293+08
state_change | 2015-11-12 11:01:33.873547+08
waiting | f
state | idle in transaction
backend_xid |
backend_xmin | 2436509
query | declare c1 cursor for select * from test_bloat for read only;

我们看到，这个只读事务的xmin是非空的，会被用来做OldestXmin，如果它长时间不完成操作（即cursore不close），就会造成整个数据库的表膨胀！因此，即便是只读事务，也要及时提交；另外，避免在存在大量更新操作的实例上，跑运行时间很长的查询语句。

回收膨胀的空间

如何回收膨胀的空间？长事务结束后，vacuum会回收一部分旧版本。但它回收数据页内的旧版本后，一般情况下并不能把空间还给操作系统。就是说，表所占的空间没有变化。只有一种情况下，即回收的页处于存储数据的文件（一张表对应一个或多个文件）尾部，并且页内没有事务可见的tuple（即整个页都可以删除）时，会做truncate操作，把尾部的这些页统一从文件中删除，文件大小和表所占空间随之减少。

另一种回收膨胀空间的方法是，执行vacuum full

操作。vacuum full命令实际上重建了整张表和上面的索引。它的缺点是，需要长时间锁住整张表，并耗费大量的IO，对应用影响很大。要减少vacuum full锁住表的时间，可以使用社区提供的pg_repack工具。它的原理是基于原表创建一张新表，同时利用触发器，将原表上当前的增量更新不断记录下来。新表建好后，将所记录的增量更新应用到新表，直到新旧表数据完全一致。最后将新旧表表名互换，删除旧表，即完成了表的空间整理操作，回收了空间。

避免表膨胀的方法

上面看到，表一旦膨胀，空间很难回收回来，所以要尽可能的避免表膨胀。要避免表膨胀，需要注意：

1. 尽早的、及时的提交事务；

2. 设计应用时，要使事务尽量短小；

3. 注意配置与应用规模相适应的硬件资源（IO能力、CPU、内存等），并调教好数据库，使其性能最优，避免有些事务因为资源或性能问题长时间无法完成；

4. 提交autovacuum，使其能按合理的周期运行。这方面的内容，我们今后专门介绍；

5. 定期监控系统中是否有长事务，可以使用下面的SQL监控持续时间超过一定时间的事务：

    select * from pg_stat_activity where state<>'idle' and pg_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and extract(epoch from (now() - xact_start))  > <时间阈值，单位秒> ;
   

只要运用好上述方法，表膨胀是可以有效控制的。正常的表膨胀不会超过原来数据量的20%。