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-03-31T04:04:07Z
Lists: pgsql-hackers 
Hi,

On 2022-03-30 20:35:25 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 8:28 PM Andres Freund <andres@anarazel.de> wrote:
> > I triggered twice now, but it took a while longer the second time.
>
> Great.
>
> I wonder if you can get an RR recording...

Started it, but looks like it's too slow.

(gdb) p MyProcPid
$1 = 2172500

(gdb) p vacrel->NewRelfrozenXid
$3 = 717
(gdb) p vacrel->relfrozenxid
$4 = 717
(gdb) p OldestXmin
$5 = 5112
(gdb) p aggressive
$6 = false

There was another autovacuum of pg_database 10s before:

2022-03-30 20:35:17.622 PDT [2165344][autovacuum worker][5/3:0][] LOG:  automatic vacuum of table "postgres.pg_catalog.pg_database": index scans: 1
        pages: 0 removed, 3 remain, 3 scanned (100.00% of total)
        tuples: 61 removed, 4 remain, 1 are dead but not yet removable
        removable cutoff: 1921, older by 3 xids when operation ended
        new relfrozenxid: 717, which is 3 xids ahead of previous value
        index scan needed: 3 pages from table (100.00% of total) had 599 dead item identifiers removed
        index "pg_database_datname_index": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "pg_database_oid_index": pages: 4 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        I/O timings: read: 0.029 ms, write: 0.034 ms
        avg read rate: 134.120 MB/s, avg write rate: 89.413 MB/s
        buffer usage: 35 hits, 12 misses, 8 dirtied
        WAL usage: 12 records, 5 full page images, 27218 bytes
        system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

The dying backend:
2022-03-30 20:35:27.668 PDT [2172500][autovacuum worker][7/0:0][] DEBUG:  autovacuum: processing database "contrib_regression_hstore"
...
2022-03-30 20:35:27.690 PDT [2172500][autovacuum worker][7/674:0][] CONTEXT:  while cleaning up index "pg_database_oid_index" of relation "pg_catalog.pg_database"


Greetings,

Andres Freund

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