Horton

Re: New strategies for freezing, advancing relfrozenxid early

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Jeff Davis <pgsql@j-davis.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, John Naylor <john.naylor@enterprisedb.com>, Justin Pryzby <pryzby@telsasoft.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Robert Haas <robertmhaas@gmail.com>
Date: 2023-01-26T00:43:47Z
Lists: pgsql-hackers 
Hi,

On 2023-01-24 14:49:38 -0800, Peter Geoghegan wrote:
> On Mon, Jan 16, 2023 at 5:55 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > 0001 (the freezing strategies patch) is now committable IMV. Or at
> > least will be once I polish the docs a bit more. I plan on committing
> > 0001 some time next week, barring any objections.
>
> I plan on committing 0001 (the freezing strategies commit) tomorrow
> morning, US Pacific time.

I unfortunately haven't been able to keep up with the thread and saw this just
now. But I've expressed the concern below several times before, so it
shouldn't come as a surprise.

I think, as committed, this will cause serious issues for some reasonably
common workloads, due to substantially increased WAL traffic.


The most common problematic scenario I see are tables full of rows with
limited lifetime. E.g. because rows get aggregated up after a while. Before
those rows practically never got frozen - but now we'll freeze them all the
time.


I whipped up a quick test: 15 pgbench threads insert rows, 1 psql \while loop
deletes older rows.

Workload fits in s_b:

Autovacuum on average generates between 1.5x-7x as much WAL as before,
depending on how things interact with checkpoints. And not just that, each
autovac cycle also takes substantially longer than before - the average time
for an autovacuum roughly doubled.  Which of course increases the amount of
bloat.


When workload doesn't fit in s_b:

Time for vacuuming goes up to ~5x. WAL volume to ~9x. Autovacuum can't keep up
with bloat, every vacuum takes longer than the prior one:
65s->78s->139s->176s
And that's with autovac cost limits removed! Relation size nearly doubles due
to bloat.


After I disabled the new strategy autovac started to catch up again:
124s->101s->103->46s->20s->28s->24s


This is significantly worse than I predicted. This was my first attempt at
coming up with a problematic workload. There'll likely be way worse in
production.



I think as-is this logic will cause massive issues.

Andres

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