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

On Thu, May 7, 2015 at 2:44 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On May 6, 2015, at 9:48 PM, Thomas Munro <thomas.munro@enterprisedb.com> wrote:
>>> On Thu, May 7, 2015 at 4:23 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> On Wed, May 6, 2015 at 10:34 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>>>>> 2. Doesn't the code that sets MultiXactState->multiVacLimit also need
>>>>> to use what I'm now calling MultiXactMemberFreezeThreshold() - or some
>>>>> similar logic?  Otherwise, a user with autovacuum=off won't get
>>>>> emergency autovacuums for member exhaustion, even though they will get
>>>>> them for offset exhaustion.
>>>>
>>>> Yeah, it looks like it does.
>>>
>>> OK, I'm not clear how to do that correctly, exactly, but hopefully one
>>> of us can figure that out.
>>
>> MultiXactState->multiVacLimit holds the multixact IDs at which an age
>> thresholds will be crossed, so that GetNewMultiXactId can check it
>> cheaply.  But we can't predict the future multixact IDs at which our
>> member usage threshold will be crossed.  We could try to estimate it
>> based on past multixact sizes, but (as I think we already covered
>> somewhere else) we shouldn't be trying to do that because it wouldn't
>> handle the situation where your member space consumption rate suddenly
>> went up, among other problems.
>>
>> How about this:  we add oldestOffset to MultiXactState, to be set by
>> DetermineSafeOldestOffset, and then at the place where
>> GetNewMultiXactId checks if (!MultiXactIdPrecedes(result,
>> MultiXactState->multiVacLimit) it could also check whether (nextOffset
>> - MultiXactState->oldestOffset > MULTIXACT_MEMBER_SAFE_THRESHOLD).
>> ReadMultiXactCounts should also use the oldestOffset value directly
>> from shmem instead of calling find_multixact_start.
>
> That sounds pretty good.

See attached patch, based on your multixact-av.patch.  With autovacuum
set to off, it vacuums as expected.  I wonder if
DetermineSafeOdlestOffset is being called in all the right places to
guarantee that the state is initialised.

This patch will change it anyway, but I noticed that oldestOffset's
computation to find the start of the segment seems wrong in master, I
think it should be like this, no?

@@ -2495,7 +2495,7 @@ DetermineSafeOldestOffset(MultiXactId oldestMXact)
         */
        oldestOffset = find_multixact_start(oldestMXact);
        /* move back to start of the corresponding segment */
-       oldestOffset -= oldestOffset / MULTIXACT_MEMBERS_PER_PAGE *
SLRU_PAGES_PER_SEGMENT;
+       oldestOffset -= oldestOffset % (MULTIXACT_MEMBERS_PER_PAGE *
SLRU_PAGES_PER_SEGMENT);

        LWLockAcquire(MultiXactGenLock, LW_EXCLUSIVE);
        /* always leave one segment before the wraparound point */

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