Re: pg_multixact not getting truncated

On 2014-11-07 17:20:44 -0800, Josh Berkus wrote:
> 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?

Depends on the concurrency. The number of members is determined by the
number of xacts concurrenly locking a row.

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

Why? If you have that high transaction volume, a few seldomly read files
won't hurt you.

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

Yes. And that's likely to cause much, much more grief.

Also. Didn't you just *vehemently* oppose making these values tunable at
all?

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services