autovacuum介绍

由于PostgreSQL的MVCC机制，会产生许多失效
的行数据，我们可以通过手工对表进行vacuum或者vacuum full来进行清理，或者配置PostgreSQL内置的autovacuum进程来自动管理。

在运维层面，autovacuum进行清理的步骤如下：

1. 获得需要vacuum的列表

   事务提交/回滚时会发消息给进程pgstat，pgstat会汇总这份信息并记录到文件中，autovacuum launcher会定期读取文件，获得，当某个表的改动超过阈值时便会触发一次统计信息的更新操作。

2. 获取表上的ShareUpdateExclusiveLock锁

3. 根据配置的autovacuum参数，对表执行vacuum analyze操作，进行低延迟的vacuum ;

4. 由于清理时执行了vacuum ananlyze，完成后也会更新相关表的统计数据

autovacuum相关参数

开启autovacuum

autovacuum：on（默认）

track_counts：on（默认）

启用在数据库活动上的统计收集。这个参数默认为打开，因为自动清理守护进程需要被收集的信息。只有超级用户可以更改这个设置。

autovacuum触发条件参数

两个条件

1. 当update,delete的tuples数量超过清理阈值
   

   清理阈值 = 清理基本阈值 + 清理缩放系数 * 元组数

   autovacuum_vacuum_threshold + pg_class.reltuples *autovacuum_vacuum_scale_factor
   清理基本阈值为：autovacuum_vacuum_threshold
   清理缩放系数为：autovacuum_vacuum_scale_factor 
   元组数为：pg_class.reltuples

2. 指定表上事务的最大年龄配置参数autovacuum_freeze_max_age，默认为2亿，达到这个阀值将触发 autovacuum进程，从而避免 wraparound。

autovacuum_vacuum_threshold

指定能在一个表上触发VACUUM的被插入、被更新或被删除元组的最小数量。默认值为50个元组(行)。

autovacuum_vacuum_scale_factor

指定一个表记录数的比例因子，默认值为0.2（20%）

所以，每当死亡元组数（pg_stat_all_tables.n_dead_tup的值）超过清理阈值时，就会触发autovacuum自动清理。

一般在dead tuple达到20%时，会进行自动清理，50行的阈值是为了防止非常频繁地清理微小的表 。这个默认的比例比较适用于中小表，但如果表较大时，比如10GB大小的表，dead tuple达到2GB时才清理，这在清理的过程中会严重影响性能，一般来说解决方案有两种：

• 一是调小大表的比例因子autovacuum_vacuum_scale_factor

• 二是放弃比例因子，调大阈值

通常都会对大表进行单独的autovacuum触发机制如下：

• 设置autovacuum_vacuum_scale_factor = 0

• 然后根据各个表的delete和update频繁程度以及表的数据量单独为每个表设置阈值（例如设置autovacuum_vacuum_threshold = 10000）

ALTER TABLE test SET (autovacuum_vacuum_threshold = 10000);
ALTER TABLE test SET (autovacuum_vacuum_scale_factor  = 0);

autovacuum_freeze_max_age

指定在一个VACUUM操作被强制执行来防止表中事务ID回卷之前，一个表 的pg_class.relfrozenxid域能保持的最大年龄（事务的）。注意即便自动清理被禁用， 系统也将发起自动清理进程来阻止回卷。清理也允许从pg_xact子目录中移除旧文件，这也是为什么默认值被设置为较低的2亿事务。该参数只能在服务器启动时设置，但是对于个别表可以通过修改表存储参数来降低该设置。

autovacuum_multixact_freeze_max_age

指定在一个VACUUM操作被强制执行来防止表中多事务ID回卷之前，一个表 的pg_class.relminmxid域能保持的最大年龄（多事务的）。注意即便自动清理被禁用， 系统也将发起自动清理进程来阻止回卷。清理多事务也允许从pg_multixact/members和pg_multixact/offsets子目录中移除旧文 件，这也是为什么默认值被设置为较低的400万多事务。该参数只能在服务器启动时设 置，但是对于个别表可以通过修改表存储参数来降低该设置

autovacuum运行资源参数

autovacuum是在后台运行的维护任务，需要对用户查询的影响最小。可以通过配置合适的参数来限制持续vacuum的消耗资源(io、cpu等)。

autovacuum的清理过程是从数据文件中读取页面（默认8kB数据块），并检查它是否需要清理，如果没有死亡元组，页面就会被丢弃而不做任何更改，否则它被清理（死元组被删除）,被标记为“脏页”并最终写出来。消耗

autovacuum成本核算基于以下三个参数：

vacuum_cost_page_hit = 1

如果页面是从shared_buffers读取的，则计为1。

