PostgreSQL（十七）Autovacuum工具

目录

一、简述Autovacuum

1、什么是Autovacuum

2、记录Autovacuum操作的设定

二、Autovacuum的操作内容&时间

1、Autovacuum的操作内容

2、Autovacuum的操作时间

三、调整Autovacuum

1、Autovacuum相关参数：

2、调整表Autovacuum setting的设置

三、autovacuum_max_workers

1、概述

2、Autovacuum是一个I/O密集型操作

3、谨慎设置autovacuum_max_workers

---

一、简述Autovacuum

1、什么是Autovacuum

Autovacuum是PG的一个后台进程，在9.6版本之后才出现，在PG实例启动时自动启动；

每隔一分钟执行一次自动收集需要做Vacuum操作的表；

当表的操作记录满足指定条件时，自动执行Vacuum操作；

2、记录Autovacuum操作的设定

参数：log_autovacuum_min_duration

-1 =不记录；

0 =记录所有；

‘250ms’、1s、1min、1h、1d =记录真空操作时间大于此设定值的操作

二、Autovacuum的操作内容&时间

1、Autovacuum的操作内容

Autovacuum进程每隔一分钟执行一次，收集需要做Vacuum操作的表并记录在册；

当确认表的操作已满足规定条件时，才会自动进行Autovacuum操作，Autovacuum操作可分为两个部分：

（1）Vacuum

需要Vacuum操作移除死元组；防止死元组膨胀；

（2）Analyze

更新表的统计信息进行分析，以便提供优化器使用；

Autovacuum launcher使用Stats Collector的后台进程收集的信息来确定Autovacuum的候选表列表。

2、Autovacuum的操作时间

（1）Autovacuum Vacuum触发条件

如果由于更新和删除，表中的实际死元组数超过此有效阈值，则该表将成为Autovacuum的候选表，计算公式如下：

Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor * number of tuples+autovacuum_vacuum_threshold

（2）Autovacuum Analyze触发条件

自上次分析以来，增删改总数超过此阈值的任何表，都可进行Autovacuum分析，计算公式如下：

Autovacuum ANALYZE thresold for a table = autovacuum_analyze_scale_factor * number of tuples+autovacuum_analyze_threshold

#举个栗子：

假如：EMP表中有1000行数据，参数设置如下：
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_threshold =50
autovacuum_analyze_threshold=50

则：
（1）表emp成为Autovacuum Vacuum操作对象的触发条件为：
Total number of Obsolete record=（0.2*1000）+50=250             //表中死元组超过250行时进行Vacuum

（2）表emp成为Autovacuum Analyze操作对象的触发条件为：
Total number of Insert/Delete/Updates=（0.1*1000）+50=150        //表增删改行数超过150行时进行分析

由上可知，一般来说，analyze比Vacuum频繁，这是为了及时向优化器更新信息，以供参考；
analyze的代价比Vacuum操作低；

三、调整Autovacuum

1、Autovacuum相关参数：

#开启/关闭参数；在生产系统中不建议将其关闭

Autovacuum=on         #on by default

track_counts=on         #on by default

#阈值参数

autovacuum_vacuum_scale_factor=0.2     #Vacuum操作的比例，缺省0.2

autovacuum_vacuum_threshold=50         #Vacuum操作的弹性值（权重），缺省50条

autovacuum_analyze_scale_factor =0.1    #分析操作的比例，缺省0.1

autovacuum_analyze_threshold=50         #分析操作的弹性值（权重），缺省50条

2、调整表Autovacuum setting的设置

        在数据库的日常管理中，经常会出现全局设置适配大部分业务表的情况，但对个别表的性能存在影响。

        PG支持通过设置单个表的存储参数来重写此行为，会使其忽略全局设置，根据表的大小、操作频率、操作类型等来特殊控制表被Autovacuum的频率，实现精细化管理：

alter table XXX set(autovacuum_vacuum_threshold=XXX);

