PostgreSQL 垃圾回收原理以及如何预防膨胀 - How to prevent object bloat in PostgreSQL
背景
PostgreSQL 9.6已支持snapshot too old
经常看到有人说表又膨胀了，那么导致对象膨胀的常见原因有哪些呢？
1. 未开启autovacuum
对于未开启autovacuum的用户，同时又没有合理的自定义vacuum调度的话，表的垃圾没有及时回收，新的数据又不断进来，膨胀是必然的。（新的数据包括插入和更新，更新产生新版本的记录）
2. 开启了autovacuum, 但是各种原因导致回收不及时，并且新的数据又不断产生，从而导致膨胀。
回收不及时的原因：
2.1. IO差
当数据库非常繁忙时，如果IO比较差，会导致回收垃圾变慢，从而导致膨胀。
这种一般出现在数据库中存在非常巨大的表，并且这些表在执行whole table vacuum (prevent xid wrapped, 或当表的年龄大于vacuum_freeze_table_age时会全表扫)，因此产生大量IO，这期间很容易导致自身或其他表膨胀。
2.2. autovacuum触发较迟
什么情况会触发autovacuum呢?
* A table needs to be vacuumed if the number of dead tuples exceeds a
* threshold. This threshold is calculated as
*
* threshold = vac_base_thresh + vac_scale_factor * reltuples
如果没有设置表级别的autovacuum thresh和factor,那么默认使用参数文件配置的值。如下：
int autovacuum_vac_thresh; // 默认50
double autovacuum_vac_scale; // 默认0.2
也就是说dead tuple达到约为表的20%时，才触发autovacuum。
然后回收又需要一定的时间，所以最终表的膨胀应该是超过20%的。
2.3. 所有worker繁忙，某些表产生的垃圾如果超过阈值，但是在此期间没有worker可以为它处理垃圾回收的事情。导致可能发生膨胀。
可fork的worker进程个数是参数autovacuum_max_workers决定的，初始化autovacuum共享内存时已固定了它的最大进程数。见代码，
src/backend/postmaster/autovacuum.c
/*
* AutoVacuumShmemInit
* Allocate and initialize autovacuum-related shared memory
*/
void
AutoVacuumShmemInit(void)
{
bool found;
AutoVacuumShmem = (AutoVacuumShmemStruct *)
ShmemInitStruct("AutoVacuum Data",
AutoVacuumShmemSize(),
&found);
if (!IsUnderPostmaster)
{
WorkerInfo worker;
int i;
Assert(!found);
AutoVacuumShmem->av_launcherpid = 0;
dlist_init(&AutoVacuumShmem->av_freeWorkers);
dlist_init(&AutoVacuumShmem->av_runningWorkers);
AutoVacuumShmem->av_startingWorker = NULL;
worker = (WorkerInfo) ((char *) AutoVacuumShmem +
MAXALIGN(sizeof(AutoVacuumShmemStruct)));
/* initialize the WorkerInfo free list */
for (i = 0; i < autovacuum_max_workers; i++)
dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
&worker[i].wi_links);
}
else
Assert(found);
}
如果数据库的表很多，而且都比较大，那么当需要vacuum的表超过了配置autovacuum_max_workers的数量，某些表就要等待空闲的worker。这个阶段就容易出现表的膨胀。
以前的PostgreSQL版本，一个数据库同一时间只会起一个worker进程，现在的版本已经没有这个限制了：
src/backend/postmaster/autovacuum.c
* Note that there can be more than one worker in a database concurrently.
* They will store the table they are currently vacuuming in shared memory, so
* that other workers avoid being blocked waiting for the vacuum lock for that
* table. They will also reload the pgstats data just before vacuuming each
* table, to avoid vacuuming a table that was just finished being vacuumed by
* another worker and thus is no longer noted in shared memory. However,
* there is a window (caused by pgstat delay) on which a worker may choose a
* table that was already vacuumed; this is a bug in the current design.
所以如果你的PostgreSQL集群有很多数据库的话，可能需要更多的worker进程来支撑。
另外需要注意一点，worker进程在工作时，每个worker最多会消耗的内存由以下参数决定：
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
所以worker进程越多，内存需求量也越大。
2.4. 数据库中存在长SQL或带XID的长事务。
通过pg_stat_activity.backend_xid和backend_xmin来观察。
backend_xid表示已申请事务号的事务，例如有增删改，DLL等操作的事务。backend_xid从申请事务号开始持续到事务结束。
backend_xmin表示SQL执行时的snapshot，即可见的最大已提交事务。例如查询语句，查询游标。backend_xmin从SQL开始持续到SQL结束，如果是游标的话，持续到游标关闭。
PostgreSQL目前存在一个非常严重的缺陷，当数据库中存在未结束的SQL语句或者未结束的持有事务ID的事务，在此事务过程中，或在此SQL执行时间范围内产生垃圾的话，这些垃圾无法回收，导致数据库膨胀。
也即是判断当前数据库中backend_xid和backend_xmin最小的值，凡是超过这个最小值的事务产生的垃圾都不能回收。
原因见：
src/backend/utils/time/tqual.c
/*
* HeapTupleSatisfiesVacuum
*
* Determine the status of tuples for VACUUM purposes. Here, what
* we mainly want to know is if a tuple is potentially visible to *any*
* running transaction. If so, it can't be removed yet by VACUUM.
*
* OldestXmin is a cutoff XID (obtained from GetOldestXmin()). Tuples
* deleted by XIDs >= OldestXmin are deemed "recently dead"; they might
* still be visible to some open transaction, so we can't remove them,
* even if we see that the deleting transaction has committed.
*/
HTSV_Result
HeapTupleSatisfiesVacuum(HeapTuple htup, TransactionId OldestXmin,
Buffer buffer)
{
后面通过测试来展示。
2.5. 开启了autovacuum_vacuum_cost_delay。
在开启了autovacuum_vacuum_cost_delay后，会使用基于成本的垃圾回收，这个可以有利于降低VACUUM带来的IO影响，但是对于IO没有问题的系统，就没有必要开启autovacuum_vacuum_cost_delay，因为这会使得垃圾回收的时间变长。
当autovacuum进程达到autovacuum_vacuum_cost_limit后，会延迟autovacuum_vacuum_cost_delay后继续。
/*
* Adjust cost limit of each active worker to balance the total of cost
* limit to autovacuum_vacuum_cost_limit.
*/
cost_avail = (double) vac_cost_limit / vac_cost_delay;
dlist_foreach(iter, &AutoVacuumShmem->av_runningWorkers)
{
WorkerInfo worker = dlist_container(WorkerInfoData, wi_links, iter.cur);
if (worker->wi_proc != NULL &&
worker->wi_dobalance &&
worker->wi_cost_limit_base > 0 && worker->wi_cost_delay > 0)
{
int limit = (int)
(cost_avail * worker->wi_cost_limit_base / cost_total);
/*
* We put a lower bound of 1 on the cost_limit, to avoid division-
* by-zero in the vacuum code. Also, in case of roundoff trouble
* in these calculations, let's be sure we don't ever set
* cost_limit to more than the base value.
*/
worker->wi_cost_limit = Max(Min(limit,
worker->wi_cost_limit_base),
1);
}
限制计算方法由另外几个参数决定：
包括在SHARED BUFFER中命中的块，未命中的块，非脏块的额外成本。
vacuum_cost_page_hit (integer)
The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.
vacuum_cost_page_miss (integer)
The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.
vacuum_cost_page_dirty (integer)
The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.
对于IO没有问题的系统，不建议设置autovacuum_vacuum_cost_limit。
2.6. autovacuum launcher process 唤醒时间太长
唤醒时间由参数autovacuum_naptime决定，autovacuum launcher进程负责告诉postmaster需要fork worker进程来进行垃圾回收，但是如果autovacuum launcher进程一直在睡觉的话，那完蛋了，有垃圾了它还在睡觉，那不就等着膨胀吗？
另外还有一个限制在代码中，也就是说不能小于MIN_AUTOVAC_SLEEPTIME 100毫秒：
src/backend/postmaster/autovacuum.c
/* the minimum allowed time between two awakenings of the launcher */
#define MIN_AUTOVAC_SLEEPTIME 100.0 /* milliseconds */
......
/* The smallest time we'll allow the launcher to sleep. */
if (nap->tv_sec <= 0 && nap->tv_usec <= MIN_AUTOVAC_SLEEPTIME * 1000)
{
nap->tv_sec = 0;
nap->tv_usec = MIN_AUTOVAC_SLEEPTIME * 1000;
}
......
/*
* Wait until naptime expires or we get some type of signal (all the
* signal handlers will wake us by calling SetLatch).
*/
rc = WaitLatch(&MyProc->procLatch,
WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH,
(nap.tv_sec * 1000L) + (nap.tv_usec / 1000L));
这个后面我会进行测试来展示它。
2.7 批量删除或批量更新，
例如对于一个10GB的表，一条SQL或一个事务中删除或更新9GB的数据，这9GB的数据必须在事务结束后才能进行垃圾回收，无形中增加了膨胀的可能。
2.8 大量的非HOT更新，会导致索引膨胀，对于BTREE索引来说，整个索引页没有任何引用才能被回收利用，因此索引比较容易膨胀。
测试
测试过程使用如下参数：
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10
autovacuum_naptime = 1
autovacuum_vacuum_threshold = 5
autovacuum_analyze_threshold = 5
autovacuum_vacuum_scale_factor = 0.002
autovacuum_analyze_scale_factor = 0.001
autovacuum_vacuum_cost_delay = 0
测试数据：
postgres=# create table tbl (id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# insert into tbl select generate_series(1,2000000),md5(random()::text),clock_timestamp();
INSERT 0 2000000
postgres=# \dt+ tbl
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | tbl | table | postgres | 146 MB |
(1 row)
postgres=# \di+ tbl_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------+-------+----------+-------+-------+-------------
public | tbl_pkey | index | postgres | tbl | 43 MB |
(1 row)
测试脚本：
一次最多更新25万条
$ vi test.sql
\setrandom id 1 2000000
update tbl set info=md5(random()::text) where id between :id-250000 and :id+250000;
测试两个东西：
1. 测试数据库中存在持有事务号的长事务，这个事务时间段内，数据库产生的垃圾无法被回收。
postgres@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -T 500000
观察日志
postgres@db-172-16-3-150-> tail -f -n 1 postgresql-2015-04-29_174535.csv|grep removable
tuples: 500001 removed, 1710872 remain, 0 are dead but not yet removable
tuples: 0 removed, 478 remain, 3 are dead but not yet removable
tuples: 499647 removed, 1844149 remain, 0 are dead but not yet removable
tuples: 500001 removed, 1830118 remain, 0 are dead but not yet removable
tuples: 290450 removed, 1865527 remain, 0 are dead but not yet removable
现在看没有问题，接下来我开一个持有事务号的事务，
postgres=# begin;
BEGIN
postgres=# select txid_current();
txid_current
--------------
314030959
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
6073
(1 row)
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=6073;
-[ RECORD 1 ]+--------
backend_xid | 314030959 -- 表示当前事务持有的事务号
backend_xmin | 314030959 -- 表示当前SQL可以看到的最大已提交事务的事务号+1
这个事务在另一个会话中通过txid_current_snapshot可以看到它是一个未结束的事务。
postgres=# select * from txid_current_snapshot();
-[ RECORD 1 ]---------+------------------------------
txid_current_snapshot | 314030959:314030981:314030959
接下来看看日志：
不可回收的行在不断的增长。
tuples: 0 removed, 2391797 remain, 500001 are dead but not yet removable
tuples: 0 removed, 484 remain, 9 are dead but not yet removable
tuples: 0 removed, 2459288 remain, 500001 are dead but not yet removable
tuples: 0 removed, 484 remain, 9 are dead but not yet removable
tuples: 0 removed, 2713489 remain, 760235 are dead but not yet removable
tuples: 0 removed, 487 remain, 12 are dead but not yet removable
tuples: 0 removed, 2572991 remain, 760235 are dead but not yet removable
tuples: 0 removed, 487 remain, 12 are dead but not yet removable
tuples: 0 removed, 2849378 remain, 760235 are dead but not yet removable
tuples: 0 removed, 487 remain, 12 are dead but not yet removable
tuples: 0 removed, 3023757 remain, 760235 are dead but not yet removable
tuples: 0 removed, 487 remain, 12 are dead but not yet removable
tuples: 0 removed, 3135900 remain, 1137469 are dead but not yet removable
tuples: 0 removed, 490 remain, 15 are dead but not yet removable
索引和表也明显膨胀了：
postgres=# \dt+ tbl
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | tbl | table | postgres | 781 MB |
(1 row)
postgres=# \di+ tbl_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------+-------+----------+-------+--------+-------------
public | tbl_pkey | index | postgres | tbl | 308 MB |
(1 row)
关闭这个事务后：这个事务期间没有被回收的垃圾可以正常回收
停314030959这个事务之前，另外再开一个
postgres=# begin;
BEGIN
postgres=# select txid_current();
txid_current
--------------
314031042
(1 row)
然后停前面那个
postgres=# end;
COMMIT
这里主要为了说明，这样的连续事务，不会影响上一个事务过程造成影响的未回收的垃圾。
tuples: 0 removed, 481 remain, 6 are dead but not yet removable
tuples: 13629196 removed, 2515757 remain, 500001 are dead but not yet removable
tuples: 0 removed, 4845701 remain, 1000002 are dead but not yet removable
tuples: 0 removed, 7146782 remain, 1500003 are dead but not yet removable
后面启动的那个也停掉，垃圾完全回收了：
postgres=# end;
COMMIT
tuples: 7183691 removed, 11252550 remain, 0 are dead but not yet removable
tuples: 500001 removed, 6234858 remain, 0 are dead but not yet removable
再来一个例子：
打开一个游标，backend_xmin会持续到事务结束前，在backend_xmin未释放的过程中，产生的垃圾是无法被回收的。
postgres=# begin;
BEGIN
postgres=# declare c1 cursor for select 1 from pg_class;
DECLARE CURSOR
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
3823
(1 row)
在另一个会话查询
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
| 5517228
(1 row)
从游标获取数据
postgres=# fetch all from c1;
?column?
----------
......
关闭游标前，backend_xmin还有值。
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
| 5517228
(1 row)
所以在事务号大于backend_xmin的事务中产生的垃圾无法回收。
postgres=# insert into t values (1);
INSERT 0 1
postgres=# delete from t;
DELETE 1
postgres=# vacuum verbose t;
INFO: vacuuming "public.t"
INFO: index "t_pkey" now contains 1 row versions in 30 pages
DETAIL: 0 index row versions were removed.
27 index pages have been deleted, 27 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL: 1 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
postgres=# delete from tt;
DELETE 1
postgres=# vacuum verbose t;
INFO: vacuuming "public.t"
INFO: index "t_pkey" now contains 1 row versions in 30 pages
DETAIL: 0 index row versions were removed.
27 index pages have been deleted, 27 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages
DETAIL: 1 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
关闭游标
postgres=# close c1;
CLOSE CURSOR
backend_xmin释放
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
|
(1 row)
垃圾得以回收
postgres=# vacuum verbose t;
INFO: vacuuming "public.t"
INFO: scanned index "t_pkey" to remove 1 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t": removed 1 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "t_pkey" now contains 0 row versions in 30 pages
DETAIL: 1 index row versions were removed.
27 index pages have been deleted, 27 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "t": truncated 1 to 0 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
postgres=# vacuum verbose tt;
INFO: vacuuming "public.tt"
INFO: "tt": removed 1 row versions in 1 pages
INFO: "tt": found 1 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "tt": truncated 1 to 0 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
再来一个例子：
长查询，backend_xmin会持续到SQL结束时，在backend_xmin未释放的过程中，产生的垃圾是无法被回收的。
postgres=# begin;
BEGIN
postgres=# select pg_sleep(1000);
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
| 5517233
(1 row)
持续到语句执行结束
^CCancel request sent
ERROR: canceling statement due to user request
语句结束后，backend_xmin释放
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
|
(1 row)
再来一个例子：
repeatable read或serializable隔离级别的事务，backend_xmin会持续到事务结束前，在backend_xmin未释放的过程中，产生的垃圾是无法被回收的。
postgres=# begin work isolation level repeatable read;
BEGIN
postgres=# select 1;
?column?
----------
1
(1 row)
持续到事务结束
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
| 5517233
(1 row)
postgres=# end;
COMMIT
postgres=# select backend_xid,backend_xmin from pg_stat_activity where pid=3823;
backend_xid | backend_xmin
-------------+--------------
|
(1 row)
所以我们监控长事务应该包含backend_xmin和backend_xid。
select datname,usename,query,xact_start,now()-xact_start,state from pg_stat_activity where state<>'idle' and (backend_xid is not null or backend_xmin is not null) order by 4;
如果表和索引已经膨胀了，无法收缩，除非使用rewrite table（vacuum full, cluster）或者pg_reorg,pg_repack这样的工具。
我再举一个实际可能存在的例子，例如持续的并发批量更新，也可能导致膨胀：
例如我有一个表包含100万条记录，分成10个进程，每个进程批量更新其中的10万条，并且持续不断的更新。
为什么说这样操作会引起膨胀呢，因为worker process 最小粒度是表级别的，同一张表同一时间只有一个进程在回收垃圾。这种场景会产生三个问题：
1. 瞬时产生垃圾的速度可能超过回收的速度，
2. 产生新TUPLE版本的需求超过FSM的剩余空间，
3. 回收过程中（其他进程可能会启动并发的更新，持有事务排他锁）会遇到不可回收的问题，就是前面这个例子提到的问题。
这几种原因会导致扩展新的数据块可能性变大，即膨胀。
测试例子：
postgres=# truncate tbl;
TRUNCATE TABLE
postgres=# insert into tbl select generate_series(1,1000000),md5(random()::text),clock_timestamp();
INSERT 0 1000000
postgres=# \dt+ tbl
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl
Type | table
Owner | postgres
Size | 73 MB
Description |
postgres=# \di+ tbl_pkey
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl_pkey
Type | index
Owner | postgres
Table | tbl
Size | 21 MB
Description |
$ vi t1.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=1 and id<100000;
$ vi t2.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=100001 and id<200000;
$ vi t3.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=200001 and id<300000;
$ vi t4.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=300001 and id<400000;
$ vi t5.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=400001 and id<500000;
$ vi t6.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=500001 and id<600000;
$ vi t7.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=600001 and id<700000;
$ vi t8.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=700001 and id<800000;
$ vi t9.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=800001 and id<900000;
$ vi t10.sql
update tbl set info=info,crt_time=clock_timestamp() where id >=900001 and id<=1000000;
pgbench -M prepared -n -r -f ./t1.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t2.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t3.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t4.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t5.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t6.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t7.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t8.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t9.sql -c 1 -j 1 -T 500000 &
pgbench -M prepared -n -r -f ./t10.sql -c 1 -j 1 -T 500000 &
观察到出现了不可回收的垃圾
tuples: 0 removed, 2049809 remain, 999991 are dead but not yet removable
tuples: 501373 removed, 2176172 remain, 999991 are dead but not yet removable
tuples: 1603158 removed, 2517367 remain, 899562 are dead but not yet removable
tuples: 405093 removed, 2647780 remain, 899992 are dead but not yet removable
tuples: 1100546 removed, 2724724 remain, 899562 are dead but not yet removable
tuples: 528200 removed, 2864735 remain, 1141307 are dead but not yet removable
tuples: 981628 removed, 2757909 remain, 933307 are dead but not yet removable
膨胀
postgres=# \dt+ tbl
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl
Type | table
Owner | postgres
Size | 554 MB
Description |
postgres=# \di+ tbl_pkey
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl_pkey
Type | index
Owner | postgres
Table | tbl
Size | 114 MB
Description |
如果产生新TUPLE版本的需求超过FSM的剩余空间，还会继续膨胀下去。
这个问题的改进非常简单，将批量更新的粒度降低，即单个事务时间缩短，可以降低事务排他锁持有时间，减少not yet removable的情况，同时事务变小后，单个事务对剩余空间的需求量也变小了，所以不需要扩展数据块。就不会膨胀。
例子：
postgres=# create sequence seq cache 10;
CREATE SEQUENCE
postgres=# vacuum full tbl;
VACUUM
postgres@db-172-16-3-150-> vi test.sql
update tbl set info=info,crt_time=clock_timestamp() where id=mod(nextval('seq'),2000001);
为了提高速度，我这里将nextval改为immutable, 让以上SQL可以走索引扫描（生产环境请勿模仿）。
postgres=# alter function nextval(regclass) immutable;
postgres@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 20 -j 10 -T 500000
因为事务很短，所以只能看到少量的not yet removable，
同时因为一次更新带来的新版本需要的空间也比较小，所以不会超出FSM中的剩余空间，不需要EXTEND BLOCK。
tuples: 11116 removed, 977673 remain, 591 are dead but not yet removable
tuples: 12050 removed, 978437 remain, 979 are dead but not yet removable
tuples: 12687 removed, 981375 remain, 227 are dead but not yet removable
tuples: 12911 removed, 978912 remain, 831 are dead but not yet removable
tuples: 7 removed, 475 remain, 0 are dead but not yet removable
tuples: 13133 removed, 979761 remain, 522 are dead but not yet removable
tuples: 14419 removed, 977651 remain, 1077 are dead but not yet removable
tuples: 12111 removed, 978558 remain, 700 are dead but not yet removable
半小时后，并未出现膨胀
postgres=# \dt+ tbl
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl
Type | table
Owner | postgres
Size | 75 MB
Description |
postgres=# \di+ tbl_pkey
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl_pkey
Type | index
Owner | postgres
Table | tbl
Size | 21 MB
Description |
2. autovacuum launcher process 唤醒时间太长会影响垃圾回收。
调整autovacuum_naptime = 1000， 1000秒的睡觉时间，看看怎么完蛋的吧。
pg_ctl reload
postgres=# vacuum full tbl;
VACUUM
postgres=# \dt+ tbl
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl
Type | table
Owner | postgres
Size | 145 MB
Description |
postgres=# \di+ tbl_pkey
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl_pkey
Type | index
Owner | postgres
Table | tbl
Size | 43 MB
Description |
postgres@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -T 500000
现在不看日志，一段时间之后，你看看pg_stat_all_tables：
postgres@db-172-16-3-150-> psql
psql (9.4.1)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_all_tables where relname='tbl';
-[ RECORD 1 ]-------+------------------------------
relid | 60511
schemaname | public
relname | tbl
seq_scan | 6
seq_tup_read | 12082648
idx_scan | 116
idx_tup_fetch | 49232974
n_tup_ins | 2000000
n_tup_upd | 50732969
n_tup_del | 0
n_tup_hot_upd | 81869
n_live_tup | 2328301
n_dead_tup | 7895450
n_mod_since_analyze | 7825343
last_vacuum |
last_autovacuum | 2015-04-29 18:02:45.325102+08
last_analyze |
last_autoanalyze | 2015-04-29 18:02:45.596096+08
vacuum_count | 0
autovacuum_count | 247
analyze_count | 0
autoanalyze_count | 91
表已经膨胀了：
postgres=# \dt+ tbl
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl
Type | table
Owner | postgres
Size | 393 MB
Description |
postgres=# \di+ tbl_pkey
List of relations
-[ RECORD 1 ]---------
Schema | public
Name | tbl_pkey
Type | index
Owner | postgres
Table | tbl
Size | 115 MB
Description |
小结
通过上面的分析，我们应该如何减少或避免PostgreSQL数据膨胀呢？
1. 一定要开启autovacuum。
2. 提高系统的IO能力，越高越好。
3. 调整触发阈值，让触发阈值和记录数匹配。调小autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor。比如我想在有1万条垃圾记录后就触发垃圾回收，那么对于一个1000万的表来说，我应该把autovacuum_vacuum_scale_factor调到千分之一即0.001，而autovacuum_analyze_scale_factor应该调到0.0005。（注意也不能太低，否则LONG SQL可能引起无用功，参考 《PostgreSQL物理”备库”的哪些操作或配置，可能影响”主库”的性能、垃圾回收、IO波动》 ）
4. 增加autovacuum_max_workers，同时增加autovacuum_work_mem，同时增加系统内存。
例如对于有大量表需要频繁更新的数据库集群，可以将autovacuum_max_workers调整为与CPU核数一致，并将autovacuum_work_mem调整为2GB，同时需要确保系统预留的内存大于autovacuum_max_workers*autovacuum_work_mem。
5. 应用程序设计时，尽量避免如下：
5.1 LONG SQL（包括查，增，删，改，DDL所有的SQL），
5.2 或者打开游标后不关闭，
5.3 或者在不必要的场景使用repeatable read或serializable事务隔离级别，
5.4 或者对大的数据库执行pg_dump进行逻辑备份（隐式repeatable read隔离级别的全库备份），
5.5 或者长时间不关闭申请了事务号的事务（增，删，改，DDL的SQL）。
6. 对于IO没有问题的系统，关闭autovacuum_vacuum_cost_delay。
7. 调整autovacuum_naptime参数到最低(但是也要慎重，比如有长事务导致某些垃圾无法回收时，会不断的唤醒VACUUM WORKER去扫描垃圾页，然后发现无法回收，循环往复，浪费IO和CPU。比如本地有LONG SQL或者STANDBY开启了feedback并有LONG SQL时，都是问题。参考 《PostgreSQL物理”备库”的哪些操作或配置，可能影响”主库”的性能、垃圾回收、IO波动》 )，如果还是唤醒时间太长，可以调整代码中的限制，例如改为1毫秒：
#define MIN_AUTOVAC_SLEEPTIME 1.0 /* milliseconds */
8. 应用程序设计时，避免使用大批量的更新，删除操作，可以切分为多个事务进行。
9. 使用大的数据块，对于现代的硬件水平，32KB是比较好的选择，fillfactor实际上不需要太关注，100就可以了，调低它其实没有必要，因为数据库总是有垃圾，也就是说每个块在被更新后实际上都不可能是满的。
10. 万一真的膨胀了，可以通过table rewrite来回收(如vacuum full, cluster)，但是需要迟排他锁。建议使用pg_reorg或者pg_repack来回收，实际上用到了交换 filenode可以缩短需要持有排他锁的时间。
参考
1. src/backend/postmaster/autovacuum.c
2. src/backend/storage/ipc/sinvaladt.c
/*
* BackendIdGetTransactionIds
* Get the xid and xmin of the backend. The result may be out of date
* arbitrarily quickly, so the caller must be careful about how this
* information is used.
*/
void
BackendIdGetTransactionIds(int backendID, TransactionId *xid, TransactionId *xmin)
{
SISeg *segP = shmInvalBuffer;
*xid = InvalidTransactionId;
*xmin = InvalidTransactionId;
/* Need to lock out additions/removals of backends */
LWLockAcquire(SInvalWriteLock, LW_SHARED);
if (backendID > 0 && backendID <= segP->lastBackend)
{
ProcState *stateP = &segP->procState[backendID - 1];
PGPROC *proc = stateP->proc;
if (proc != NULL)
{
PGXACT *xact = &ProcGlobal->allPgXact[proc->pgprocno];
*xid = xact->xid;
*xmin = xact->xmin;
}
}
LWLockRelease(SInvalWriteLock);
}
/* ----------
* pgstat_read_current_status() -
*
* Copy the current contents of the PgBackendStatus array to local memory,
* if not already done in this transaction.
* ----------
*/
static void
pgstat_read_current_status(void)
{
......
/* Only valid entries get included into the local array */
if (localentry->backendStatus.st_procpid > 0)
{
BackendIdGetTransactionIds(i,
&localentry->backend_xid,
&localentry->backend_xmin);
localentry++;
localappname += NAMEDATALEN;
localactivity += pgstat_track_activity_query_size;
localNumBackends++;
}
......
注意事项
《PostgreSQL物理”备库”的哪些操作或配置，可能影响”主库”的性能、垃圾回收、IO波动》