Re: MultiXact pessmization in 9.3

On 2013-11-22 12:04:31 -0300, Alvaro Herrera wrote:
> Andres Freund wrote:
>
> > While looking at the multixact truncation code (mail nearby), I've
> > noticed that there's a significant difference in the way multixact
> > members are accessed since fkey locks were introduced:
> >
> > <9.3 when heap_lock_tuple finds a XMAX_IS_MULTI tuple it executes
> > MultiXactIdWait() which in turn uses GetMultiXactIdMembers() to get the
> > xids to wait for. But it skips the lookup if the mxid we lookup is older
> > than OldestVisibleMXactId.
> >
> > 9.3+ instead always looks up the members because GetMultiXactIdMembers()
> > is also used in cases where we need to access old xids (to check whether
> > members have commited in non-key updates).
>
> But HeapTupleSatisfiesUpdate(), called at the top of heap_lock_tuple,
> has already called MultiXactIdIsRunning() (which calls GetMembers)
> before that's even considered. So the call heap_lock_tuple should have
> members obtained from the multixact.c cache.
>
> Am I misunderstanding which code path you mean?

Yes, somewhat: <9.3 GetMultiXactIdMembers() didn't do any work if the
multixact was old enough:
if (MultiXactIdPrecedes(multi, OldestVisibleMXactId[MyBackendId]))
{
debug_elog2(DEBUG2, "GetMembers: it's too old");
*xids = NULL;
return -1;
}
so, in OLTP style workloads, doing a heap_lock_tuple() often didn't have
to perform any IO when locking a tuple that previously had been locked
using a multixact. We knew that none of the members could still be
running and thus didn't have to ask the SLRU for them since a
not-running member cannot still have a row locked.

Now, fkey locks changed that because for !HEAP_XMAX_LOCK_ONLY mxacts, we
need to look up the members to get the update xid and check whether it
has committed or aborted, even if we know that it isn't currently
running anymore due do OldestVisibleMXactId. But there's absolutely no
need to do that for HEAP_XMAX_LOCK_ONLY mxacts, the old reasoning for
not looking up the members if old is just fine.

Additionally, we don't ever set hint bits indicating that a
HEAP_XMAX_IS_MULTI & !HEAP_XMAX_LOCK_ONLY mxact has commited, so we'll
do HeapTupleGetUpdateXid(), TransactionIdIsCurrentTransactionId(),
TransactionIdIsInProgress(), TransactionIdDidCommit() calls for every
HeapTupleSatisfiesMVCC().

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services