Re: Impact of multixact "members" limit exceeded

On 2018-Jun-14, Daniel Lagerman wrote:

> > Set vacuum_multixact_freeze_table_age to 0 (causing all vacuums to scan
> > tables fully) and vacuum_multixact_freeze_min_age to some value like one
> > million (so that they remove most of the oldest multixacts, leaving
> > just the frontmost one million). Then vacuum the tables with the
> > highest multixact ages. Make sure the datminmxid advances in all
> > databases (incl. postgres template0 template1); once it has advanced,
> > the oldest member files are removed. Don't waste time processing tables
> > with datminmxid higher than the minimum, as that won't free up any
> > member space.
>
> Thanks, we opted to go with 1 million min and 100 million table age, the
> tables in question are all way over that anyway. We completed vacuum on all
> but two which are larger tables and I'm running Vacuum there right now
> after I also bumped the maintenance work mem a bit. As this is 9.4 I can't
> see the progress except that I noted that initialy it uses less CPU and
> once it seems to finnaly start working it uses more CPU 10%->50% but that
> is fine. So my hope is that the Vacuum completes over night.

Did you observe whether the vacuumed tables' relminmxid advance? If it
didn't, then those vacuums are a waste of time.

Note that freeze_table_age affects when vacuum does a full table scan
vs. when it skips pages due to visibility map. A vacuum that does a
partial scan does *not* advance the relfrozenxid / relminmxid; only
full-table scan vacuums can do that. (Except in 9.6.) If the
relminmxid ages are younger than your 100 million table_age, vacuum
won't do a full-table scan.

Cheers

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services