Re: truncating pg_multixact/members

Robert Haas escribió:
> On Mon, Dec 30, 2013 at 10:59 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)com> wrote:
> > One problem I see is length of time before freezing multis: they live
> > for far too long, causing the SLRU files to eat way too much disk space.
> > I ran burnmulti in a loop, creating multis of 3 members each, with a min
> > freeze age of 50 million, and this leads to ~770 files in
> > pg_multixact/offsets and ~2900 files in pg_multixact/members. Each file
> > is 32 pages long. 256kB apiece. Probably enough to be bothersome.
> >
> > I think for computing the freezing point for multis, we should slash
> > min_freeze_age by 10 or something like that. Or just set a hardcoded
> > one million.
>
> Yeah. Since we expect mxids to be composed at a much lower rate than
> xids, we can keep pg_multixact small without needing to increase the
> rate of full table scans. However, it seems to me that we ought to
> have GUCs for mxid_freeze_table_age and mxid_freeze_min_age. There's
> no principled way to derive those values from the corresponding values
> for XIDs, and I can't see any reason to suppose that we know how to
> auto-tune brand new values better than we know how to auto-tune their
> XID equivalents that we've had for years.
>
> One million is probably a reasonable default for mxid_freeze_min_age, though.

I didn't want to propose having new GUCs, but if there's no love for my
idea of deriving it from the Xid freeze policy, I guess it's the only
solution. Just keep in mind we will need to back-patch these new GUCs
to 9.3. Are there objections to this?

Also, what would be good names? Peter E. complained recently about the
word MultiXactId being exposed in some error messages; maybe "mxid" is
too short an abbreviation of that. Perhaps
multixactid_freeze_min_age = 1 million
multixactid_freeze_table_age = 3 million
?
I imagine this stuff would be described somewhere in the docs, perhaps
within the "routine maintenance" section somewhere.

FWIW the idea of having a glossary sounds good to me.

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