Re: pg_multixact not getting truncated

On Sat, Nov 8, 2014 at 3:10 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> I also think our
> defaults for multixact freezing should be tied to the ones for xid
> freezing, and should not by default be completely independent numbers;
> I'm still not convinced that it makes sense to have a separate multixact
> threshold at all **since the same amount of vacuuming needs to be done
> regardless of whether we're truncating xids or mxids**.

That doesn't make any sense at all. For one thing, it's not as if
there is only ONE threshold here. There are three different ones,
controlling three different aspects of the behavior: (a) the age at
which we begin trying to freeze the pages we are planning to vacuum
anyway, (b) the age at which we force a vacuum that we're planning to
do anyway to scan the entire table, and (c) the age at which we
trigger an autovacuum that we weren't otherwise planning to do.

Generally, the reason why I think we need different thresholds for
XIDs and MXIDs is that they may be getting consumed at vastly
different rates. It may be useful to have a light that comes on in
your car when you only have one gallon of fuel left, but you'd want a
different threshold for an airplane because it burns fuel at a
different rate. If you made that light come on when there's a gallon
of fuel left, it would way too late to do any good.

I think a big part of the tuning problem here is that we don't have
any way of knowing what the real burn rates will be in a particular
customer environment. If you're burning MXIDs very slowly, you
probably want to threshold (a), the age at which we begin freezing
pages we are planning to vacuum anyway, quite low, so that the next
full-table vacuum triggered by XID consumption freezes all the MXIDs
also, and advances relminmxid, thus preventing freezing passes
specifically for MXIDs from ever happening. But if the MXID
consumption rate is very high, that may result in unnecessary I/O
freezing tuples that would have been updated before MXID age became an
issue anyway.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company