Horton

Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-18T14:11:10Z
Lists: pgsql-hackers 
On Tue, Jan 18, 2022 at 12:14 AM Peter Geoghegan <pg@bowt.ie> wrote:
> I quite clearly said that you'll only get an anti-wraparound VACUUM
> with the patch applied when the only factor that *ever* causes *any*
> autovacuum worker to VACUUM the table (assuming the workload is
> stable) is the anti-wraparound/autovacuum_freeze_max_age cutoff. With
> a table like this, even increasing autovacuum_freeze_max_age to its
> absolute maximum of 2 billion would not make it any more likely that
> we'd get a non-aggressive VACUUM -- it would merely make the
> anti-wraparound VACUUMs less frequent. No big change should be
> expected with a table like that.

Sure, I don't disagree with any of that. I don't see how I could. But
I don't see how it detracts from the points I was trying to make
either.

> Also, since the patch is not magic, and doesn't even change the basic
> invariants for relfrozenxid, it's still true that any scenario in
> which it's fundamentally impossible for VACUUM to keep up will also
> have anti-wraparound VACUUMs. But that's the least of the user's
> trouble -- in the long run we're going to have the system refuse to
> allocate new XIDs with such a workload.

Also true. But again, it's just about making sure that the patch
doesn't make other decisions that make things worse for people in that
situation. That's what I was expressing uncertainty about.

> The claim that I have made is 100% testable. Even if it was flat out
> incorrect, not getting anti-wraparound VACUUMs per se is not the
> important part. The important part is that the work is managed
> intelligently, and the burden is spread out over time. I am
> particularly concerned about the "freezing cliff" we get when many
> pages are all-visible but not also all-frozen. Consistently avoiding
> an anti-wraparound VACUUM (except with very particular workload
> characteristics) is really just a side effect -- it's something that
> makes the overall benefit relatively obvious, and relatively easy to
> measure. I thought that you'd appreciate that.

I do.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Commits

  1. Have VACUUM warn on relfrozenxid "in the future".

  2. vacuumlazy.c: Further consolidate resource allocation.

  3. Generalize how VACUUM skips all-frozen pages.

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

  5. Doc: Add relfrozenxid Tip to XID wraparound section.

  6. vacuumlazy.c: document vistest and OldestXmin.

  7. Increase hash_mem_multiplier default to 2.0.

  8. Consolidate VACUUM xid cutoff logic.

  9. Add VACUUM instrumentation for scanned pages, relfrozenxid.

  10. Simplify lazy_scan_heap's handling of scanned pages.

  11. Try to stabilize reloptions test, again.

  12. Unify VACUUM VERBOSE and autovacuum logging.

  13. Fix possible HOT corruption when RECENTLY_DEAD changes to DEAD while pruning.

  14. pg_resetxlog: add option to set oldest xid & use by pg_upgrade

  15. Teach VACUUM to bypass unnecessary index vacuuming.

  16. Centralize horizon determination for temp tables, fixing bug due to skew.

  17. pg_surgery: Try to stabilize regression tests.

  18. Add "split after new tuple" nbtree optimization.

  19. Fix bugs in vacuum of shared rels, by keeping their relcache entries current.

  20. Avoid useless truncation attempts during VACUUM.

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

  22. Fix recently-understood problems with handling of XID freezing, particularly