postgresql autovacuum作用与原理

什么是autovacuum

autovacuum是postgresql数据库是一个后台进程，随数据库自启动。

[postgres@lineqi ~]$ ps -ef |grep postgres
postgres   2626      1  0 09:17 pts/0    00:00:01 /usr/local/pgsql/bin/postgres -D /data/pg11.6
postgres   2627   2626  0 09:17 ?        00:00:00 postgres: logger   
postgres   2629   2626  0 09:17 ?        00:00:00 postgres: checkpointer   
postgres   2630   2626  0 09:17 ?        00:00:00 postgres: background writer   
postgres   2631   2626  0 09:17 ?        00:00:00 postgres: walwriter   
postgres   2632   2626  0 09:17 ?        00:00:00 postgres: autovacuum launcher   
postgres   2633   2626  0 09:17 ?        00:00:00 postgres: archiver   
postgres   2634   2626  0 09:17 ?        00:00:02 postgres: stats collector   
postgres   2635   2626  0 09:17 ?        00:00:00 postgres: TimescaleDB Background Worker Launcher  
postgres   2636   2626  0 09:17 ?        00:00:00 postgres: logical replication launcher   
1
2
3
4
5
6
7
8
9
10
11

autovacuum的作用

autovacuum作用如下：
1、清理表中的过期死元组
2、防止业务表膨胀
3、更新表的统计信息以供优化器使用
4、autovacuum launcher使用stats collector后台进程收集统计信息来确定autovacuum候选列表
这是一个非常频繁的操作，脏活、累活都让它给干了

如何开启autovacuum

默认情况下autovacuum是自动开启，但要正常工作还需要开启track_counts，该参数也是默认开启的。

lineqi=# 
select name,setting,short_desc  from pg_settings where name='track_counts'
union all
select name,setting,short_desc from pg_settings where name ='autovacuum';
     name     | setting |                short_desc
--------------+---------+-------------------------------------------
 track_counts | on      | Collects statistics on database activity.
 autovacuum   | on      | Starts the autovacuum subprocess.
1
2
3
4
5
6
7
8

提示：即使关闭该参数也会在需要防止事务ID回卷时发起清理进程，两上参数可以postgresql.conf中修改

autovacuum相关参数

lineqi=# select name,setting,short_desc  from pg_settings where name like 'autovacuum%';
                name                 |  setting   |                                        short_desc
-------------------------------------+------------+-------------------------------------------------------------------------------------------
 autovacuum                          | on         | Starts the autovacuum subprocess.
 autovacuum_analyze_scale_factor     | 0.05       | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
 autovacuum_analyze_threshold        | 50         | Minimum number of tuple inserts, updates, or deletes prior to analyze.
 autovacuum_freeze_max_age           | 1200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
 autovacuum_max_workers              | 5          | Sets the maximum number of simultaneously running autovacuum worker processes.
 autovacuum_multixact_freeze_max_age | 1400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
 autovacuum_naptime                  | 60         | Time to sleep between autovacuum runs.
 autovacuum_vacuum_cost_delay        | 0          | Vacuum cost delay in milliseconds, for autovacuum.
 autovacuum_vacuum_cost_limit        | 10000      | Vacuum cost amount available before napping, for autovacuum.
 autovacuum_vacuum_scale_factor      | 0.02       | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
 autovacuum_vacuum_threshold         | 50         | Minimum number of tuple updates or deletes prior to vacuum.
 autovacuum_work_mem                 | 104448     | Sets the maximum memory to be used by each autovacuum worker process.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

autovacuum触发条件

autovacuum会做两件事件：一是vacuum，二是analyze。触发条件如下：
vacuum:autovacuum_vacuum_scale_factor* num of tuples per table + autovacuum_vacuum_threshold
analyze:autovacuum_analyze_scale_factor* num of tuples per table + autovacuum_analyze_threshold
例如：test表上有1000条记录，当对这个表进行dml操作达到以下阀值是就会进行autovacuum候选列表，等待执行autovacuum操作。
vacuum=10000.02+50=70个tuple
analyze=10000.05+50=100个tuple

autovacuum案例

create table test (userid int ,username varchar(100),crt_time timestamp(6) without time zone)
insert into test select id,'username'||id,now() from generate_series(1,1000) as id
1
2

select * from pg_stat_user_tables where relname=‘test’

场景一：update更新大于70条小于100条记录，查看是否自动执行autovacuum操作
2020-11-05 15:30:40.491624+08
update test set username=‘aa’ where userid<80

结论：autovacuum_naptime 1分钟以内只执行autovacuum操作，而没有执行autoanalzye操作。
场景二：update更新大于100条记录，查看是否自动执行autovacuum、autoanalyze操作
2020-11-05 15:49:48.756724+08
update test set username=‘cc’ where userid<110

结论：autovacuum_naptime 1分钟以内两个操作都执行了。

场景三 ：delete更新大于100条记录，查看是否自动执行autovacuum、autoanalyze操作
2020-11-05 16:25:48.263717+08
delete from test where userid<430 ;

结论：这里测试过三次，每次都是删除100条以下的记录，都会同时执行autovacuum和autoanalyze操作 ，但这里统计信息显示不及时，主要表现在查询pg_stat_user_tables没有及时显示出来。

场景四：delete更新大于70条小于100条记录，查看是否自动执行autovacuum操作
2020-11-05 16:36:20.818849+08
delete from test where userid<520 ;

结论：这里删除了90条记录，结果没有执行autovacuum操作。

autovacuum内部原理

参考资料

postgresql自动清理:http://www.postgres.cn/docs/12/runtime-config-autovacuum.html