Re: pg_multixact not getting truncated

Jim Nasby wrote:
> On 11/10/14, 12:16 AM, Josh Berkus wrote:
> >On 11/09/2014 08:00 PM, Josh Berkus wrote:
> >On 11/08/2014 01:46 PM, Andres Freund wrote:
> >>>I'm these days suggesting that people should add manual vacuuming for
> >>>>"older" relations during off peak hours on busy databases. There's too
> >>>>many sites which service degrades noticeably during a full table vacuum.
> >>Me too: https://github.com/pgexperts/flexible-freeze
> >
> >It turns out that not even a program of preventative scheduled vacuuming
> >helps. This is because the template0 database anchors the minmxid and
> >prevents it from being advanced until autovacuum gets around to that
> >database, at whatever the minmxid threshold is.
>
> How did template0 even get a MultiXact? That sounds like they're really abusing the template databases. :( (Do keep in mind that MXID 1 is a special value.)

No, it's normal -- template0 does not have a multixact in any tuple's
xmax, but datminxid is set to the value that is current when it is
frozen.

> BTW, the only reason I know of not to set both min_age parameters to
> zero is to prevent loss of forensic information. If that's not a
> concern you can always just set them to zero. Even if it is a concern,
> I suspect that the forensic info you could gather from a MultiXact is
> a lot more limited than for an XID, so it's probably pretty safe
> setting that to zero.

Freezing tuples too early could cause useless dirtying of pages; if the
tuple is deleted, updated or locked again after being frozen, you end up
with more writes.

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