On Mon, Jan 17, 2022 at 2:13 PM Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Jan 17, 2022 at 4:28 PM Peter Geoghegan <p...@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
