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