alter table XXX set(autovacuum_vacuum_scale_factor=XXX);

#举个栗子

假设表为：public.table1
alter table public.table1 set(autovacuum_vacuum_threshold=100);
alter table public.table1 set(autovacuum_vacuum_scale_factor=0);

\d+ public.table1
此时，只要有超过100条过时的记录，就会运行Autovacuum Vacuum

三、autovacuum_max_workers

1、概述

（1）一次可以运行多少个Autovacuum进程

        autovacuum_max_workers =3        （缺省值）

        #Autovacuum的工作进程个数参数，意为一次可以运行多少个Autovacuum进程，可以使用此参数，对工作进程进程管理。

        此参数运用在可能包含多个数据库的实例/群集上；

        一次运行的Autovacuum进程数不能超过此参数的设定值，缺省值为3；

        例如：加入有5个数据库，3个进程只能同时处理3个数据库的工作，等其中之一整理好后，再整理下一个数据库；

（2）启动下一个Autovacuum之前的等待时间：

autovacuum_naptime =1min      （缺省值）

#在存在多个数据库的情况下，等待时间为autovacuum_naptime/N ，N为实例中数据库的总数

2、Autovacuum是一个I/O密集型操作

可以把他看做一种清洁工作。

可通过设置一些参数，来最小化Autovacuum对I/O的影响

autovacuum_vacuum_cost_limit	-1 (=vacuum_cost_limit :200)	Autovacuum可达到的总成本限制（结合所有Autovacuum作业） 到达这个成本之后，worker进程会进入休眠
autovacuum_vacuum_cost_delay	20ms	当一个清理工作达到 autovacuum_vacuum_cost_limit指定的成本限制时，Autovacuum将休眠的时间（毫秒）
vacuum_cost_page_hit	1	读取已在共享缓冲区中，且不需要磁盘读取的页的成本
vacuum_cost_page_miss	10	获取不在共享缓冲区中的页的成本
vacuum_cost_page_dirty	20	在每一页中发现死元组(垃圾整理)时写入该页的成本

• 根据以上参数及缺省值，模拟1秒后发生的事件（1000毫秒）

在读取延迟为0毫秒的最佳情况下， autovacuum可以唤醒并进入睡眠50次(1000毫秒/20毫秒)，因为唤醒之间的延迟需要20毫秒。
    1 second = 1000 milliseconds = 50 * autovacuum_vacuum_cost_delay

由于在共享缓冲区中每次读取一个页面的相关成本是1，因此在每个唤醒中可以读取200个页面（因为上面把总成本限制设置为200），在50个唤醒中可以读取50*200个页面

如果在共享缓冲区中找到了所有具有死元组的页，并且 autovacuum代价延迟为20毫秒，则它可以在每一轮中读取：((200/ vacuum_cost_page hit)*8)KB，这需要等待autovacuun代价延迟时间量。

因此，考虑到块大小为8192字节， Autovacuum最多可以读取：
    50*200*8kb=78.13 MB/s(如果在共享缓冲区中已经找到块)。

如果块不在共享缓冲区中，需要从磁盘提取，则 autovacuum可以读取:
    50*(200/vacuum cost_ _page_miss)*8)KB=7.81 MB/秒。

现在，为了从页/块中删除死元组，写操作的开销是: vacuum_cost_page_dirty，默认设置为20

一个 auto vacuum每秒最多可以写/脏：50*(200/vacuum_cost_page dirty)*8) KB=39MB/秒

3、谨慎设置autovacuum_max_workers

        通常，autovacuum_vacuum_cost_limit成本平均分配给实例中运行的所有Autovacuum过程的

autovacuum_max_workers数；

     因此，增加autovacuum_max_workers可能会延迟当前运行的autovacuum workers的Autovacuum的执行；

        而增加autovacuum_vacuum_cost_limit可能会导致I/O瓶颈；

        因此可以通过设置单个表的存储参数来重写此行为，这样可以忽略全局设置；