1500字范文 > How to prevent object bloat in PostgreSQL

How to prevent object bloat in PostgreSQL

时间：2020-07-05 14:42:08

Postgres全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)Postgres-XL的项目发起人Mason Sharppgpool的作者石井达夫(Tatsuo Ishii)PG-Strom的作者海外浩平(Kaigai Kohei)Greenplum研发总监姚延栋周正中(德哥), PostgreSQL中国用户会创始人之一汪洋，平安科技数据库技术部经理……

经常看到有人说表又膨胀了，那么导致对象膨胀的常见原因有哪些呢？

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 doubleautovacuum_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*/voidAutoVacuumShmemInit(void){boolfound;

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. 数据库中存在持有事务Exclusive锁的长事务 PostgreSQL目前存在一个非常严重的缺陷，当数据库中存在持有事务Exclusive锁的长事务，事务过程中产生垃圾的话，无法回收，导致数据库膨胀。原因见： 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_ResultHeapTupleSatisfiesVacuum(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 = onlog_autovacuum_min_duration = 0autovacuum_max_workers = 10autovacuum_naptime = 1autovacuum_vacuum_threshold = 5autovacuum_analyze_threshold = 5autovacuum_vacuum_scale_factor = 0.002autovacuum_analyze_scale_factor = 0.001autovacuum_vacuum_cost_delay = 0

测试数据：

测试脚本：一次最多更新25万条

$ vi test.sql\setrandom id 1 2000000update tbl set info=md5(random()::text) where id between :id-250000 and :id+250000;

测试两个东西： 1. 数据库中存在持有事务Exclusive锁的长事务，这个事务时间段内，数据库产生的垃圾无法被回收。

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--04-29_174535.csv|grep removabletuples: 500001 removed, 1710872 remain, 0 are dead but not yet removabletuples: 0 removed, 478 remain, 3 are dead but not yet removabletuples: 499647 removed, 1844149 remain, 0 are dead but not yet removabletuples: 500001 removed, 1830118 remain, 0 are dead but not yet removabletuples: 290450 removed, 1865527 remain, 0 are dead but not yet removable

现在看没有问题，接下来我开一个持有事务Exclusive锁的事务，

这个事务在另一个会话中通过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 removabletuples: 0 removed, 484 remain, 9 are dead but not yet removabletuples: 0 removed, 2459288 remain, 500001 are dead but not yet removabletuples: 0 removed, 484 remain, 9 are dead but not yet removabletuples: 0 removed, 2713489 remain, 760235 are dead but not yet removabletuples: 0 removed, 487 remain, 12 are dead but not yet removabletuples: 0 removed, 2572991 remain, 760235 are dead but not yet removabletuples: 0 removed, 487 remain, 12 are dead but not yet removabletuples: 0 removed, 2849378 remain, 760235 are dead but not yet removabletuples: 0 removed, 487 remain, 12 are dead but not yet removabletuples: 0 removed, 3023757 remain, 760235 are dead but not yet removabletuples: 0 removed, 487 remain, 12 are dead but not yet removabletuples: 0 removed, 3135900 remain, 1137469 are dead but not yet removabletuples: 0 removed, 490 remain, 15 are dead but not yet removable

索引和表也明显膨胀了：

关闭这个事务后：这个事务期间没有被回收的垃圾可以正常回收停之前，另外再开一个

postgres=# begin;BEGINpostgres=# select txid_current();txid_current-------------- 314031042(1 row)

然后停前面那个

postgres=# end;COMMIT

这里主要为了说明，这样的连续事务，不会影响上一个事务过程造成影响的未回收的垃圾。

tuples: 0 removed, 481 remain, 6 are dead but not yet removabletuples: 13629196 removed, 2515757 remain, 500001 are dead but not yet removabletuples: 0 removed, 4845701 remain, 1000002 are dead but not yet removabletuples: 0 removed, 7146782 remain, 1500003 are dead but not yet removable

后面启动的那个也停掉，垃圾完全回收了：

postgres=# end;COMMITtuples: 7183691 removed, 11252550 remain, 0 are dead but not yet removabletuples: 500001 removed, 6234858 remain, 0 are dead but not yet removable

但是表和索引已经膨胀了，无法收缩，除非使用rewrite table（vacuum full, cluster）或者pg_reorg,pg_repack这样的工具。我再举一个实际可能存在的例子，例如持续的并发批量更新，也可能导致膨胀：例如我有一个表包含100万条记录，分成10个进程，每个进程批量更新其中的10万条，并且持续不断的更新。为什么说这样操作会引起膨胀呢，因为worker process 最小粒度是表级别的，同一张表同一时间只有一个进程在回收垃圾。这种场景会产生三个问题： 1. 瞬时产生垃圾的速度可能超过回收的速度， 2. 产生新TUPLE版本的需求超过FSM的剩余空间， 3. 回收过程中（其他进程可能会启动并发的更新，持有事务排他锁）会遇到不可回收的问题，就是前面这个例子提到的问题。这几种原因会导致扩展新的数据块可能性变大，即膨胀。测试例子：

postgres=# truncate tbl;TRUNCATE TABLEpostgres=# insert into tbl select generate_series(1,1000000),md5(random()::text),clock_timestamp();INSERT 0 1000000postgres=# \dt+ tblList of relations-[ RECORD 1 ]---------Schema | publicName | tblType | tableOwner | postgresSize | 73 MBDescription |

$ vi t1.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=1 and id<100000;$ vi t2.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=100001 and id<200000;$ vi t3.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=200001 and id<300000;$ vi t4.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=300001 and id<400000;$ vi t5.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=400001 and id<500000;$ vi t6.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=500001 and id<600000;$ vi t7.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=600001 and id<700000;$ vi t8.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=700001 and id<800000;$ vi t9.sqlupdate tbl set info=info,crt_time=clock_timestamp() where id >=800001 and id<900000;$ vi t10.sqlupdate 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 removabletuples: 501373 removed, 2176172 remain, 999991 are dead but not yet removabletuples: 1603158 removed, 2517367 remain, 899562 are dead but not yet removabletuples: 405093 removed, 2647780 remain, 899992 are dead but not yet removabletuples: 1100546 removed, 2724724 remain, 899562 are dead but not yet removabletuples: 528200 removed, 2864735 remain, 1141307 are dead but not yet removabletuples: 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 SEQUENCEpostgres=# vacuum full tbl;VACUUM

postgres@db-172-16-3-150-> vi test.sqlupdate 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 removabletuples: 12050 removed, 978437 remain, 979 are dead but not yet removabletuples: 12687 removed, 981375 remain, 227 are dead but not yet removabletuples: 12911 removed, 978912 remain, 831 are dead but not yet removabletuples: 7 removed, 475 remain, 0 are dead but not yet removabletuples: 13133 removed, 979761 remain, 522 are dead but not yet removabletuples: 14419 removed, 977651 remain, 1077 are dead but not yet removabletuples: 12111 removed, 978558 remain, 700 are dead but not yet removable

2. autovacuum launcher process 唤醒时间太长会影响垃圾回收。调整autovacuum_naptime = 1000， 1000秒的睡觉时间，看看怎么完蛋的吧。

postgres@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 1 -j 1 -T 500000

现在不看日志，一段时间之后，你看看pg_stat_all_tables：

表已经膨胀了：

[小结] 通过上面的分析，我们应该如何减少或避免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。 4. 增加autovacuum_max_workers，同时增加autovacuum_work_mem，同时增加系统内存。例如对于有大量表需要频繁更新的数据库集群，可以将autovacuum_max_workers调整为与CPU核数一致，并将autovacuum_work_mem调整为2GB，同时需要确保系统预留的内存大于autovacuum_max_workers*autovacuum_work_mem。 5. 应用程序设计时，尽量避免持有事务Exclusive锁的长事务（DDL,DML都会持有事务Exclusive锁） 6. 对于IO没有问题的系统，关闭autovacuum_vacuum_cost_delay。 7. 调整autovacuum_naptime参数到最低，如果还是唤醒时间太长，可以调整代码中的限制，例如改为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

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。