lightdb/postgresql中的事务回卷原理解析及避免

　　在pg中，由于事务id采用32位实现，所以是采用循环复用的，如下：

　　虽然最大支持4 billion个事务（32位无符号数，xid最大可以到40亿），但是新老事务相差2 billion是上限，当达到2 billion的时候，PostgreSQL就会强制关闭。 在此之前，pg会有一系列的提示（以下为默认值）：

• 当小于10 million的时候，日志中会告警。
• 当小于1 million的时候，PostgreSQL 会进入只读状态。

　　只要保证日常维护freeze，确保当前xid-oldestxid不超过20亿这个边界，是能够完全正常运行的。

　　实际和冻结相关的参数如下：

zjh@lt_test=# show %freez%;
                name                 |  setting  |                                  description                                  
-------------------------------------+-----------+-------------------------------------------------------------------------------
 autovacuum_freeze_max_age           | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound.
 autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound.
 vacuum_freeze_min_age               | 50000000  | Minimum age at which VACUUM should freeze a table row.  --接近很新的记录不用处理，可以降低不必要的冻结
 vacuum_freeze_table_age             | 150000000 | Age at which VACUUM should scan whole table to freeze tuples. --因为正常可能会被关闭全局autovacuum，而是人工调度，所以这个参数是这个用途
 vacuum_multixact_freeze_min_age     | 5000000   | Minimum age at which VACUUM should freeze a MultiXactId in a table row.
 vacuum_multixact_freeze_table_age   | 150000000 | Multixact age at which VACUUM should scan whole table to freeze tuples.
(6 rows)

zjh@lt_test=#  select * from pg_xid_assignment();
 next_xid | oldest_xid | xid_vac_limit | xid_warn_limit | xid_stop_limit | xid_wrap_limit | oldest_xid_db 
----------+------------+---------------+----------------+----------------+----------------+---------------
 41119082 |        480 |     200000480 |     2136484127 |     2146484127 |     2147484127 |             1
(1 row)

zjh@lt_test=# select * from pg_xid_assignment();
  next_xid  | oldest_xid | xid_vac_limit | xid_warn_limit | xid_stop_limit | xid_wrap_limit | oldest_xid_db 
------------+------------+---------------+----------------+----------------+----------------+---------------
 2941154270 | 2891144668 |    3091144668 |      732661019 |      742661019 |      743661019 |         13604
(1 row)
-- 40 billion 取模之后，相减和没有回卷是一样的。
zjh@lt_test=# SELECT datname,                   
 datfrozenxid,
 age(datfrozenxid),                                             
 (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"
FROM pg_database ORDER BY 3 DESC;
  datname  | datfrozenxid |   age    | % WRAPAROUND RISK 
-----------+--------------+----------+-------------------
 postgres  |   2891144668 | 50009950 |              5.00
 test      |   2891144737 | 50009881 |              5.00
 lt_test   |   2891144799 | 50009819 |              5.00
 template1 |   2941144860 |     9758 |              0.00
 template0 |   2941144921 |     9697 |              0.00
(5 rows)

　　xid_wrap_limit：发生回卷的上限，减去oldest_xid=2147483647。

　　xid_stop_limit：进入只读状态的上线，减去oldest_xid=2146483647。也就是10 million。

　　xid_warn_limit：log里面打告警，提示需要做freeze操作了。也就是1 million。

　　下列查询可以每个库的已冻结事务id：

zjh@lt_test=# SELECT datname,
 datfrozenxid,
 age(datfrozenxid),  // 32位整型
 (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"  /*这里的基数到底是10亿还是20亿？下同*/
FROM pg_database ORDER BY 3 DESC;
  datname  | datfrozenxid |    age    | % WRAPAROUND RISK 
-----------+--------------+-----------+-------------------
 postgres  |   2091130736 | 150012652 |             15.00
 test      |   2091130804 | 150012584 |             15.00
 lt_test   |   2091130862 | 150012526 |             15.00
 template1 |   2141130946 | 100012442 |             10.00
 template0 |   2141131007 | 100012381 |             10.00
(5 rows)

zjh@lt_test=# SELECT 
zjh@lt_test-# c.oid::regclass as table_name,
zjh@lt_test-# greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as "TXID age",
zjh@lt_test-# (greatest(age(c.relfrozenxid),age(t.relfrozenxid))::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"
zjh@lt_test-# FROM pg_class c
zjh@lt_test-# LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
zjh@lt_test-# WHERE c.relkind IN ('r', 'm')
zjh@lt_test-# ORDER BY 2 DESC;
                 table_name                 | TXID age  | % WRAPAROUND RISK 
--------------------------------------------+-----------+-------------------
 utl_file.utl_file_dir                      | 150012644 |             15.00
 pg_statistic                               | 150012644 |             15.00
 pg_type                                    | 150012644 |             15.00
 pg_foreign_table                           | 150012644 |             15.00
 pg_authid                                  | 150012644 |             15.00
 pg_statistic_ext_data                      | 150012644 |             15.00

　　通常是有几张表的冻结事务id特别小（因为vacuum正常不会做freeze，只有超过参数后才会做freeze，所以持续累计），导致整个库的事务冻结事务id无法推进、进而年龄越来越大直至超过为止。

　　所以，当vacuum进程无法跟上数据库DML活动的速度时，当到达一个瓶颈时，就可能发生事务ID回卷（Transaction ID Wraparound），此时pg数据库会强制重启。原因是此时已经可以确定mvcc无法在保证正确性。通常来说事务ID回卷会在下列情况下可能会发生：

• autovacuum被关闭
• 长时间未提交事务，如OLAP
• 复制槽保留了事务id
• DML活动导致autovacuum操作被频繁取消

　　1、3、4是比较容易出问题，需要注意。4要特别注意监控，不要该告警。

　　当当前的事务id - 表冻结事务id>=autovacuum_freeze_max_age时，会触发防止事务ID回卷的激进vacuum。如下：

2022-11-26 20:36:09.283575T,,,,,autovacuum worker,,00000,2022-11-26 20:35:59 CST,0,118166,LOG:  automatic aggressive vacuum to prevent wraparound of table "lt_test.pg_toast.pg_toast_100508": index scans: 0
    pages: 0 removed, 0 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 241134359
    buffer usage: 25 hits, 1 misses, 0 dirtied
    avg read rate: 108.507 MB/s, avg write rate: 0.000 MB/s
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
    WAL usage: 1 records, 0 full page images, 188 bytes

　　这是一个非常消耗io和cpu的操作，务必要安排在低峰期调度。

https://github.com/MasaoFujii/pg_cheat_funcs

https://developer.aliyun.com/article/560513

https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

https://www.percona.com/blog/overcoming-vacuum-wraparound/