pg vacuum小实验

调整PG参数，尽快模拟vacuum，进入freeze,vacuum等情况

参数调整

2C8G VM环境

vi  /var/lib/pgsql/15/data/postgresql.conf
synchronous_commit=off
shared_buffer=2GB
effective_cache_size=6GB
autovacuum = on  
maintenance_work_mem=256MB

autovacuum_naptime = 5 min

#table need vacuum ：50000+0.1 tuples
#pg_stat_all_tables.n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × pg_class.reltuples
autovacuum_vacuum_scale_factor=0.1
autovacuum_vacuum_threshold=50000

#table need analyze:10000+0.1 tuples
#autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × pg_class.reltuples
autovacuum_analyze_scale_factor=0.1
autovacuum_analyze_threshold=10000

#table need aggressive vacuum:
#vacuum_freeze_table_age < autovacuum_freeze_max_age* 0.95
vacuum_freeze_table_age = 150000
autovacuum_freeze_max_age=200000
vacuum_freeze_min_age = 50000

#
autovacuum_multixact_freeze_max_age=250000
vacuum_multixact_freeze_table_age=250000
#checkpoint
#checkpoint_timeout=300000
#max_wal_size=1GB
#checkpoint_completion_target=0.9

禁止autovacuum

autovacuum=off

重启

systemctl restart postgresql-15

模拟数据产生

即使设置auto_vacuum为OFF，如果迫切需要阻止事务环绕 ID，PostgreSQL 也会启动一个Autovacuum进程

建表

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR
);

模拟事务存储过程

CREATE OR REPLACE PROCEDURE insert_trx (trx_num integer)  LANGUAGE plpgsql AS $$
DECLARE 
v_step INT := 0;
begin
  while v_step < trx_num loop
    INSERT INTO test_table (name) SELECT 'test' || v_step;
    commit;
    v_step:=v_step+1;
  end loop;
end;
$$ ;

CREATE OR REPLACE PROCEDURE delete_trx (trx_num integer)  LANGUAGE plpgsql AS $$
DECLARE 
v_step INT := 0;
v_start INT := 0;
begin
  select min(id) into  v_start from test_table;
  while v_step < trx_num loop
   
    delete from test_table  WHERE id = v_start+v_step;
    commit;
    v_step:=v_step+1;
  end loop;
end;
$$ ;


CREATE OR REPLACE PROCEDURE update_trx (trx_num integer)  LANGUAGE plpgsql AS $$
DECLARE 
v_step INT := 0;
v_start INT := 0;
begin
  select min(id) into  v_start from test_table;
  while v_step < trx_num loop
    
    UPDATE test_table SET name = 'update '||name||v_step WHERE id = v_start+v_step;
    commit;
    v_step:=v_step+1;
  end loop;
end;
$$ ;

关闭autovacuum

插入数据

truncate table test_table;
call insert_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

 relfrozenxid |  age
--------------+-------
    166611169 | 50001

\x
select * from pg_stat_all_tables where relname ='test_table';
\x

postgres=# select * from pg_stat_all_tables where relname ='test_table';
-[ RECORD 1 ]-------+-----------
relid               | 16466
schemaname          | public
relname             | test_table
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 50000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 50000
n_dead_tup          | 0
n_mod_since_analyze | 50000
n_ins_since_vacuum  | 50000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
-[ RECORD 1 ]+----------
relfrozenxid | 166611169
age          | 100001
\x
select * from pg_stat_all_tables where relname ='test_table';
\x

-[ RECORD 1 ]-------+-----------
relid               | 16466
schemaname          | public
relname             | test_table
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 50001
idx_tup_fetch       | 50001
n_tup_ins           | 50000
n_tup_upd           | 50000
n_tup_del           | 0
n_tup_hot_upd       | 28653
n_live_tup          | 50000
n_dead_tup          | 21348
n_mod_since_analyze | 100000
n_ins_since_vacuum  | 50000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0


call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

 relfrozenxid |  age
--------------+--------
    166611169 | 150001

\x
select * from pg_stat_all_tables where relname ='test_table';
\x

-[ RECORD 1 ]-------+-----------
relid               | 16466
schemaname          | public
relname             | test_table
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 100002
idx_tup_fetch       | 100002
n_tup_ins           | 50000
n_tup_upd           | 100000
n_tup_del           | 0
n_tup_hot_upd       | 73976
n_live_tup          | 50000
n_dead_tup          | 26310
n_mod_since_analyze | 150000
n_ins_since_vacuum  | 50000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
 relfrozenxid |  age
--------------+--------
    166611169 | 200001

