模拟PG触发freeze

模拟触发freeze，分析冻结txids

freeze分析准备

查看freeze设置

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

SELECT name, setting, unit 
FROM pg_settings WHERE name LIKE 'vacuum_freeze%';

          name           | setting | unit
-------------------------+---------+------
 vacuum_freeze_min_age   | 50000   |
 vacuum_freeze_table_age | 150000  |
(2 rows)

SELECT name, setting, unit 
FROM pg_settings WHERE name LIKE 'vacuum_freeze%';

                name                 | setting | unit
-------------------------------------+---------+------
 autovacuum_freeze_max_age           | 200000  |
 autovacuum_multixact_freeze_max_age | 250000  |
 vacuum_freeze_min_age               | 50000   |
 vacuum_freeze_table_age             | 150000  |
 vacuum_multixact_freeze_min_age     | 5000000 |
 vacuum_multixact_freeze_table_age   | 250000  |

模仿事务消耗存储过程

对表test_table插入：insert_trx

对表test_table修改：update_trx

对表test_table删除：delete_trx

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;
$$ ;

页面分析准备

CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE FUNCTION heap_page(
relname text, pageno_from integer, pageno_to integer
)
RETURNS TABLE(
ctid tid, state text,
xmin text, xmin_age integer, xmax text
) AS $$
SELECT (pageno,lp)::text::tid AS ctid,
CASE lp_flags
WHEN 0 THEN 'unused'
WHEN 1 THEN 'normal'
WHEN 2 THEN 'redirect to '||lp_off
WHEN 3 THEN 'dead'
END AS state,
t_xmin || CASE
WHEN (t_infomask & 256+512) = 256+512 THEN ' f'
WHEN (t_infomask & 256) > 0 THEN ' c'
WHEN (t_infomask & 512) > 0 THEN ' a'
ELSE ''
END AS xmin,
age(t_xmin) AS xmin_age,
t_xmax || CASE
WHEN (t_infomask & 1024) > 0 THEN ' c'
WHEN (t_infomask & 2048) > 0 THEN ' a'
ELSE ''
END AS xmax
FROM generate_series(pageno_from, pageno_to) p(pageno),
heap_page_items(get_raw_page(relname, pageno))
ORDER BY pageno, lp;
$$ LANGUAGE sql;

模拟触发freeze

实验1事务不及时提交

#session 1 
 begin;
 INSERT INTO test_table (name) values('first' )  ;
#session 2
call insert_trx(50000);
call update_trx(50000);
call update_trx(50000);
call update_trx(50000);

# session 3
 select datname, age(datfrozenxid) from pg_database;
 select 
 t_xmin,
 t_xmax,
 t_infomask,
 t_infomask2,
 age(t_xmin)
from heap_page_items(get_raw_page('test_table', 1)) where lp_flags=1 ;
SELECT * FROM heap_page('test_table',0,1);
select datname, age(datfrozenxid) from pg_database;

触发freeze，日志warning输出如下

2024-04-18 16:05:51.106 CST [11888] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

SELECT * FROM heap_page(‘test_table’,0,1);

  ctid   | state  |    xmin     | xmin_age |    xmax
---------+--------+-------------+----------+-------------
 (0,1)   | normal | 171295009   |   250006 | 0 a
 (0,2)   | normal | 171295010 c |   250005 | 171395012 c
 (0,3)   | normal | 171295011 c |   250004 | 171395013 c
 (0,4)   | normal | 171295012 c |   250003 | 171395014 c
 (0,5)   | normal | 171295013 c |   250002 | 171395015 c
 (0,6)   | normal | 171295014 c |   250001 | 171395016 c
 (0,7)   | normal | 171295015 c |   250000 | 171395017 c
 (0,8)   | normal | 171295016 c |   249999 | 171395018 c
 (0,9)   | normal | 171295017 c |   249998 | 171395019 c
 (0,10)  | normal | 171295018 c |   249997 | 171395020 c

select
t_xmin,
t_xmax,
t_infomask,
t_infomask2,
age(t_xmin)
from heap_page_items(get_raw_page(‘test_table’, 1)) ;

  t_xmin   |  t_xmax   | t_infomask | t_infomask2 |  age
