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

On Sat, May 2, 2015 at 7:22 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> 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;
>>   }
>>
>> Without that change, autovacuum would trigger repeatedly as we got
>> near 75% SLRU usage but not freeze anything, because
>> default_multixact_freeze_min_age was higher than the age of any tuples
>> (which had only made it to an age of around ~12 million; actually it's
>> not exactly the tuple age per se... I don't fully understand the
>> treatment of locker and updater multixact IDs in the vacuum code,
>> HeapTupleSatisfiesVacuum and heap_freeze_tuple etc yet so I'm not sure
>> exactly how that value translates into vacuum work, but I can see
>> experimentally that a low multixact freeze min age is needed to get
>> relminxmid moved forward).
>>
>> It's good that freeze table age ramps down so that the autovacuum
>> launcher trigger point jumps around a bit and we spread the autovacuum
>> launches over time, but it's not great that we finish up truncating
>> different amounts of multixacts and associated SLRU each time.  We
>> could instead use a freeze min age of 0 to force freezing of *all*
>> tuples if this is a member-space-wraparound-prevention vacuum (that
>> is, if autovacuum_multixact_freeze_max_age !=
>> autovacuum_multixact_freeze_max_age_adjusted()).
>
> We already set vacuum_multixact_freeze_min_age to half of
> autovacuum_multixact_freeze_max_age so that autovacuums to
> prevent MultiXact wraparound won't occur too frequently as per below
> code:
>
> vacuum_set_xid_limits()
> {
> ..
> mxid_freezemin = Min(mxid_freezemin,
>
> autovacuum_multixact_freeze_max_age / 2);
> Assert(mxid_freezemin >= 0);
> ..
> }
>
> Now if we set it to zero, then I think it might lead to excessive
> freezing and inturn more I/O without the actual need (more space
> for multixact members)

That point is certainly worthy of some consideration.  Letting the
freeze xmin get set to half of the (effective)
autovacuum_multixact_freeze_age would certainly be more consistent
with what we do elsewhere.  The policy trade-off is not as
straightforward as you are making it out to be, though:

1. Using a min freeze age of zero will result in half as many
full-table scans, because we'll advance relminmxid twice as far each
time.

2. But each one will freeze more stuff, some of which might have been
updated again before the next freeze pass, so we might do more
freezing in total.

So either policy might win, depending on whether you care more about
reducing reads (in which case you want a very low min freeze age) or
about reducing writes (in which case you want a higher min freeze
age).

All things being equal, I'd rather stick with the existing 50% policy
in the back-branches, rather than going to zero, but I'm not sure all
things are equal.  It matters what difference the higher value makes.

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