Re: truncating pg_multixact/members

On Fri, Jan 3, 2014 at 9:11 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> 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.

Yeah, this stuff is definitely underdocumented relative to vacuum right now.

As far as back-patching the GUCs, my thought would be to back-patch
them but mark them GUC_NOT_IN_SAMPLE in 9.3, so we don't have to touch
the default postgresql.conf.

Also, while multixactid_freeze_min_age should be low, perhaps a
million as you suggest, multixactid_freeze_table_age should NOT be
lowered to 3 million or anything like it. If you do that, people who
are actually doing lots of row locking will start getting many more
full-table scans. We want to avoid that at all cost. I'd probably
make the default the same as for vacuum_freeze_table_age, so that
mxids only cause extra full-table scans if they're being used more
quickly than xids.

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