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

On Wed, Apr 29, 2015 at 5:44 AM, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
wrote:
>
> On Tue, Apr 28, 2015 at 6:30 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> > Tomorrow I will send a separate patch for the autovacuum changes that
> > I sent earlier. Let's discuss and hopefully eventually commit that
> > separately.
>
> Here is a separate patch which makes autovacuum start a wrap-around
> vacuum sooner if the member space is running out, by adjusting
> autovacuum_multixact_freeze_max_age using a progressive scaling
> factor. This version includes a clearer implementation of
> autovacuum_multixact_freeze_max_age_adjusted provided by Kevin
> Grittner off-list.
>

Some comments:

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?

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?

3. currently there is some minimum limit of autovacuum_multixact_freeze_age
(10000000)
which might not be honored by this calculation, so not sure if that can
impact the
system performance in some cases where it is currently working sane.

4. Can you please share results that can show improvement
with current patch versus un-patched master?

5.
+ /*
+ * TODO: In future, could oldestMultiXactMemberOffset be stored in shmem,
+ *
pg_controdata, alongside oldestMultiXactId?
+ */

You might want to write the comment as:
XXX: We can store oldestMultiXactMemberOffset in shmem, pg_controldata
alongside oldestMultiXactId?

6.
+ * Returns vacuum_multixact_freeze_max_age, adjusted down to prevent
excessive use
+ * of addressable
multixact member space if required.

I think here you mean autovacuum_multixact_freeze_max_age?

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