PostgreSQL Autovacuum拾遗__本文介绍了PostgreSQL的Autovacuum机制中容易出现误解的地方，包括Autovacuum的调度机制、Autovacuum的监控以及相关的数据字典和数据库参数等。: 世界上功能最强大的开源数据库...

### **作者简介**
#### 何小栋，从事产品研发和架构设计工作，ITPUB数据库版块资深版主，对PostgreSQL数据库及其源代码有深入研究。现就职于广州云图数据技术有限公司，系统架构师，博客：http://blog.itpub.net/6906/

#### 本文介绍了PostgreSQL的Autovacuum机制中容易出现误解的地方，包括Autovacuum的调度机制、Autovacuum的监控以及相关的数据字典和数据库参数等。

### **一、Autovacuum按照参数autovacuum_naptime设定的时间进行调度吗？**
#### 看到这个问题，很多人的第一反应是：这不是废话？PostgreSQL不是已经提供了参数autovacuum_naptime对autovacuum进行调度吗？
#### 这个问题的答案是，也不是。对于PostgreSQL中每个需要清理的db而言，答案确实是按该参数的设定启动worker进行处理（前提是有空闲的worker存在）；但对于整个database cluster来讲，假设有n个db需要清理，那么每隔autovacuum_naptime/n就会启动worker来对相应的db进行处理，实例实际的autovacuum调度时间是t=autovacuum_naptime/n。如下图所示，如果有n个db需要清理，那么autovacuum_naptime 将被分成n等份，每一份的间隔是t，图中环上每一个绿点分配给一个db。

![CENTER_PostgreSQL_Community]( /images/news/2020/20200120_1342_图片2012.1_.20_.png)
####  图一：autovacuum时间调度

#### 下面是一个简单的测试场景，创建3个数据库，db1 -> db3，每个数据库每隔10s执行全表更新，通过日志观察autovacuum的行为。
```
-- session 1
\c db1
drop table t_autovacuum_db1;
create table t_autovacuum_db1(id int,c1 varchar(20),c2 varchar(20));
insert into t_autovacuum_db1 select x,'c1'||x,'c2'||x from generate_series(1,500000) as x;
 
do
$$
declare
begin
  for i in 1..20 loop
    update t_autovacuum_db1 set id = i;
    commit;
    perform pg_sleep(10);
  end loop;
end
$$;
-- session 2
\c db2
-- 与db1类似
-- session 3
\c db3
-- 与db1类似
```

#### 对应的日志输出
```
2019-12-11 15:34:05.298 CST,,,1870,,5df09bec.74e,1,,2019-12-11 15:34:04 CST,6/37,3406,LOG,00000,"automatic analyze of table ""db2.public.t_autovacuum_db2"" system usage: CPU: user: 0.38 s, system: 0.01 s, elapsed: 0.52 s",,,,,,,,,""
2019-12-11 15:34:20.528 CST,,,1884,,5df09bfb.75c,1,,2019-12-11 15:34:19 CST,6/41,3412,LOG,00000,"automatic analyze of table ""db1.public.t_autovacuum_db1"" system usage: CPU: user: 0.51 s, system: 0.01 s, elapsed: 0.71 s",,,,,,,,,""
2019-12-11 15:34:35.482 CST,,,1897,,5df09c0a.769,1,,2019-12-11 15:34:34 CST,6/45,3416,LOG,00000,"automatic analyze of table ""db3.public.t_autovacuum_db3"" system usage: CPU: user: 0.40 s, system: 0.01 s, elapsed: 0.61 s",,,,,,,,,""
2019-12-11 15:35:05.904 CST,,,1924,,5df09c28.784,1,,2019-12-11 15:35:04 CST,6/51,0,LOG,00000,"automatic vacuum of table ""db2.public.t_autovacuum_db2"": index scans: 0
pages: 0 removed, 25467 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 500149 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3422
buffer usage: 50964 hits, 2 misses, 6 dirtied
avg read rate: 0.015 MB/s, avg write rate: 0.044 MB/s
system usage: CPU: user: 0.43 s, system: 0.00 s, elapsed: 1.07 s",,,,,,,,,""
……
```

#### 从日志可见，PG认为目前有4个数据库（除上述3个数据库还有一个用于测试的数据库）需要处理，每隔15s（60s/4）调度一个数据库，每个数据库间隔60s调度一次。

