Postgresql autovacuum专题

原创龙舌兰地落🌏 All China Database Union 2024-12-23

251

概述

什么是autovacuum?
autovacuum是启动postgresql时自动启动的后台实用程序进程之一，从9.6版本开始引入。

在生产系统中不应该将其设置为关闭。
原因是在PG中，delete和update操作不会自动删除原先的行，会一直保留在数据块中，而形成死元组（死行）。
随着时间的推移，垃圾数据量越来越多，即死元组膨胀，浪费存储空间和内存空间，降低性能。
其他原因参考下一节autovacuum的作用。

参数:

autovacuum = on (默认开启)
track_counts = on(默认开启）

查看当前开启状态：

SHOW autovacuum;

autovacuum作用

为什么需要autovacuum？
1）需要vacumm来移除死元组（清理死行）
2）防止死元组膨胀
3）更新表的统计信息进行分析，以便提供优化器使用（更新统计信息）
4）autovacuum launcher使用stats collector的后台进程收集的信息来确定autovacuum的候选表列表

记录autovacuum

通过此参数来控制：　
log_autovacuum_min_duration
当是-1时，表示不记录（默认），
当是0时，表示记录所有，
250ms or 1s or 1min or 1h or ld :表示记录清除操作时间大于此值的操作（只记录超过额定时间的）。

在表上autovacuum的触发条件

autovacuum进程每隔一分钟执行一次，收集需要做vacuum的表并记录在册。
什么时候在表上做autovacuum？
不会每个delete或update操作都autovacuum，而是达到一定触发条件才进行。
PG内部有一个公式，会计算出哪些表需要做autovacuum。

Autovacuum操作的实际内容：

1）vacuum（空间整理）
2）Analyze（分析）

两个操作的触发条件是不一样的。

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

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

Autovacuum ANALYZE触发条件：
自上次分析以来插入/删除/更新总数超过此阈值的任何表都有资格进行autovacuum分析。
阈值计算公式：

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

举个例子：
表Employee有1000行。
那什么时候轮到表Employee做autovacuum呢？
当下面的条件满足时，Table:employee成为autovacuum Vacuum的候选者

Total number of Obsolete records = (0.2 * 1000) + 50 = 250

意思是说死行超过250则会触发autovacuum的Vacuum操作。

当下面的条件满足时，Table:employee成为autovacuum ANAlYZE 候选者

Total number of Inserts/Deletes/Updates = (0.1 * 1000) + 50 = 150

意思是说增删改行数超过250则会触发autovacuum的ANAlYZE操作，这个操作比vacuum消耗的i/o要低，因为是更新统计信息。

调整autovacuum

可能出现的问题

想象如下场景：
Table1有100行
其触发分析和vacuum的阈值分别是：60和70。
Table2有100万行
其触发分析和vacuum的阈值分别是：100050和200050。
如果两张表都做同样数量的dml操作，T1 触发Autovacuum的次数将是T2的2857倍。
这么看小表操作过于频繁，而大表操作少但一次操作的数据量会很多。
因此PG提供了针对单表Autovacuum的相关参数。

设置表autovacuum阈值参数

如何调整表autovacuum setting的设置？

首先几个阈值参数如下：

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条

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

alter table percona.employee set (autovacuum_vacuum_threshold = 100);
alter table percona.employee set (autovacuum_vacuum_scale_factor=0);
\d+ percona.employee

按如上设置则定位每100条死行进行一次清理。

设置autovacuum_max_workers

一次可以运行多少个autovacuum进程？
在可能包含多个数据库的实例/群集上，一次运行的autovacuum进程数不能超过下面参数设置的值：
autovacuum_max_workers = 3 (默认值)

启动下一个autovacuum之前的等待时间参数
autovacuum_naptime = 1min
注意，在存在多个数据库的情况下，等待时间为autovacuum_naptime/N，其中N是实例中数据库的总数。
一个autovacuum进程每次只能处理一个数据库。

Autovacuum是一个I/O密集型操作
可以把Autovacuum看做一种清洁工作。
可通过设置一些参数，来最小化Autovacuum对I/O的影响：

autovacuum_vacuum_cost_limit : autovacuum可达到的总成本限制(结合所有autovacuum作业)，如果达到这个阈值则休眠（可以理解为做了多少工作，实际上是处理多少数据页）
autovacuum_vacuum_cost_delay : 当一个清理工作达到autovacuum_vacuum_cost_limit指定的成本限制时，autovacuum将休眠数毫秒（中间休息时间）
vacuum_cost_page_hit : 读取已在共享缓冲区中且不需要磁盘读取的页的成本（在内存中找到的代价）
vacuum_cost_page_miss : 获取不在共享缓冲区中的页的成本（在磁盘中找到的代价）
vacuum_cost_page_dirty : 在每一页中发现死元组时写入该页的成本（清理的代价）

上面参数默认的值考虑如下：

autovacuum_vacuum_cost_limit = -1(=vacuum_cost_limit:200)
autovacuum_vacuum_cost_delay = 20ms
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/秒。

谨慎设置autovacuum_max_workers
通常，autovacuum_vacuum_cost_limit成本平均分配给实例中运行的所有Autovacuum过程的autovacuum_max_workers数，（只设置autovacuum_max_workers数没有用，因为没有提高autovacuum可使用的总资源），因此，增加autovacuum_max_workers可能会延迟当前运行的autovacuum workers的Autovacuum的执行；
而增加autovacuum_vacuum_cost_limit可能会导致I/O瓶颈；

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

定位需要调整autovacuum参数的表

如何确定哪些表需要调整其autovacuum setting?
为了单独调整表的autovacuum，必须知道一段时间内表上的插入/删除/更新数。

SELECT n_tup_ins as "inserts"
  ,n_tup_upd as "updates"
  ,n_tup_del as "deletes"
  ,n_live_tup as "live_tuples"
  , n_dead_tup as "dead_tuples"
FROM pg_stat_user_tables
WHERE schemaname = 'scott' 
  and relname = 'employee';

下面的例子列出了需要执行VACUUM操作的前50个表：

SELECT c.oid::regclass
, age(c.relfrozenxid)
, pg_size_pretty(pg_total_relation_size(c.oid))
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm')
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 50;

一些实用命令

查看相关信息：

select * from pg_stat_user_tables where relname='tbname';
SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables where relname='tbname';

可以看到活行和死行，根据比例推算。

手动vacuum：

vacuum table_name;

这种不会回收空间，要回收空间要vacuum full，但无法在生产环境的业务繁忙期执行。
参考：

https://mp.weixin.qq.com/s?__biz=MzU5MTQ3ODY3MQ==&mid=2247496083&idx=6&sn=36debb074df418246cf5730fc249d2b2&chksm=fe2cdb73c95b5265c4dce4e2b6502199909e0ae4216380d67dc5822ae4db3d55268464a53976&scene=27

手动更新表统计信息：

ANALYZE table_name;

手动更新库统计信息：

ANALYZE;