Horton

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

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Robert Haas <robertmhaas@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-04-03T19:05:32Z
Lists: pgsql-hackers 
Hi,

On 2022-04-01 10:54:14 -0700, Peter Geoghegan wrote:
> On Thu, Mar 31, 2022 at 11:19 AM Peter Geoghegan <pg@bowt.ie> wrote:
> > The assert is "Assert(diff > 0)", and not "Assert(diff >= 0)".
>
> Attached is v15. I plan to commit the first two patches (the most
> substantial two patches by far) in the next couple of days, barring
> objections.

Just saw that you committed: Wee! I think this will be a substantial
improvement for our users.


While I was writing the above I, again, realized that it'd be awfully nice to
have some accumulated stats about (auto-)vacuum's effectiveness. For us to get
feedback about improvements more easily and for users to know what aspects
they need to tune.

Knowing how many times a table was vacuumed doesn't really tell that much, and
requiring to enable log_autovacuum_min_duration and then aggregating those
results is pretty painful (and version dependent).

If we just collected something like:
- number of heap passes
- time spent heap vacuuming
- number of index scans
- time spent index vacuuming
- time spent delaying
- percentage of non-yet-removable vs removable tuples

it'd start to be a heck of a lot easier to judge how well autovacuum is
coping.

If we tracked the related pieces above in the index stats (or perhaps
additionally there), it'd also make it easier to judge the cost of different
indexes.

- Andres

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