目录
一、简述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瓶颈;
因此可以通过设置单个表的存储参数来重写此行为,这样可以忽略全局设置;