作者
digoal
日期
2021-04-08
标签
PostgreSQL , vacuum 加速 , vacuum_failsafe_age , vacuum_multixact_failsafe_age
背景
当表的年龄(pg_class. relfrozenxid relminmxid ) 超过 vacuum_failsafe_age, vacuum_multixact_failsafe_age. 这两个参数默认为16亿.
这个表的vacuum会被强制加速, 忽略delay参数, 同时跳过不必要的垃圾回收(例如index的vacuum会被跳过), 从而彻底防止xid wraparound .
PG 14以前, 虽然有手段, 但是vacuum依旧收到delay参数的控制, vacuum时会休息, 另外表的index vacuum也会增加vacuum的时间.
PG 14的vacuum加速针对的是紧急情况, 即防止xid wraparound.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e55e7d1755cefbb44982fbacc7da461fa8684e6
```
Add wraparound failsafe to VACUUM.
author Peter Geoghegan pg@bowt.ie
Wed, 7 Apr 2021 19:37:45 +0000 (12:37 -0700)
committer Peter Geoghegan pg@bowt.ie
Wed, 7 Apr 2021 19:37:45 +0000 (12:37 -0700)
commit 1e55e7d1755cefbb44982fbacc7da461fa8684e6
tree 45561d27b289f6386a41c10c29b605dd89ff6bb1 tree
parent 4f0b0966c866ae9f0e15d7cc73ccf7ce4e1af84b commit | diff
Add wraparound failsafe to VACUUM.
Add a failsafe mechanism that is triggered by VACUUM when it notices
that the table's relfrozenxid and/or relminmxid are dangerously far in
the past. VACUUM checks the age of the table dynamically, at regular
intervals.
When the failsafe triggers, VACUUM takes extraordinary measures to
finish as quickly as possible so that relfrozenxid and/or relminmxid can
be advanced. VACUUM will stop applying any cost-based delay that may be
in effect. VACUUM will also bypass any further index vacuuming and heap
vacuuming -- it only completes whatever remaining pruning and freezing
is required. Bypassing index/heap vacuuming is enabled by commit
8523492d, which made it possible to dynamically trigger the mechanism
already used within VACUUM when it is run with INDEX_CLEANUP off.
It is expected that the failsafe will almost always trigger within an
autovacuum to prevent wraparound, long after the autovacuum began.
However, the failsafe mechanism can trigger in any VACUUM operation.
Even in a non-aggressive VACUUM, where we're likely to not advance
relfrozenxid, it still seems like a good idea to finish off remaining
pruning and freezing. An aggressive/anti-wraparound VACUUM will be
launched immediately afterwards. Note that the anti-wraparound VACUUM
that follows will itself trigger the failsafe, usually before it even
begins its first (and only) pass over the heap.
The failsafe is controlled by two new GUCs: vacuum_failsafe_age, and
vacuum_multixact_failsafe_age. There are no equivalent reloptions,
since that isn't expected to be useful. The GUCs have rather high
defaults (both default to 1.6 billion), and are expected to generally
only be used to make the failsafe trigger sooner/more frequently.
Author: Masahiko Sawada sawada.mshk@gmail.com
Author: Peter Geoghegan pg@bowt.ie
Discussion: https://postgr.es/m/CAD21AoD0SkE11fMw4jD4RENAwBMcw1wasVnwpJVw3tVqPOQgAw@mail.gmail.com
Discussion: https://postgr.es/m/CAH2-WzmgH3ySGYeC-m-eOBsa2=sDwa292-CFghV4rESYo39FsQ@mail.gmail.com
```
+ <varlistentry id="guc-vacuum-failsafe-age" xreflabel="vacuum_failsafe_age">
+ <term><varname>vacuum_failsafe_age</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>vacuum_failsafe_age</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Specifies the maximum age (in transactions) that a table's
+ <structname>pg_class</structname>.<structfield>relfrozenxid</structfield>
+ field can attain before <command>VACUUM</command> takes
+ extraordinary measures to avoid system-wide transaction ID
+ wraparound failure. This is <command>VACUUM</command>'s
+ strategy of last resort. The failsafe typically triggers
+ when an autovacuum to prevent transaction ID wraparound has
+ already been running for some time, though it's possible for
+ the failsafe to trigger during any <command>VACUUM</command>.
+ </para>
+ <para>
+ When the failsafe is triggered, any cost-based delay that is
+ in effect will no longer be applied, and further non-essential
+ maintenance tasks (such as index vacuuming) are bypassed.
+ </para>
+ <para>
+ The default is 1.6 billion transactions. Although users can
+ set this value anywhere from zero to 2.1 billion,
+ <command>VACUUM</command> will silently adjust the effective
+ value to no less than 105% of <xref
+ linkend="guc-autovacuum-freeze-max-age"/>.
+ </para>
+ </listitem>
+ </varlistentry>
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
