Re: truncating pg_multixact/members

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company