Horton

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

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2022-01-20T14:55:39Z
Lists: pgsql-hackers 
On Wed, Jan 19, 2022 at 2:54 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > On the other hand if that user is going to close that
> > cursor after 10 minutes and open a new one in the same place 10
> > seconds later, the best thing to do is to keep FreezeLimit as low as
> > possible, because the first time we wait for the pin to be released
> > we're guaranteed to advance relfrozenxid within 10 minutes, whereas if
> > we don't do that we may keep missing the brief windows in which no
> > cursor is held for a very long time. But we have absolutely no way of
> > knowing which of those things is going to happen on any particular
> > system, or of estimating which one is more common in general.
>
> I agree with all that, and I think that this particular scenario is
> the crux of the issue.

Great, I'm glad we agree on that much. I would be interested in
hearing what other people think about this scenario.

> The first time this happens (and we don't get a cleanup lock), then we
> will at least be able to set relfrozenxid to the exact oldest unfrozen
> XID. So that'll already have bought us some wallclock time -- often a
> great deal (why should the oldest XID on such a page be particularly
> old?). Furthermore, there will often be many more VACUUMs before we
> need to do an aggressive VACUUM -- each of these VACUUM operations is
> an opportunity to freeze the oldest tuple that holds up cleanup. Or
> maybe this XID is in a dead tuple, and so somebody's opportunistic
> pruning operation does the right thing for us. Never underestimate the
> power of dumb luck, especially in a situation where there are many
> individual "trials", and we only have to get lucky once.
>
> If and when that doesn't work out, and we actually have to do an
> anti-wraparound VACUUM, then something will have to give. Since
> anti-wraparound VACUUMs are naturally confined to certain kinds of
> tables/workloads with the patch series, we can now be pretty confident
> that the problem really is with this one problematic heap page, with
> the idle cursor. We could even verify this directly if we wanted to,
> by noticing that the preexisting relfrozenxid is an exact match for
> one XID on some can't-cleanup-lock page -- we could emit a WARNING
> about the page/tuple if we wanted to. To return to my colorful analogy
> from earlier, we now know that the patient almost certainly has a
> brain tumor.
>
> What new risk is implied by delaying the wait like this? Very little,
> I believe. Lets say we derive FreezeLimit from
> autovacuum_freeze_max_age/2 (instead of vacuum_freeze_min_age). We
> still ought to have the opportunity to wait for the cleanup lock for
> rather a long time -- if the XID consumption rate is so high that that
> isn't true, then we're doomed anyway. All told, there seems to be a
> huge net reduction in risk with this design.

I'm just being honest here when I say that I can't see any huge
reduction in risk. Nor a huge increase in risk. It just seems
speculative to me. If I knew something about the system or the
workload, then I could say what would likely work out best on that
system, but in the abstract I neither know nor understand how it's
possible to know.

My gut feeling is that it's going to make very little difference
either way. People who never release their cursors or locks or
whatever are going to be sad either way, and people who usually do
will be happy either way. There's some in-between category of people
who release sometimes but not too often for whom it may matter,
possibly quite a lot. It also seems possible that one decision rather
than another will make the happy people MORE happy, or the sad people
MORE sad. For most people, though, I think it's going to be
irrelevant. The fact that you seem to view the situation quite
differently is a big part of what worries me here. At least one of us
is missing something.

-- 
Robert Haas
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