Horton

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

Greg Stark <stark@mit.edu>

View thread View on postgr.es
From: Greg Stark <stark@mit.edu>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Masahiko Sawada <sawada.mshk@gmail.com>
Date: 2022-02-05T03:20:49Z
Lists: pgsql-hackers 
On Wed, 15 Dec 2021 at 15:30, Peter Geoghegan <pg@bowt.ie> wrote:
>
> My emphasis here has been on making non-aggressive VACUUMs *always*
> advance relfrozenxid, outside of certain obvious edge cases. And so
> with all the patches applied, up to and including the opportunistic
> freezing patch, every autovacuum of every table manages to advance
> relfrozenxid during benchmarking -- usually to a fairly recent value.
> I've focussed on making aggressive VACUUMs (especially anti-wraparound
> autovacuums) a rare occurrence, for truly exceptional cases (e.g.,
> user keeps canceling autovacuums, maybe due to automated script that
> performs DDL). That has taken priority over other goals, for now.

While I've seen all the above cases triggering anti-wraparound cases
by far the majority of the cases are not of these pathological forms.

By far the majority of anti-wraparound vacuums are triggered by tables
that are very large and so don't trigger regular vacuums for "long
periods" of time and consistently hit the anti-wraparound threshold
first.

There's nothing limiting how long "long periods" is and nothing tying
it to the rate of xid consumption. It's quite common to have some
*very* large mostly static tables in databases that have other tables
that are *very* busy.

The worst I've seen is a table that took 36 hours to vacuum in a
database that consumed about a billion transactions per day... That's
extreme but these days it's quite common to see tables that get
anti-wraparound vacuums every week or so despite having < 1% modified
tuples. And databases are only getting bigger and transaction rates
faster...


-- 
greg

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