Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated)

On Wed, May 6, 2015 at 9:26 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, May 5, 2015 at 3:58 AM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>> Ok, so if you have autovacuum_freeze_max_age = 400 million multixacts
>> before wraparound vacuum, which is ~10% of 2^32, we would interpret
>> that to mean 400 million multixacts OR ~10% * some_constant of member
>> space, in other worlds autovacuum_freeze_max_age * some_constant
>> members, whichever comes first. But what should some_constant be?
>
> some_constant should be all the member space there is. So we trigger
> autovac if we've used more than ~10% of the offsets OR more than ~10%
> of the members. Why is autovacuum_multixact_freeze_max_age
> configurable in the place? It's configurable so that you can set it
> low enough that wraparound scans complete and advance the minmxid
> before you hit the wall, but high enough to avoid excessive scanning.
> The only problem is that it only lets you configure the amount of
> headroom you need for offsets, not members. If you squint at what I'm
> proposing the right way, it's essentially that that GUC should control
> both of those things.

But member space *always* grows at least twice as fast as offset space
(aka active multixact IDs), because multixacts always have at least 2
members (except in some rare cases IIUC), don't they? So if we do
what you just said, then we'll trigger wraparound vacuums twice as
soon as we do now for everybody, even people who don't have any
problem with member space management. We don't want this patch to
change anything for most people, let alone everyone. So I think that
some_constant should be at least 2, if we try to do it this way, in
other words if you set the GUC for 10% of offset space, we also start
triggering wraparounds at 20% of member space. The code in
MultiXactCheckMemberSpace would just say safe_member_count =
autovacum_multixact_freeze_max_age * 2, where 2 is some_constant (this
number is the average number of multixact members below which your
workload will be unaffected by the new autovac behaviour).

--
Thomas Munro
http://www.enterprisedb.com