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

Here's a new patch, with responses to several reviews.

On Tue, May 5, 2015 at 8:41 AM, Kevin Grittner <kgrittn@ymail.com> wrote:
> I really don't like the "honor the configured value of
> vacuum_multixact_freeze_min_age until the members SLRU gets to 50%
> of wraparound and then use zero" approach.  It made a lot more
> sense to me to honor the configured value to 25% and decrease it in
> a linear fashion until it hit zero at 75%.  It seems like maybe we
> weren't aggressive enough in the dynamic adjustment of
> autovacuum_multixact_freeze_max_age, but I'm not clear why fixing
> that required the less gradual adjustment of the *_min_age setting.

Ok, the new patch uses 25% as the safe threshold, and then scales
multixact_freeze_table_age down from the current number of active
multixacts (ie to select the minimum number of tables) progressively
to 0 (to select all tables) when you reach 75% usage.  It also sets
multixact_freeze_min_age to half of the value it used for
multixact_table_age, so that reduces progressively too.  But see my
response to Robert below for discussion (question) about how to make
safe threshold depend on autovacuum_multixact_freeze_max_age.

On Tue, May 5, 2015 at 8:37 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Robert Haas wrote:
>> Anyway, solving that problem would be nice (particularly for xmin!),
>> but we shouldn't get into that with relation to this bug fix.  It's a
>> problem, but one that will probably take a good deal of work to solve,
>> and certainly not something we would back-patch.
>
> +1

OK, not attempting to do anything about that.  If your database has a
bunch of tables with the same relminmxid (for example after a recent
restore from pg_dump), you're going to get vacuums starting at the
same time.  If they're more scrambled up, you'll get your vacuum work
spread out better, and that is just as it is for normal mxid
wraparound prevention so that is OK for the purposes of this patch.

On Tue, May 5, 2015 at 6:46 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> I think the 75% threshold for reducing multxact_freeze_table_age to
> zero is fine, but I don't agree with the 50% cutoff.  The purpose of
> autovacuum_multixact_freeze_max_age is to control the fraction of the
> 2^32-entry offset space that can be consumed before we begin viewing
> the problem as urgent.  We have a setting for that because it needs to
> be tunable, and the default value for that setting is 400 million,
> which is roughly 10% of the members space.  That is a whole lot lower
> than the 50% threshold you are proposing here.  Moreover, it leaves
> the user with no meaningful choice: if the 50% threshold consumes too
> much disk space, or doesn't leave enough room before we hit the wall,
> then the user is simply hosed.  This is why I initially proposed that
> the member-space-consumption-percentage at which we start derating
> multixact_freeze_table_age should be based on
> autovacuum_multixact_freeze_max_age/2^32.  That way,
> autovacuum_multixact_freeze_max_age controls not only how aggressively
> we try to reclaim offset space but also how aggressively we try to
> reclaim member space.  The user can then tune the value, and the
> default is the same in both cases.

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?  If
it's 1, then we are only allowed 400 million members for our 400
million multixacts, which is not enough, if I am right in assuming
that multixacts (nearly?) always have 2 or more members: a setting of
400 million would trigger wraparound at 200 million multixacts (or
less) for *everyone* and trigger wraparound scans for some users as
soon as they upgrade.  If it's 2, then anyone exceeding an average of
2 members would get their effective autovacuum_freeze_max_age lowered.
If it's 8, then people using the default max age of 400 million will
hit the 75% usage threshold that we have decided should trigger
freeze-everything-mode just as they hit the cutoff at which we start
doing anything, and for people who have raised the default value it
would be after (ahh, I might have the tests the wrong way around for
this scenario in MultiXactCheckMemberUsage() in the current patch...).
What do we know about expected multixact sizes in the wild, and what
sort of values people tend to set their
autovacuum_multixact_freeze_max_age to?  Does anyone have a principled
suggestion for some_constant?

> I also think that halving the numbers for manual vacuums is arbitrary
> and unprecedented.  The thought process isn't bad, but an autovacuum
> currently behaves in most respects like a manual vacuum, and I'm
> reluctant to make those more different.

Ok, removed.

On Tue, May 5, 2015 at 9:30 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> the values would be at their normal defaults when the wraparound is
> distant, and the multiplier would start to become slightly less than 1
> as the counter moves towards wraparound; by the time we're at at an
> emergency i.e. we reach max_freeze_age, the values naturally become zero
> (or perhaps just before we reach max_freeze_age, the values were 50% of
> their normal values, so the drop to zero is not as dramatic).  Since
> this is gradual, the behavior is not as jumpy as in the proposed patch.
>
> Anyway this is in line with what Kevin is saying elsewhere: we shouldn't
> just use the normal values all the time just up to the freeze_max_age
> point; there should be some gradual ramp-up.

Ok, in the new patch multixact_freeze_min_age is ramped.

> Perhaps we can combine this with the other idea of using a multiplier
> connected to average size of multixact, if it doesn't become too
> complicated, surprising, or both.

What bothers me about this is that the average multixact size may
suddenly change, for example when new application code rolls out or a
nightly job runs, leading to a sudden change in member space
consumption, you might get in trouble by trying to be too clever with
predictions instead of just using something really simple based on
thresholds.  But I might not be understanding what you meant...

On Mon, May 4, 2015 at 6:25 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> I think setting multixact_freeze_min_age to zero could be too aggresive
> for I/O.  Yes with this you can get maximum benefit, but at cost of
> increased I/O.  How would you justify setting it to zero as appropriate
> w.r.t increased I/O?

Changed back to ramped, see above.

> Isn't it better to use minimum to already computed value of
> multixact_freeze_max_age and max_multixact_age_to_avoid_member_wrap?

Ok, done.

On Tue, May 5, 2015 at 8:59 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> As a secondary point, I find variable-names-as-documentation bad
> practice.  Please don't use a long name such as
> max_multixact_age_to_avoid_member_wrap; code becomes unwieldy.  A short
> name such as safe_mxact_age preceded by a comment /* this variable is
> the max that avoids member wrap */ seems more palatable; side-by-side
> merges and all that!  I don't think long function names are as
> problematic (though the name of your new function is still a bit too
> long).

I changed the function name to MultiXactCheckMemberUsage and moved
into multixact.c, and changed the variable name in various places to
safe_multixact_age.  There were already a few comments explaining its
purpose.

> Please note that 9.4 and earlier do not have ExecVacuum; the
> determination of freeze ages is done partly in gram.y (yuck).  Not sure
> what will the patch look like in those branches.

Autovacuum seems to parameterize vacuum via parser statements (!) so
why not just do the same if we detect that we arrived in vacuum via a
user command, and it wasn't a VACUUM FREEZE?  In that case I think the
values arrive as -1.  Like in the attached patch against 9.3 (not
tested).

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