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: Robert Haas <robertmhaas@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-21T20:06:24Z
Lists: pgsql-hackers 
On Thu, 20 Jan 2022 at 17:01, Peter Geoghegan <pg@bowt.ie> wrote:
>
> Then there's the fact that you
> really cannot think about the rate of XID consumption intuitively --
> it has at best a weak, unpredictable relationship with anything that
> users can understand, such as data stored or wall clock time.

This confuses me. "Transactions per second" is a headline database
metric that lots of users actually focus on quite heavily -- rather
too heavily imho. Ok, XID consumption is only a subset of transactions
that are not read-only but that's a detail that's pretty easy to
explain and users get pretty quickly.

There are corner cases like transactions that look read-only but are
actually read-write or transactions that consume multiple xids but
complex systems are full of corner cases and people don't seem too
surprised about these things.

What I find confuses people much more is the concept of the
oldestxmin. I think most of the autovacuum problems I've seen come
from cases where autovacuum is happily kicking off useless vacuums
because the oldestxmin hasn't actually advanced enough for them to do
any useful work.

-- 
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