PgSQL · 答疑解惑 · 表膨胀 · 数据库内核月报

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