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-02-04T22:26:46Z
Lists: pgsql-hackers 
On Fri, Feb 4, 2022 at 4:18 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Feb 4, 2022 at 3:31 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > Application B will already block pruning by VACUUM operations against
> > application A's table, and so effectively blocks recording of the
> > resultant free space in the FSM in your scenario. And so application A
> > and application B should be considered the same application already.
> > That's just how VACUUM works.
>
> Sure ... but that also sucks. If we consider application A and
> application B to be the same application, then we're basing our
> decision about what to do on information that is inaccurate.

I agree that it sucks, but I don't think that it's particularly
relevant to the FSM prototype patch that I included with v7 of the
patch series. A heap page cannot be considered "closed" (either in the
specific sense from the patch, or in any informal sense) when it has
recently dead tuples.

At some point we should invent a fallback path for pruning, that
migrates recently dead tuples to some other subsidiary structure,
retaining only forwarding information in the heap page. But even that
won't change what I just said about closed pages (it'll just make it
easier to return and fix things up later on).

> I don't see it that way. There are cases where avoiding writes is
> better, and cases where trying to cram everything into the fewest
> possible ages is better. With the right test case you can make either
> strategy look superior.

The cost of reads is effectively much lower than writes with modern
SSDs, in TCO terms. Plus when a FSM strategy like the one from the
patch does badly according to a naive measure such as total table
size, that in itself doesn't mean that we do worse with reads. In
fact, it's quite the opposite.

The benchmark showed that v7 of the patch did very slightly worse on
overall space utilization, but far, far better on reads. In fact, the
benefits for reads were far in excess of any efficiency gains for
writes/with WAL. The greatest bottleneck is almost always latency on
modern hardware [1]. It follows that keeping logically related data
grouped together is crucial. Far more important than potentially using
very slightly more space.

The story I wanted to tell with the FSM patch was about open and
closed pages being the right long term direction. More generally, we
should emphasize managing page-level costs, and deemphasize managing
tuple-level costs, which are much less meaningful.

> What I think your test case has going for it
> is that it is similar to something that a lot of people, really a ton
> of people, actually do with PostgreSQL. However, it's not going to be
> an accurate model of what everybody does, and therein lies some
> element of danger.

No question -- agreed.

> > Then what could you have confidence in?
>
> Real-world experience. Which is hard to get if we don't ever commit
> any patches, but a good argument for (a) having them tested by
> multiple different hackers who invent test cases independently and (b)
> some configurability where we can reasonably include it, so that if
> anyone does experience problems they have an escape.

I agree.

[1] https://dl.acm.org/doi/10.1145/1022594.1022596
-- 
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