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.
Yes they did, they all advanced, and no they are not younger than 100 million. They were all over the default value of 150 as are the remaining 2, relminxid/relfrozenid advanced, relminxid to match NextMultiXactID and age went down. But good point to keep in mind.
Best regards
Daniel
On Thu, Jun 14, 2018 at 7:15 PM, Alvaro Herrera <alvherre@xxxxxxxxxxxxxxx> wrote:
On 2018-Jun-14, Daniel Lagerman wrote:
> > Set vacuum_multixact_freeze_table_age to 0 (causing all vacuums to scan Did you observe whether the vacuumed tables' relminmxid advance? If it
> > 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.
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
