postgresql回收机制autovacuum

postgresql autovacuum

delete操作行不会立即从数据文件中删除，而是通过xmax字段将其标记删除
update操作可能在postgresql中被视为delete+insert

dead tuples

select schemaname,relname,n_dead_tup,
(case when n_live_tup>0 then n_dead_tup::float8/n_live_tup::float8
else 0 end) aa
from pg_stat_all_tables;

vacuum

用法1：vacuum tb_name
手动操作，扫描表并从表和索引中删除死元组，但是不会将磁盘空间返回到操作系统，而将用于新行，CPU和IO使用率升高

用法2：vacuum full table_name
将回收空间返回给操作系统，但会锁表（独占锁），阻止所有操作，如select。其次，实际是创建一个表的副本，使所需的磁盘空间加倍，同时复制表非常慢

analyze

analyze table_name
手动执行，效率差，每次执行时都是从头开始重建统计数据

autoanalyze：自动执行

autovacuum优点
1）清理死元组
2）更新优化程序规划查询时使用的数据分布统计信息
3）后台运行的维护任务，对用户查询的影响较小，消耗的CPU和IO也相对较小

autovacuum相关参数

##参看参数设置
select * from pg_settings where name like '%vacuum%';

autovacuum的工作成本

• autovacuum_vacuum_cost_delay
• autovacuum_vacuum_cost_limit

触发autovacuum的参数

• autovacuum_vacuum_threshold:设置当表上的被更新的元组数的阈值
• autovacuum_vacuum_scale_factor:设置表大小的缩放系数
• autovacuum:是否启动系统自动清理功能，默认on
• autovacuum_max_workers:设置系统自动清理工作进程的最大数量
• autovacuum_naptime:设置两次系统自动清理操作之间的间隔时间
• autovacuum_freeze_max_age:设置需要强制对数据库进行清理的XID上限值

触发条件
1.表上（update,delete记录）>=autovacuum_vacuum_scale_factor* reltuples(表上记录数) + autovacuum_vacuum_threshold

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

##查看表事务年龄
select relname,age(relfrozenxid) from pg_class where relname='tb_name';

a.可以单独设置每个表
alter table tb_name set with(参数设置值);

b.新创建表
create table tb_name(...) with(参数设置值);

c.可以关闭某个表的autovacuum功能
alter table tb_name set(autovacuum_enabled=false);

注意：
1.一般不建议单独设置，管理起来比较困难和复杂
2.autovacuum_max_workers数值设置建议为CPU核数/3。如果CPU资源充，I/O性能较好时，可以适当调大
3.autovacuum_freeze_max_age达到最大值可能出现的问题，如果冻结操作很慢，导致事务id耗尽，最终会导致数据库拒绝所有事务的执行，指导冻结操作结束。版本pg9.6之后对已经全部是冻结的行的数据块，不再进行冻结处理。
4.autovacuum_freeze_max_age不会等到到达这个限制之后才进行冻结，默认情况下，autovacuum_freeze_max_age*95%的事务数量时候开始进行冻结操作。

##查询每个表的xid使用程度
SELECT c.oid::regclass as tb_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age 
FROM pg_class c 
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid 
WHERE c.relkind IN ('r', 'm') 
order by age desc;

##查看当前数据库的autovacuum情况
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup/(n_live_tup* current_setting('autovacuum_vacuum_scale_factor')::float8+ current_setting('autovacuum_vacuum_threshold')::float8) DESC
LIMIT 10;

pg_stat_all_tables：记录当前数据库中所有表的统计信息

字段	描述
relid	表oid
schemaname	模式名
relname	表名
seq_scan	表顺序扫描次数
seq_tup_read	表顺序扫描返回行数
idx_scan	索引扫描次数
idx_tup_fetch	索引扫描返回行数
n_tup_ins	插入行数
n_tup_upd	更新行数，包含hot update
n_tup_del	删除行数
n_tup_hot_upd	热更新行数
n_live_tup	活元组行数
n_dead_tup	死元组行数
n_mod_since_analyze	上次last analyzed修改的行数
last_vacuum	上次执行vacuum的时间
last_autovacuum	上次执行autovacuum的时间
last_analyze	上次执行analyze的时间
vacuum_count	vacuum的行数
autovacuum_count	autovacuum的行数
analyze_count	analyze的行数
autoanalyze_count	autoanalyze的行数

参考文档：
https://blog.csdn.net/kmblack1/article/details/84953517
https://www.sohu.com/a/287094621_505827
https://blog.csdn.net/liuhuayang/article/details/112211102