-----------+-----------+------------+-------------+--------
 171945210 | 171995211 |       1282 |           2 | 249820
 171945211 | 171995212 |       1282 |           2 | 249819
 171945212 | 171995213 |       1282 |           2 | 249818
 171945213 | 171995214 |       1282 |           2 | 249817
 171945214 | 171995215 |       1282 |           2 | 249816
 171945215 | 171995216 |       1282 |           2 | 249815
 171945216 | 171995217 |       1282 |           2 | 249814
 171945217 | 171995218 |       1282 |           2 | 249813
 171945218 | 171995219 |       1282 |           2 | 249812
 171945219 | 171995220 |       1282 |           2 | 249811
 171945220 | 171995221 |       1282 |           2 | 249810
 171945221 | 171995222 |       1282 |           2 | 249809
 171945222 | 171995223 |       1282 |           2 | 249808
 171945223 | 171995224 |       1282 |           2 | 249807

实验2事务及时提交

实验3调整参数长事务

vacuum_failsafe_age =600000

select name, setting, short_desc from pg_settings 
where name in ('autovacuum',
              'autovacuum_freeze_max_age',
              'log_autovacuum_min_duration',
              'vacuum_failsafe_age',
              'vacuum_freeze_min_age',
              'vacuum_freeze_table_age'
              );


 

            name             |  setting  |                                   short_desc
-----------------------------+-----------+--------------------------------------------------------------------------------
 autovacuum                  | on        | Starts the autovacuum subprocess.
 autovacuum_freeze_max_age   | 200000    | Age at which to autovacuum a table to prevent transaction ID wraparound.
 log_autovacuum_min_duration | 0         | Sets the minimum execution time above which autovacuum actions will be logged.
 vacuum_failsafe_age         | 600000 | Age at which VACUUM should trigger failsafe to avoid a wraparound outage.
 vacuum_freeze_min_age       | 50000     | Minimum age at which VACUUM should freeze a table row.
 vacuum_freeze_table_age     | 150000    | Age at which VACUUM should scan whole table to freeze tuples.

call update_trx(50000);

call update_trx(50000);

call update_trx(50000);

call update_trx(50000);

call update_trx(50000); | postgres | 600008 | oldest xmin is far in the past |
| T9 | | call update_trx(50000); | postgres | 650009 | oldest xmin is far in the past |
| T10 | commit; | | postgres | 50000 | |

T4时日志

2024-04-18 17:55:39.238 CST [15090] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-04-18 17:55:39.238 CST [15090] LOG:  automatic aggressive vacuum to prevent wraparound of table "postgres.pg_toast.pg_toast_14109": index scans: 0
        pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
        tuples: 0 removed, 0 remain, 0 are dead but not yet removable
        removable cutoff: 172295034, which was 200004 XIDs old when operation ended
        new relfrozenxid: 172295034, which is 1 XIDs ahead of previous value
        index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
        avg read rate: 195.312 MB/s, avg write rate: 0.000 MB/s
        buffer usage: 17 hits, 1 misses, 0 dirtied
        WAL usage: 1 records, 0 full page images, 188 bytes
        system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

T8时日志

2024-04-18 18:13:39.761 CST [15608] WARNING:  oldest xmin is far in the past
2024-04-18 18:13:39.761 CST [15608] HINT:  Close open transactions soon to avoid wraparound problems.
        You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
2024-04-18 18:13:39.761 CST [15608] WARNING:  bypassing nonessential maintenance of table "postgres.pg_toast.pg_toast_14114" as a failsafe after 0 index scans
2024-04-18 18:13:39.761 CST [15608] DETAIL:  The table's relfrozenxid or relminmxid is too far in the past.
2024-04-18 18:13:39.761 CST [15608] HINT:  Consider increasing configuration parameter "maintenance_work_mem" or "autovacuum_work_mem".
        You might also need to consider other ways for VACUUM to keep up with the allocation of transaction IDs.
2024-04-18 18:13:39.761 CST [15608] LOG:  automatic aggressive vacuum to prevent wraparound of table "postgres.pg_toast.pg_toast_14114": index scans: 0
        pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
        tuples: 0 removed, 0 remain, 0 are dead but not yet removable
        removable cutoff: 172295034, which was 650009 XIDs old when operation ended
        index scan bypassed by failsafe: 0 pages from table (100.00% of total) have 0 dead item identifiers
        avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
        buffer usage: 16 hits, 0 misses, 0 dirtied
        WAL usage: 0 records, 0 full page images, 0 bytes
        system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

结论

1.超过autovacuum_freeze_max_age会触发自动vacuum

2.长事务会制约自动触发vacuum清理，需要保存MVCC所需版本

3.数据库只会报警告“Close open transactions soon to avoid wraparound problems”，未能模拟出

4.调整vacuum_failsafe_age强制触发autovacuum也被长事务阻塞

PS: 不管哪种数据库，对大事务，长事务都不待见；及时提交事务