数据库内核月报

数据库内核月报 - 2015 / 12

PgSQL · 答疑解惑 · 表膨胀

Author: 明虚

背景

最近处理了几起线上实例表膨胀的问题。表膨胀是指表的数据和索引所占文件系统的空间,在有效数据量并未发生大的变化的情况下,不断增大。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 <table name> 操作。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%。