Horton

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

John Naylor <john.naylor@enterprisedb.com>

View thread View on postgr.es
From: John Naylor <john.naylor@enterprisedb.com>
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-02-04T19:00:00Z
Lists: pgsql-hackers 
On Sat, Jan 29, 2022 at 11:43 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Thu, Jan 20, 2022 at 2:00 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > I do see some value in that, too. Though it's not going to be a way of
> > turning off the early freezing stuff, which seems unnecessary (though
> > I do still have work to do on getting the overhead for that down).
>
> Attached is v7, a revision that overhauls the algorithm that decides
> what to freeze. I'm now calling it block-driven freezing in the commit
> message. Also included is a new patch, that makes VACUUM record zero
> free space in the FSM for an all-visible page, unless the total amount
> of free space happens to be greater than one half of BLCKSZ.
>
> The fact that I am now including this new FSM patch (v7-0006-*patch)
> may seem like a case of expanding the scope of something that could
> well do without it. But hear me out! It's true that the new FSM patch
> isn't essential. I'm including it now because it seems relevant to the
> approach taken with block-driven freezing -- it may even make my
> general approach easier to understand.

Without having looked at the latest patches, there was something in
the back of my mind while following the discussion upthread -- the
proposed opportunistic freezing made a lot more sense if the
earlier-proposed open/closed pages concept was already available.

> Freezing whole pages
> ====================

> It's possible that a higher cutoff (for example a cutoff of 80% of
> BLCKSZ, not 50%) will actually lead to *worse* space utilization, in
> addition to the downsides from fragmentation -- it's far from a simple
> trade-off. (Not that you should believe that 50% is special, it's just
> a starting point for me.)

How was the space utilization with the 50% cutoff in the TPC-C test?

> TPC-C raw numbers
> =================
>
> The single most important number for the patch might be the decrease
> in both buffer misses and buffer hits, which I believe is caused by
> the patch being able to use index-only scans much more effectively
> (with modifications to BenchmarkSQL to improve the indexing strategy
> [1]). This is quite clear from pg_stat_database state at the end.
>
> Patch:

> blks_hit                 | 174,551,067,731
> tup_fetched              | 124,797,772,450

> Here is the same pg_stat_database info for master:

> blks_hit                 | 283,015,966,386
> tup_fetched              | 237,052,965,901

That's impressive.

--
John Naylor
EDB: http://www.enterprisedb.com

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