Re: Eager page freeze criteria clarification - Mailing list pgsql-hackers

On Mon, Oct 2, 2023 at 6:26 AM Robert Haas <robertmhaas@gmail.com> wrote:
> I think it's true that the fact that pgbench does what pgbench does
> makes us think more about that workload than about some other, equally
> plausible workload. It's the test we have, so we end up running it a
> lot. If we had some other test, we'd run that one.

I find pgbench very useful as a stress-test. It usually represents the
most extreme possible adversarial case, which puts certain patches in
context, helping to paint a complete picture.

> As for non-uniform access, that is real and does matter, but there are
> certainly installations with tables where no rows survive long enough
> to need freezing, either because the table is regularly emptied, or
> just because the update load is high enough to hit all the rows fairly
> quickly.

That's true, but I think that they're all queue-like tables. Not large
tables where there just isn't any chance of any row/page not being
updating after a fairly short period of time.

> Maybe I'm misunderstanding your point here, in which case all of the
> above may be irrelevant. But my feeling is that we can't simply ignore
> cases where all/many rows are short-lived and say, well, those are
> unrealistic, so let's just freeze everything super-aggressively and
> that should be fine. I don't think that's OK. We can (and I think we
> should) treat that situation as a special case rather than as the
> typical case, but I think it would be a bad idea to dismiss it as a
> case that we don't need to worry about at all.

I think that my point about pgbench being generally unrepresentative
might have overshadowed the more important point about the VACUUM
requirements of pgbench_accounts in particular. Those are particularly
unrealistic.

If no vacuuming against pgbench_accounts is strictly better than some
vacuuming (unless it's just to advance relfrozenxid, which can't be
avoided), then to what extent is Melanie's freezing patch obligated to
not make the situation worse? I'm not saying that it doesn't matter at
all; just that there needs to be a sense of proportion. If even
pruning (by VACUUM) is kinda, sorta, useless, then it seems like we
might need to revisit basic assumptions. (Or maybe the problem itself
can just be avoided for the most part; whatever works.)

I have a patch that provides a simple way of tracking whether each
PRUNE record was generated by VACUUM or by opportunistic pruning (also
something Melanie is involved with):

https://commitfest.postgresql.org/44/4288/

Although it's really hard (maybe impossible) to track right now, I've
noticed that the vast majority of all PRUNE records are from
opportunistic vacuuming with just about every write-heavy benchmark,
including pgbench and TPC-C. And that's just going on the raw count of
each type of record, not the utility provided by each pruning
operation. Admittedly "utility" is a very hard thing to measure here,
but it might still matter. VACUUM might be doing some pruning, but
only pruning targeting pages that never particularly "need to be
pruned" by VACUUM itself (since pruning could happen whenever via
opportunistic pruning anyway).

Whatever your priorities might be, workload-wise, it could still be
useful to recognize useless freezing as part of a broader problem of
useless (or at least dubious) work performed by VACUUM -- especially
with pgbench-like workloads. The utility of freezing is a lot easier
to measure than the utility of pruning, but why should we assume that
pruning isn't already just as much of a problem? (Maybe that's not a
problem that particularly interests you right now; I'm bringing it up
because it seems possible that putting it in scope could somehow
clarify what to do about freezing.)

--
Peter Geoghegan