Re: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated) - Mailing list pgsql-bugs

On Thu, Apr 30, 2015 at 5:17 PM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> On Wed, Apr 29, 2015 at 11:41 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> 1. It seems that you are using
>> autovacuum_multixact_freeze_max_age_adjusted()
>> only at couple of places, like it is not used in below calc:
>>
>> vacuum_set_xid_limits()
>> {
>> ..
>> mxid_freezemin = Min(mxid_freezemin,
>> autovacuum_multixact_freeze_max_age / 2);
>> ..
>> }
>>
>> What is the reason of using this calculation at some places and
>> not at other places?
>
> You're right, I will review the other places where that variable is
> used and come back on this point.

Those other places are for capping the effective table and tuple
multixact freeze ages for manual vacuums, so that manual vacuums (say
in nightly cronjobs) get a chance to run a wraparound scans before
autovacuum kicks in at a less convenient time.  So, yeah, I think we
want to incorporate member wraparound prevention into that logic, and
I will add that in the next version of the patch.

>> 2.
>> @@ -2684,8 +2719,8 @@ relation_needs_vacanalyze(Oid relid,
>>   : autovacuum_freeze_max_age;
>>
>>   multixact_freeze_max_age = (relopts && relopts->multixact_freeze_max_age
>>>= 0)
>> - ? Min(relopts->multixact_freeze_max_age,
>> autovacuum_multixact_freeze_max_age)
>> - : autovacuum_multixact_freeze_max_age;
>> + ? Min(relopts->multixact_freeze_max_age,
>> autovacuum_multixact_freeze_max_age_adjusted())
>> + : autovacuum_multixact_freeze_max_age_adjusted();
>>
>>
>> It seems that it will try to read from offset file for each
>> relation which might or might not be good, shall we try to
>> cache the oldestMultiXactMemberOffset?
>
> True.  I will look into this.

The version attached reuses the value by passing it as a new parameter
to the functions relation_needs_vacanalyze and table_recheck_autovac.

[...]

>> 4. Can you please share results that can show improvement
>> with current patch versus un-patched master?
> [...]
> 2.  We get to somewhere in the 73-75% SLRU used range before
> wraparound vacuums are triggered.  We probably need to spread things
> out more that that.

The version attached should spread the work out a lot better.  Instead
of using a proportion of your autovacuum_multixact_max_freeze_age, it
uses a proportion of the number of active multixacts (see
compute_max_multixact_age_to_avoid_member_wrap).

> 3.  When the autovacuum runs, it advances oldest_mxid by different
> amounts each time; that's because I'm using the adjusted freeze max
> age (the max age of a table before it gets a wraparound vacuum) as our
> freeze min age (the max age for individual tuples before they're
> frozen) here:
>
> @@ -1931,7 +1964,9 @@ do_autovacuum(void)
>   {
>   default_freeze_min_age = vacuum_freeze_min_age;
>   default_freeze_table_age = vacuum_freeze_table_age;
> - default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age;
> + default_multixact_freeze_min_age =
> + Min(vacuum_multixact_freeze_min_age,
> + autovacuum_multixact_freeze_max_age_adjusted());
>   default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age;
>   }

That was just completely wrong...  removed, and replaced with code
that sets multixact_freeze_min_age to zero in table_recheck_autovac in
this scenario.

I will post another version with some more test results soon, but
wanted to share this WIP patch and respond to the above questions.

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