Re: pg_multixact not getting truncated

On 11/07/2014 04:43 PM, Alvaro Herrera wrote:
> This says that the live multixact range goes from 123 million to 162
> million; roughly 40 million values.  (The default value for
> vacuum_multixact_freeze_table_age is 150 million, which is what
> determines how many values are kept.)
> 
> You gist.github paste tells us there are 4598 members files.  Each file
> has 32 pages, and each page hosts 2045 members; so there are 32 * 2045 *
> 4598 members, or somewhat about 300 million.  For 40 million
> multixacts, this means there are about 7 members per multixact, in
> average, which seems a reasonable number to me.

So the basic problem is that multixact files are just huge, with an
average of 35 bytes per multixact?

> If you want to have vacuum truncate pg_multixact more aggresively, you
> need to decrease vacuum_multixact_freeze_table_age and
> vacuum_multixact_freeze_min_age.

If that's the case, then we need to set the defaults more aggressively.I suggest maybe 10 million.  The alternative is
allowingit to creep up
 
to 150million, which would be 5GB.  I don't see adding 5GB to user
databases without warning them as good behavior.

Of course, this will lead to LOTs of additional vacuuming ...

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com