Re: VACUUM (DISABLE_PAGE_SKIPPING on)

On Wed, 18 Nov 2020 at 10:28, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)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/