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

On Fri, Jan 7, 2022 at 12:24 PM Robert Haas <robertmhaas@gmail.com> wrote:
> This seems like a weak argument. Sure, you COULD hard-code the limit
> to be autovacuum_freeze_max_age/2 rather than making it a separate
> tunable, but I don't think it's better. I am generally very skeptical
> about the idea of using the same GUC value for multiple purposes,
> because it often turns out that the optimal value for one purpose is
> different than the optimal value for some other purpose.

I thought I was being conservative by suggesting
autovacuum_freeze_max_age/2. My first thought was to teach VACUUM to
make its FreezeLimit "OldestXmin - autovacuum_freeze_max_age". To me
these two concepts really *are* the same thing: vacrel->FreezeLimit
becomes a backstop, just as anti-wraparound autovacuum (the
autovacuum_freeze_max_age cutoff) becomes a backstop.

Of course, an anti-wraparound VACUUM will do early freezing in the
same way as any other VACUUM will (with the patch series). So even
when the FreezeLimit backstop XID cutoff actually affects the behavior
of a given VACUUM operation, it may well not be the reason why most
individual tuples that we freeze get frozen. That is, most individual
heap pages will probably have tuples frozen for some other reason.
Though it depends on workload characteristics, most individual heap
pages will typically be frozen as a group, even here. This is a
logical consequence of the fact that tuple freezing and advancing
relfrozenxid are now only loosely coupled -- it's about as loose as
the current relfrozenxid invariant will allow.

> I feel generally that a lot of the argument you're making here
> supposes that tables are going to get vacuumed regularly.

> I agree that
> IF tables are being vacuumed on a regular basis, and if as part of
> that we always push relfrozenxid forward as far as we can, we will
> rarely have a situation where aggressive strategies to avoid
> wraparound are required.

It's all relative. We hope that (with the patch) cases that only ever
get anti-wraparound VACUUMs are limited to tables where nothing else
drives VACUUM, for sensible reasons related to workload
characteristics (like the pgbench_accounts example upthread). It's
inevitable that some users will misconfigure the system, though -- no
question about that.

I don't see why users that misconfigure the system in this way should
be any worse off than they would be today. They probably won't do
substantially less freezing (usually somewhat more), and will advance
pg_class.relfrozenxid in exactly the same way as today (usually a bit
better, actually). What have I missed?

Admittedly the design of the "Freeze tuples early to advance
relfrozenxid" patch (i.e. v5-0005-*patch) is still unsettled; I need
to verify that my claims about it are really robust. But as far as I
know they are. Reviewers should certainly look at that with a critical
eye.

> Now, I agree with you in part: I don't think it's obvious that it's
> useful to tune vacuum_freeze_table_age.

That's definitely the easier argument to make. After all,
vacuum_freeze_table_age will do nothing unless VACUUM runs before the
anti-wraparound threshold (autovacuum_freeze_max_age) is reached. The
patch series should be strictly better than that. Primarily because
it's "continuous", and so isn't limited to cases where the table age
falls within the "vacuum_freeze_table_age - autovacuum_freeze_max_age"
goldilocks age range.

> We should be VERY conservative about removing
> existing settings if there's any chance that somebody could use them
> to tune their way out of trouble.

I agree, I suppose, but right now I honestly can't think of a reason
why they would be useful.

If I am wrong about this then I'm probably also wrong about some basic
facet of the high-level design, in which case I should change course
altogether. In other words, removing the GUCs is not an incidental
thing. It's possible that I would never have pursued this project if I
didn't first notice how wrong-headed the GUCs are.

> So, let's see: if we see a page where the tuples are all-visible and
> we seize the opportunity to freeze it, we can spare ourselves the need
> to ever visit that page again (unless it gets modified). But if we
> only mark it all-visible and leave the freezing for later, the next
> aggressive vacuum will have to scan and dirty the page. I'm prepared
> to believe that it's worth the cost of freezing the page in that
> scenario.

That's certainly the most compelling reason to perform early freezing.
It's not completely free of downsides, but it's pretty close.

