Re: Removing more vacuumlazy.c special cases, relfrozenxid optimizations - Mailing list 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