vacuum_cost_page_miss = 10

如果在shared_buffers找不到并且需要从操作系统中读取，则计为10(它 可能仍然从RAM提供,但我们不知道)

vacuum_cost_page_dirty = 20

当清理修改一个之前干净的块时需要花费的估计代价，它表示再次把脏块刷 出到磁盘所需要的额外I/O,默认值为20

autovacuum_vacuum_cost_delay = 20ms

每次完成清理后睡眠20ms，如果值为-1那采用vacuum_cost_delay

autovacuum_vacuum_cost_limit = 200

完成一次清理的消耗限制，如果值为-1那采用vacuum_cost_limit

autovacuum是从表数据库文件中一个个页面读取(PostgreSQL的blocksize默认是8k)，配置参数如下：

vacuum_cost_page_hit = 1 
vacuum_cost_page_miss = 10 
vacuum_cost_page_dirty = 20 
autovacuum_vacuum_cost_delay = 20ms 
autovacuum_vacuum_cost_limit = 200

由上可知延迟是20ms，则每秒可以清理50轮，乘以200后，即为10000的成本，那么可以算出对磁盘的消耗为：

• shared_buffers读取是 10000/18KB = 80MB/s

• os中读取是 10000/10*8KB = 8MB/s

• vacuum写入是 10000/20*8KB = 4MB/s

如果硬盘顺序读写IO能力较好，可以将cost_limit参数增加到1000或2000，这将使吞吐量增加5倍或10倍。

或者调小睡眠时间也可以达到autovacuum性能提升，但会持续占用IO，加大磁盘繁忙度。

autovacuum_naptime

内置autovacuum调度的间隔的时间，默认是60秒。

naptime会被分配到每个DB上， 如果你有4个数据库且naptime值为1分钟，则vacuum进程将在新数据库中每20秒运行一次 。

autovacuum_max_workers

设置系统自动清理工作进程的最大数量 ，默认值：3。CPU核多、并且IO好的情况下可多点，但也不是越多越好，如果vacuum_cost_limit给得不够，太多的workers反而是浪费。

autovacuum_work_mem

每个worker可使用的最大内存数，默认值为 -1，表示转而使用 maintenance_work_mem的值。

maintenance_work_mem

指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存量，默认值64MB。内存设置越大效率就越高，但也不是越大越好，太大反而会浪费内存，如果内存资源充足，可使用1GB。

相关查询

更改系统autovacuum相关参数

ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.03;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 300;
ALTER SYSTEM SET autovacuum_analyze_threshold = 300;
select pg_reload_conf ();

更改单表autovacuum相关参数

alter table tableA set (autovacuum_vacuum_scale_factor=0.03);

或者在创建表时指定

create table tmp(id int ,name varchar(100)) with(autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=100);

查看表死元组以及autovacuum信息的语句

select * from pg_catalog.pg_stat_all_tables where relname='tmp';

查看进行autovacuum中的表

SELECT p.pid,
      p.datname,
      p.query,
      p.backend_type,
      a.phase,
      a.heap_blks_scanned  a.heap_blks_total::float * 100 AS "% scanned",
      a.heap_blks_vacuumed  a.heap_blks_total::float * 100 AS "% vacuumed",
      pg_size_pretty(pg_table_size(a.relid)) AS "table size",
pg_size_pretty(pg_indexes_size(a.relid)) AS "indexes size",
      pg_get_userbyid(c.relowner) AS owner
 FROM pg_stat_activity p
 JOIN pg_stat_progress_vacuum a ON a.pid = p.pid
 JOIN pg_class c ON c.oid = a.relid
WHERE p.query LIKE 'autovacuum%';

小结

1. autovacuum基于track_counts和analyze的统计信息才能进行

2. autoanalyze跟autovacuum一样也有单独配置的参数，原理一样，通常autoanalyze触发阀值要比autovacuum小，autoanalyze收集表的统计信息达到阀值才会进行autovacuum

• autovacuum_analyze_scale_factor=0.1

• autovacuum_analyze_threshold=50

3. autovacuum时运行vacuum analyze，即vacuum同时也会analyze

4. 手工vacuum时，影响参数是：（即没有autovacuum开头，其它同理）

   vacuum_cost_page_hit 
   vacuum_cost_page_miss
   vacuum_cost_page_dirty
   vacuum_cost_delay
   vacuum_cost_limit 
   maintenance_work_mem

   
   

参考文档

https://cdn.modb.pro/db/238547
https://mp.weixin.qq.com/s/bPuaq1fjIZacDizk_Mn7BA
https://mp.weixin.qq.com/s/MxvrXFfCkK6smVc6novEWA