> There's another situation in which vacuum_freeze_min_age could apply,
> though: suppose the page isn't all-visible yet. I'd argue that in that
> case we don't want to run around freezing stuff unless it's quite old
> - like older than vacuum_freeze_table_age, say. Because we know we're
> going to have to revisit this page in the next vacuum anyway, and
> expending effort to freeze tuples that may be about to be modified
> again doesn't seem prudent. So, hmm, on further reflection, maybe it's
> OK to remove vacuum_freeze_min_age. But if we do, then I think we had
> better carefully distinguish between the case where the page can
> thereby be marked all-frozen and the case where it cannot. I guess you
> say the same, further down.

I do. Although v5-0005-*patch still freezes early when the page is
dirtied by pruning, I have my doubts about that particular "freeze
early" criteria. I believe that everything I just said about
misconfigured autovacuums doesn't rely on anything more than the "most
compelling scenario for early freezing" mechanism that arranges to
make us set the all-frozen bit (not just the all-visible bit).

> I mean, those kinds of pathological cases happen *all the time*. Sure,
> there are plenty of users who don't leave cursors open. But the ones
> who do don't leave them around for short periods of time on randomly
> selected pages of the table. They are disproportionately likely to
> leave them on the same table pages over and over, just like data can't
> in general be assumed to be uniformly accessed. And not uncommonly,
> they leave them around until the snow melts.

> And we need to worry about those kinds of users, actually much more
> than we need to worry about users doing normal things.

I couldn't agree more. In fact, I was mostly thinking about how to
*help* these users. Insisting on waiting for a cleanup lock before it
becomes strictly necessary (when the table age is only 50
million/vacuum_freeze_min_age) is actually a big part of the problem
for these users. vacuum_freeze_min_age enforces a false dichotomy on
aggressive VACUUMs, that just isn't unhelpful. Why should waiting on a
cleanup lock fix anything?

Even in the extreme case where we are guaranteed to eventually have a
wraparound failure in the end (due to an idle cursor in an
unsupervised database), the user is still much better off, I think. We
will have at least managed to advance relfrozenxid to the exact oldest
XID on the one heap page that somebody holds an idle cursor
(conflicting buffer pin) on. And we'll usually have frozen most of the
tuples that need to be frozen. Sure, the user may need to use
single-user mode to run a manual VACUUM, but at least this process
only needs to freeze approximately one tuple to get the system back
online again.

If the DBA notices the problem before the database starts to refuse to
allocate XIDs, then they'll have a much better chance of avoiding a
wraparound failure through simple intervention (like killing the
backend with the idle cursor). We can pay down 99.9% of the "freeze
debt" independently of this intractable problem of something holding
onto an idle cursor.

> Honestly,
> autovacuum on a system where things are mostly "normal" - no
> long-running transactions, adequate resources for autovacuum to do its
> job, reasonable configuration settings - isn't that bad.

Right. Autovacuum is "too big to fail".

> > But the "freeze early" heuristics work a bit like that anyway. We
> > won't freeze all the tuples on a whole heap page early if we won't
> > otherwise set the heap page to all-visible (not all-frozen) in the VM
> > anyway.
>
> Hmm, I didn't realize that we had that. Is that an existing thing or
> something new you're proposing to do? If existing, where is it?

It's part of v5-0005-*patch. Still in flux to some degree, because
it's necessary to balance a few things. That shouldn't undermine the
arguments I've made here.

> I agree that it's OK for this to become a purely backstop mechanism
> ... but again, I think that the design of such backstop mechanisms
> should be done as carefully as we know how, because users seem to hit
> the backstop all the time. We want it to be made of, you know, nylon
> twine, rather than, say, sharp nails. :-)

Absolutely. But if autovacuum can only ever run due to
age(relfrozenxid) reaching autovacuum_freeze_max_age, then I can't see
a downside.

Again, the v5-0005-*patch needs to meet the standard that I've laid
out. If it doesn't then I've messed up already.

-- 
Peter Geoghegan