On Wed, 18 Nov 2020 at 10:28, Masahiko Sawada <sawada.m...@gmail.com> wrote:
> > So we have 3 ways to reset relfrozenxid by a user action: > > VACUUM (DISABLE_PAGE_SKIPPING ON) - scans all blocks, deliberately > > ignoring the ones it could have skipped. This certainly slows it down. > > VACUUM (FREEZE ON) - freezes everything in its path, slowing down the > > scan by writing too many blocks. > > VACUUM (FULL on) - rewrites table and rebuilds index, so very slow > > > > What I think we need is a 4th option which aims to move relfrozenxid > > forwards as quickly as possible > > * initiates an aggressive scan, so it does not skip blocks because of > > busy buffer pins > > * skip pages that are all-frozen, as we are allowed to do > > * uses normal freeze limits, so we avoid writing to blocks if possible > > > > This can be done with VACUUM today by vacuum_freeze_table_age and > vacuum_multixact_freeze_table_age to 0. Adding an option for this > behavior would be good for users to understand and would work well > with the optimization. > > > If we do all 3 of those things, the scan will complete as quickly as > > possible and reset relfrozenxid quickly. It would make sense to use > > that in conjunction with index_cleanup=off > > Agreed. Patches attached. 1. vacuum_anti_wraparound.v2.patch 2. vacuumdb_anti_wrap.v1.patch - depends upon (1) > > As an additional optimization, if we do find a row that needs freezing > > on a data block, we should simply freeze *all* row versions on the > > page, not just the ones below the selected cutoff. This is justified > > since writing the block is the biggest cost and it doesn't make much > > sense to leave a few rows unfrozen on a block that we are dirtying. > > +1 I'll work on that. -- Simon Riggs http://www.EnterpriseDB.com/
vacuumdb_anti_wrap.v1.patch
Description: Binary data
vacuum_anti_wraparound.v2.patch
Description: Binary data