Horton

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

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-15T02:43:08Z
Lists: pgsql-hackers

Attachments

On Thu, Jan 13, 2022 at 1:27 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Admittedly you may see a blip from this -- you might notice that the
> final relfrozenxid value for that one single VACUUM isn't quite as new
> as you'd like. But then the next VACUUM should catch up with the
> stable long term average again. It's hard to describe exactly why this
> effect is robust, but as I said, empirically, in practice, it appears
> to be robust. That might not be good enough as an explanation that
> justifies committing the patch series, but that's what I see. And I
> think I will be able to nail it down.

Attached is v6, which like v5 is a rebased version that I'm posting to
keep CFTester happy. I pushed a commit that consolidates VACUUM
VERBOSE and autovacuum logging earlier (commit 49c9d9fc), which bitrot
v5. So new real changes, nothing to note.

Although it technically has nothing to do with this patch series, I
will point out that it's now a lot easier to debug using VACUUM
VERBOSE, which will directly display information about how we've
advanced relfrozenxid, tuples frozen, etc:

pg@regression:5432 =# delete from mytenk2 where hundred < 15;
DELETE 1500
pg@regression:5432 =# vacuum VERBOSE mytenk2;
INFO:  vacuuming "regression.public.mytenk2"
INFO:  finished vacuuming "regression.public.mytenk2": index scans: 1
pages: 0 removed, 345 remain, 0 skipped using visibility map (0.00% of total)
tuples: 1500 removed, 8500 remain (8500 newly frozen), 0 are dead but
not yet removable
removable cutoff: 17411, which is 0 xids behind next
new relfrozenxid: 17411, which is 3 xids ahead of previous value
index scan needed: 341 pages from table (98.84% of total) had 1500
dead item identifiers removed
index "mytenk2_unique1_idx": pages: 39 in total, 0 newly deleted, 0
currently deleted, 0 reusable
index "mytenk2_unique2_idx": pages: 30 in total, 0 newly deleted, 0
currently deleted, 0 reusable
index "mytenk2_hundred_idx": pages: 11 in total, 1 newly deleted, 1
currently deleted, 0 reusable
I/O timings: read: 0.011 ms, write: 0.000 ms
avg read rate: 1.428 MB/s, avg write rate: 2.141 MB/s
buffer usage: 1133 hits, 2 misses, 3 dirtied
WAL usage: 1446 records, 1 full page images, 199702 bytes
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s
VACUUM

-- 
Peter Geoghegan

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