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

On Mon, Jan 17, 2022 at 2:13 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Jan 17, 2022 at 4:28 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > Updating relfrozenxid should now be thought of as a continuous thing,
> > not a discrete thing.
>
> I think that's pretty nearly 100% wrong. The most simplistic way of
> expressing that is to say - clearly it can only happen when VACUUM
> runs, which is not all the time.

That just seems like semantics to me. The very next sentence after the
one you quoted in your reply was "And so it's highly unlikely that any
given VACUUM will ever *completely* fail to advance relfrozenxid".
It's continuous *within* each VACUUM. As far as I can tell there is
pretty much no way that the patch series will ever fail to advance
relfrozenxid *by at least a little bit*, barring pathological cases
with cursors and whatnot.

> That's a bit facile, though; let me
> try to say something a little smarter. There are real production
> systems that exist today where essentially all vacuums are
> anti-wraparound vacuums. And there are also real production systems
> that exist today where virtually none of the vacuums are
> anti-wraparound vacuums. So if we ship your proposed patches, the
> frequency with which relfrozenxid gets updated is going to increase by
> a large multiple, perhaps 100x, for the second group of people, who
> will then perceive the movement of relfrozenxid to be much closer to
> continuous than it is today even though, technically, it's still a
> step function. But the people in the first category are not going to
> see any difference at all.

Actually, I think that even the people in the first category might
well have about the same improved experience. Not just because of this
patch series, mind you. It would also have a lot to do with the
autovacuum_vacuum_insert_scale_factor stuff in Postgres 13. Not to
mention the freeze map. What version are these users on?

I have actually seen this for myself. With BenchmarkSQL, the largest
table (the order lines table) starts out having its autovacuums driven
entirely by autovacuum_vacuum_insert_scale_factor, even though there
is a fair amount of bloat from updates. It stays like that for hours
on HEAD. But even with my reasonably tuned setup, there is eventually
a switchover point. Eventually all autovacuums end up as aggressive
anti-wraparound VACUUMs -- this happens once the table gets
sufficiently large (this is one of the two that is append-only, with
one update to every inserted row from the delivery transaction, which
happens hours after the initial insert).

With the patch series, we have a kind of virtuous circle with freezing
and with advancing relfrozenxid with the same order lines table. As
far as I can tell, we fix the problem with the patch series. Because
there are about 10 tuples inserted per new order transaction, the
actual "XID consumption rate of the table" is much lower than the
"worst case XID consumption" for such a table.

It's also true that even with the patch we still get anti-wraparound
VACUUMs for two fixed-size, hot-update-only tables: the stock table,
and the customers table. But that's no big deal. It only happens
because nothing else will ever trigger an autovacuum, no matter the
autovacuum_freeze_max_age setting.

> And therefore the reasoning that says - anti-wraparound vacuums just
> aren't going to happen any more - or - relfrozenxid will advance
> continuously seems like dangerous wishful thinking to me.

I never said that anti-wraparound vacuums just won't happen anymore. I
said that they'll be limited to cases like the stock table or
customers table case. I was very clear on that point.

With pgbench, whether or not you ever see any anti-wraparound VACUUMs
will depend on how heap fillfactor for the accounts table -- set it
low enough (maybe to 90) and you will still get them, since there
won't be any other reason to VACUUM. As for the branches table, and
the tellers table, they'll get VACUUMs in any case, regardless of heap
fillfactor. And so they'll always advance relfrozenxid during eac
VACUUM, and never have even one anti-wraparound VACUUM.

> It's only
> true if (# of vacuums) / (# of wraparound vacuums) >> 1. And that need
> not be true in any particular environment, which to me means that all
> conclusions based on the idea that it has to be true are pretty
> dubious. There's no doubt in my mind that advancing relfrozenxid
> opportunistically is a good idea. However, I'm not sure how reasonable
> it is to change any other behavior on the basis of the fact that we're
> doing it, because we don't know how often it really happens.

It isn't that hard to see that the cases where we continue to get any
anti-wraparound VACUUMs with the patch seem to be limited to cases
like the stock/customers table, or cases like the pathological idle
cursor cases we've been discussing. Pretty narrow cases, overall.
Don't take my word for it - see for yourself.

-- 
Peter Geoghegan