PostgreSQL autovacuum 优化与调试 (1 触发 autovacuum 的条件)

PostgreSQL 的数据库系统中是需要进行autovacuum 进行表级别的数据清理的。在开始autovacuum 进行调优之前实际上是需要理解为什么需要autovacuum.

长话短说，基于POSTGRESQL 的原理每个表中的行会存在多个版本的数据，为了完成数据库的MVCC 多版本控制，以及数据的UNDO 的功能在这些过期版本的行被弃用后，会产生众多的死行，dead tuple. 而这样的情况下，如果不及时的将这些dead tuple 进行清理，轻则影响磁盘空间的在利用，导致本来一张表5G可能就能存储，但最终可能达到10G 甚至更大的空间。

同时每个dead tuple 还牵扯这index 的问题，以及多版本比对时造成的性能损耗，所以一个表的 dead tuple 越多，数据查询的性能也就越来越低。 这也是postgresql 核心问题之一。

在进行表的 autovacuum 之前，我们应该做的是了解系统当中到底是不是存在需要进行vacuum 的地方，也就是表的 dead tuple 到底有没有，有多少。

1 监控 monitor

表的dead tuple监控 其中我们

pgbench -n -r -T 60 -P 1 -c 500 -j 64

先给我们的表增加一些数据

select schemaname,relname,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_autovacuum,autovacuum_count from pg_stat_all_tables where schemaname = 'public';

通过上面的语句我们可以先查看一下当前的postgresql 中的表的dead tuple 以及autovacuum 的情况。

从中这边可以发现，我们这些表中有的已经进行了autovacuum 有些没有，并且存在dead tuple.

提出问题

1 什么条件 autovacuum 对表进行vacuum 工作

2 autovacuum 进行了什么样的工作

3 autovacuum 是否可以被关闭

4 autovacuum 调整的参数有那些

5 autovacuum 针对某个特殊表进行调节

6 autovacuum 的工作情况怎么了解

下面针对以上的问题，分期来进行

1 什么条件 autovacuum 对表进行vacuum 工作

实际上 autovacuum 本身并没有想象的简单，他需要完成的工作除了上面提到 cleanup dead tuples ，同时他还的想如何减少在工作期间对系统的用影响。那么什么时间，什么频率 autovacuum来出来进行工作就是一个要考虑的问题。

这里抛出一个问题，如果我们通过上面的语句来查询dead tuple ，每次查询某个表的 dead tuple 的数量都特别的大， 这说明一个问题， autovacuum 做的不够多 （当然也有可能是一些long transaction，这个问题不在这个问题的考虑范围）。

实际上什么时间对表进行autovacuum 这个问题，应该换成频率，什么样的情况下会触发 autovacuum对表进行操作。

这里有两个关键的参数

autovacuum_vacuum_threshold 这个参数主要是指定表中变动的tuple数，超过这个数字会触发autovacuum 对这个表进行整理

autovacuum_vacuum_scale_factor 这个参数主要指定表的变动行占整体表的百分之几，超过这个占用的比率会触发 autovacuum

在操作过程中，如果表符合上述的两个条件，被autovacuum 扫描到就开始进行整理。

具体的过程为 修改表的行数 + 修改表的百分比*总的表的行数 < 实际修改的表的行数

举例： 我们本次修改的行数为 200行 ， autovacuum_vacuum_threshold = 50 autovacuum_vacuum_scale_factor = 0.1 ，目前表的行数为1000000 一百万。

则 计算公式为 1000000 * 0.1 + 50 = 10万零50 当修改的表超过这个行数，才能触发autovacuum 。

所以一个表随着数据行数越来越多，则会导致触发autovacuum 越来越少，最终导致表的膨胀越来越大。

所以调整autovacuum 的参数对于一个基于postgresql核心的数据库是非常重要的。