#### 不需要做实验，我们也完全可以想象，如果有数十甚至上百个db，而autovacuum_naptime设置为较小的值如使用默认参数（60s），那么worker启动的间隔时间只有几秒甚至更短，如果没有空闲的worker或者worker不能及时完成并退出，实际db清理调度间隔将会大于调度时间t。

### **二、提高autovacuum_max_workers有用吗？** 
#### 我们凭直觉，提升autovacuum_max_workers意味着有更多的worker执行清理工作，自然可以提高性能。但实际上，提高autovacuum_max_workers不一定会有用。

#### 1、如果只有一个活跃业务db，那么不管设定的autovacuum_max_workers多大，永远只会启动一个worker来对该db进行清理，要想提升性能只能通过多线程的方式同时对多个表进行处理。但目前来看autovacuum worker进程并没有启动多线程并行处理；

#### 2、如果有多个活跃业务db，单纯提高autovacuum_max_workers而不提高autovacuum_vacuum_cost_limit（默认值为vacuum_cost_limit，即200）的值也没有用处。原因是autovacuum_vacuum_cost_limit对成本的限制是全局的，该参数控制了PostgreSQL实例所有worker在运行时可达到的“成本”上限，其计算公式为：autovacuum_vacuum_cost_limit/autovacuum_max_workers。一旦达到成本上限，worker就会进入休眠，sleep时间为参数vacuum_cost_delay所设定的值（PG 12该参数默认为2ms，PG 11该参数默认为20ms），然后再继续处理。打个比方，一开始有3台货车运送货物，每台货车限重2吨，现在增加3台货车总共6台，但每台货车限重1吨，实际吞吐量跟原来3台限重2吨的吞吐量其实没有什么区别。
 
### **三、设定好系统参数是否就可以高枕无忧了？**
#### 很可惜，答案是否定的。由于autovacuum执行vacumm需要获取Relation的SHARE UPDATE EXCLUSIVE lock，如果业务db非常频繁，vacumm很有可能获取不到lock而跳过不处理。对于这种情况，可通过查询PostgreSQL提供的pg_stat_user_tables获得相关信息。如果last_autovacuum列显示的时间与调度时间不吻合并且与当前时间间隔较大，那么很可能是因为获取不到lock而无法执行vacumm。
```
SELECT schemaname, relname, last_autovacuum
FROM pg_stat_user_tables;
```
 
### **四、系统参数autovacuum已设置为on了，但实际上为什么没有执行autovacuum？**

#### 实际没有执行autovacuum的原因是影响执行的参数除了系统参数autovacuum外，还有track_counts参数。如该参数设置为禁用，PostgreSQL的statistics collector不会更新Relation的dead rows信息，而这些信息是PostgreSQL确定是否执行autovacuum所依赖的。

#### 下面是一个简单的测试场景：把track_counts设置为off，PG没有采集dead tuples，因此不会启动autovacuum对relation进行清理。
```
-- 设置为off
[local:/data/run/pg12]:5120 pg12@testdb=#  set track_counts=off;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 3;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=#  SELECT schemaname, relname, n_dead_tup FROM pg_stat_user_tables where relname='t1';
 schemaname | relname | n_dead_tup
------------+---------+------------
 public     | t1      |          0
(1 row)
-- 设置为on
[local:/data/run/pg12]:5120 pg12@testdb=#  set track_counts=on;
SET
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 3;
UPDATE 80000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup FROM pg_stat_user_tables where relname='t1';
 schemaname | relname | n_dead_tup
------------+---------+------------
 public     | t1      |     160000
(1 row)
```
#### 另外，PostgreSQL支持在表级上设置autovacuum是否生效，如autovacuum_enabled设置为false，则不会对该relation执行清理。
```
[local:/data/run/pg12]:5120 pg12@testdb=# create table t2(id int);
CREATE TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# alter table t2 SET (autovacuum_enabled = false);
ALTER TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT reloptions FROM pg_class WHERE relname='t2';
         reloptions         
----------------------------
 {autovacuum_enabled=false}
(1 row)
```
 
### **五、参考资料**
####1、PgSQL · 源码分析 · AutoVacuum机制之autovacuum worker
#### <http://mysql.taobao.org/monthly/2018/02/04/>
 
#### 2、Monitoring PostgreSQL VACUUM processes
#### <https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/>

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)