Horton

Re: New strategies for freezing, advancing relfrozenxid early

Matthias van de Meent <boekewurm+postgres@gmail.com>

View thread View on postgr.es
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>, Jeff Davis <pgsql@j-davis.com>, John Naylor <john.naylor@enterprisedb.com>, Justin Pryzby <pryzby@telsasoft.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-01-26T19:26:00Z
Lists: pgsql-hackers 
On Thu, 26 Jan 2023 at 19:45, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Thu, Jan 26, 2023 at 9:53 AM Andres Freund <andres@anarazel.de> wrote:
> > I assume the case you're thinking of is that pruning did *not* do any changes,
> > but in the process of figuring out that nothing needed to be pruned, we did a
> > MarkBufferDirtyHint(), and as part of that emitted an FPI?
>
> Yes.
>
> > > That's going to be very significantly more aggressive. For example
> > > it'll impact small tables very differently.
> >
> > Maybe it would be too aggressive, not sure. The cost of a freeze WAL record is
> > relatively small, with one important exception below, if we are 99.99% sure
> > that it's not going to require an FPI and isn't going to dirty the page.
> >
> > The exception is that a newer LSN on the page can cause the ringbuffer
> > replacement to trigger more more aggressive WAL flushing. No meaningful
> > difference if we modified the page during pruning, or if the page was already
> > in s_b (since it likely won't be written out via the ringbuffer in that case),
> > but if checksums are off and we just hint-dirtied the page, it could be a
> > significant issue.
>
> Most of the overhead of FREEZE WAL records (with freeze plan
> deduplication and page-level freezing in) is generic WAL record header
> overhead. Your recent adversarial test case is going to choke on that,
> too. At least if you set checkpoint_timeout to 1 minute again.

Could someone explain to me why we don't currently (optionally)
include the functionality of page freezing in the PRUNE records? I
think they're quite closely related (in that they both execute in
VACUUM and are required for long-term system stability), and are even
more related now that we have opportunistic page-level freezing. I
think adding a "freeze this page as well"-flag in PRUNE records would
go a long way to reducing the WAL overhead of aggressive and more
opportunistic freezing.

-Matthias

Commits

  1. Revert "Add eager and lazy freezing strategies to VACUUM."

  2. Add eager and lazy freezing strategies to VACUUM.

  3. Refine the definition of page-level freezing.

  4. Avoid special XID snapshotConflictHorizon values.

  5. Add page-level freezing to VACUUM.

  6. Remove overzealous MultiXact freeze assertion.

  7. Refactor how VACUUM passes around its XID cutoffs.

  8. Deduplicate freeze plans in freeze WAL records.

  9. Set relfrozenxid to oldest extant XID seen by VACUUM.

  10. Only skip pages marked as clean in the visibility map, if the last 32

  11. Add vacuum_freeze_table_age GUC option, to control when VACUUM should