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

On Mon, May 4, 2015 at 5:19 AM, Thomas Munro <thomas.munro@enterprisedb.com>
wrote:
>
> On Sun, May 3, 2015 at 4:40 PM, Amit Kapila <amit.kapila16@gmail.com>
wrote:
> > --
> > +int
> > +compute_max_multixact_age_to_avoid_member_wrap(bool manual)
> > {
> > ..
> > + if (members <= safe_member_count)
> > + {
> > + /*
> > + * There is no danger of
> > member wrap, so return a number that is not
> > + * lower than autovacuum_multixact_freeze_max_age.
> > +
> > */
> > + return -1;
> > + }
> > ..
> >
> > The above code doesn't seem to match its comments.
> > Comment says "..not lower than autovacuum_multixact_freeze_max_age",
> > but then return -1.  It seems to me here we should return unchanged
> > autovacuum_multixact_freeze_max_age as it was coded in the initial
> > version of patch.  Do you have any specific reason to change it?
>
> Oops, the comment is fixed in the attached patch.
>
> In an earlier version, I was only dealing with the autovacuum case.
> Now that the VACUUM command also calls it, I didn't want this
> compute_max_multixact_age_to_avoid_member_wrap function to assume that
> it was being called by autovacuum code and return the
> autovacuum-specific GUC in the case that no special action is needed.
> Also, the function no longer computes a value by scaling
> autovacuum_multixact_freeze_max_age, it now scales the current number
> of active multixacts, so that we can begin selecting a small non-zero
> number of tables to vacuum as soon as we exceed safe_member_count as
> described above

I am slightly worried that if for scaling we don't consider the value for
multixact_*_age as configured by user, Vacuum/Autovacuum might
behave totally different from what user is expecting.  Basically
it will be dominated based on member space usage and will ignore
the values set by user for multixact_*_age parameters.  One way
could be to use minimum of the value calculated based on member
space and the value specified by user for multixact related parameters
as suggested in points 1 and 2 (below in mail).

One more thing, I think the current calculation considers members
usage, shouldn't we try to consider offset usage as well?


> (whereas when we used a scaled down
> autovaccum_multixact_freeze_max_age, we usually didn't select any
> tables at all until we scaled it down a lot, ie until we got close to
> dangerous_member_count).  Finally, I wanted a special value like -1
> for 'none' so that table_recheck_autovac and ExecVacuum could use a
> simple test >= 0 to know that they also need to set
> multixact_freeze_min_age to zero in the case of a
> member-space-triggered vacuum, so that we get maximum benefit from our
> table scans by freezing all relevant tuples, not just some older ones
>

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?

Few more observations:

1.
@@ -2687,6 +2796,10 @@ relation_needs_vacanalyze(Oid relid,
  ? Min(relopts-
>multixact_freeze_max_age, autovacuum_multixact_freeze_max_age)
  :
autovacuum_multixact_freeze_max_age;

+ /* Special settings if we are running out of member address space.
*/
+ if (max_multixact_age_to_avoid_member_wrap >= 0)
+ multixact_freeze_max_age =
max_multixact_age_to_avoid_member_wrap;
+

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

multixact_freeze_max_age = Min(multixact_freeze_max_age,
max_multixact_age_to_avoid_member_wrap);

Similar change needs to be done in table_recheck_autovac()

2.
@@ -1118,7 +1197,12 @@ do_start_worker(void)

  /* Also determine the oldest datminmxid we will consider. */
  recentMulti = ReadNextMultiXactId();
- multiForceLimit = recentMulti - autovacuum_multixact_freeze_max_age;
+ max_multixact_age_to_avoid_member_wrap =
+ compute_max_multixact_age_to_avoid_member_wrap(false);
+ if (max_multixact_age_to_avoid_member_wrap >= 0)
+ multiForceLimit = recentMulti - max_multixact_age_to_avoid_member_wrap;
+ else
+ multiForceLimit = recentMulti - autovacuum_multixact_freeze_max_age;

Here also, isn't it better to use minimum of
autovacuum_multixact_freeze_max_age
and max_multixact_age_to_avoid_member_wrap.

3.
+int
+compute_max_multixact_age_to_avoid_member_wrap(bool manual)
+{
+ MultiXactOffset members;
+ uint32 multixacts;
+ double fraction;
+ MultiXactOffset safe_member_count = MaxMultiXactOffset / 2;

It is not completely clear what is more appropriate value
for safe_member_count (25% or 50%).  Anybody else have any
opinion on this value?

4. Once we conclude on final algorithm, we should update the
same in docs as well, probably in description at below link:
http://www.postgresql.org/docs/devel/static/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com