On Fri, Aug 19, 2022 at 8:40 AM Vido Vlahinic <Vido.Vlahinic@xxxxxxxxxxxxxxxxxx> wrote: > However it is not quite clear to me how I can interpret results from above and e.g. conclude: my_table accumulates x more multixact members since I measured last. You can't. And not just because nobody got around to implementing it yet -- it's quite a fundamental restriction. VACUUM must always make sure of that, and must always scan all unfrozen pages to safely determine that much (it may or may not have to freeze *any* MultiXacts as part of that process, but it must always be sure that no Multis < its final relminmxid remain). > My goal here is to predict where multixact members are growing the fastest so I can perform manual VACUUM FREEZE only on those tables The problem with that strategy is that you still have to do anti-wraparound autovacuums when the mxid_age(relminmxid) of a table crosses the usual threshold, even when in reality there are *zero* MultiXacts in the table (often the case with the largest tables). That's just how it works, unfortunately. There is one piece of good news, though: work in Postgres 15 taught VACUUM to track the oldest extant XID and MXID in the table, and set relfrozenxid and remind to those oldest values (rather than using the cutoffs for freezing, which in general might be much older than the oldest remaining unfrozen XID/MXID). I expect that this will make it much less likely that anti-wraparound autovacuums affecting many tables will all stampede, hurting performance. This will be possible because VACUUM will now be able to set relminmxid to a value that actually tells us something about what's really going on in each table, MultiXact-wise (not just what you set vacuum_multixact_freeze_min_age and autovacuum_multixact_freeze_max_age to in postgresql.conf, which is pretty far removed from what matters most of the time). Simply by noticing that there are no remaining MultiXacts (and probably never were any in the first place) with the larger tables. The timeline for anti-wraparound autovacuums will tend to make a lot more sense for *your* workload, where huge differences in the rate of MultiXact consumption among tables is likely the norm. This still isn't perfect (far from it), but it has the potential to make things far better here. -- Peter Geoghegan