\x
select * from pg_stat_all_tables where relname ='test_table';
\x

-[ RECORD 1 ]-------+-----------
relid               | 16466
schemaname          | public
relname             | test_table
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 150003
idx_tup_fetch       | 150003
n_tup_ins           | 50000
n_tup_upd           | 150000
n_tup_del           | 0
n_tup_hot_upd       | 115066
n_live_tup          | 50000
n_dead_tup          | 35177
n_mod_since_analyze | 200000
n_ins_since_vacuum  | 50000
last_vacuum         |
last_autovacuum     |
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 0
analyze_count       | 0
autoanalyze_count   | 0

call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

 relfrozenxid |  age
--------------+--------
    166611169 | 200001

\x
select * from pg_stat_all_tables where relname ='test_table';
\x

-[ RECORD 1 ]-------+------------------------------
relid               | 16466
schemaname          | public
relname             | test_table
seq_scan            | 2
seq_tup_read        | 0
idx_scan            | 200004
idx_tup_fetch       | 200004
n_tup_ins           | 50000
n_tup_upd           | 200000
n_tup_del           | 0
n_tup_hot_upd       | 159441
n_live_tup          | 49999
n_dead_tup          | 5920
n_mod_since_analyze | 250000
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     | 2024-04-05 22:06:38.238202+08
last_analyze        |
last_autoanalyze    |
vacuum_count        | 0
autovacuum_count    | 1
analyze_count       | 0
autoanalyze_count   | 0
);;

当table age达到：autovacuum_multixact_freeze_max_age,即便自动清理被禁用，系统也将发起自动清理进程来阻止回卷

lazy 模式vacuum

开启autovacuum

autovacuum=on

启动

systemctl restart postgresql-15

插入数据

truncate table test_table;
call insert_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
 relfrozenxid |  age
--------------+-------
    170274876 | 50001

select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table';

  relname   | n_mod_since_analyze | n_dead_tup |        last_autovacuum        |       last_autoanalyze
------------+---------------------+------------+-------------------------------+-------------------------------
 test_table |                   0 |          0 | 2024-04-05 23:01:11.884061+08 | 2024-04-05 23:01:11.967404+08


call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

 relfrozenxid |  age
--------------+--------
    170274876 | 100001

select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table';

  relname   | n_mod_since_analyze | n_dead_tup |        last_autovacuum        |       last_autoanalyze
------------+---------------------+------------+-------------------------------+-------------------------------
 test_table |                   0 |      21348 | 2024-04-05 23:01:11.884061+08 | 2024-04-05 23:34:05.083539+08
 


call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

 relfrozenxid |  age
--------------+--------
    170274876 | 150003

select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table';
   relname   | n_mod_since_analyze | n_dead_tup |        last_autovacuum        |       last_autoanalyze
------------+---------------------+------------+-------------------------------+-------------------------------
 test_table |                   0 |      26284 | 2024-04-05 23:01:11.884061+08 | 2024-04-05 23:44:25.295981+08

 
call update_trx(50000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
 relfrozenxid |  age
--------------+-------
    170424879 | 50001

select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table'; 

  relname   | n_mod_since_analyze | n_dead_tup |        last_autovacuum        |       last_autoanalyze
------------+---------------------+------------+-------------------------------+-------------------------------
 test_table |                   0 |          0 | 2024-04-05 23:47:45.343117+08 | 2024-04-05 23:47:45.551927+08

当age 达到 vacuum_freeze_min_age开始触发lazy vacuum,达到autovacuum_freeze_max_age 进入eager vacuum

eager模式vacuum

插入数据

truncate table test_table;
call insert_trx(160000);

truncate table test_table;
call insert_trx(200000);

select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';

select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table';
 


call update_trx(20000);
select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
select relname,n_mod_since_analyze,n_dead_tup,
last_autovacuum, last_autoanalyze from pg_stat_all_tables where relname ='test_table'; 

看起来lazy vacuum和eager vacuum都是一样autovacuum,唯一的差别就是，当触发eager vacuum 会立刻执行，不需要等autovacuum_naptime = 1min

观察语句

select relfrozenxid,age(relfrozenxid) from pg_class where relname = 'test_table';
select * ,xmin,xmax,cmin,cmax from test_table;    
select datname,datfrozenxid,age(datfrozenxid) from pg_database where datname='postgres';
select relname,relfrozenxid,age(relfrozenxid) 
from pg_class where relfrozenxid !=0 order by age(relfrozenxid) desc limit 10;    
\x
select * from pg_stat_all_tables where relname ='test_table';