Search Postgresql Archives

Re: Impact of multixact "members" limit exceeded

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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
> > 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux