Excerpts from Simon Riggs's message of lun mar 05 15:28:59 -0300 2012:
>
> On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> >> Regarding performance, the good thing about this patch is that if you
> >> have an operation that used to block, it might now not block. So maybe
> >> multixact-related operation is a bit slower than before, but if it
> >> allows you to continue operating rather than sit waiting until some
> >> other transaction releases you, it's much better.
> >
> > That's probably true, although there is some deferred cost that is
> > hard to account for. You might not block immediately, but then later
> > somebody might block either because the mxact SLRU now needs fsyncs or
> > because they've got to decode an mxid long after the relevant segment
> > has been evicted from the SLRU buffers. In general, it's hard to
> > bound that latter cost, because you only avoid blocking once (when the
> > initial update happens) but you might pay the extra cost of decoding
> > the mxid as many times as the row is read, which could be arbitrarily
> > many. How much of a problem that is in practice, I'm not completely
> > sure, but it has worried me before and it still does. In the worst
> > case scenario, a handful of frequently-accessed rows with MXIDs all of
> > whose members are dead except for the UPDATE they contain could result
> > in continual SLRU cache-thrashing.
>
> Cases I regularly see involve wait times of many seconds.
>
> When this patch helps, it will help performance by algorithmic gains,
> so perhaps x10-100.
>
> That can and should be demonstrated though, I agree.
BTW, the isolation tester cases have a few places that in the unpatched
code die with deadlocks and with the patched code continue without
dying. Others are cases that block in unpatched master, and continue
without blocking in patched. This should be enough proof that there are
"algorithmic gains" here.
There's also a test case that demostrates a fix for the problem (pointed
out by docs) that if you acquire a row lock, then a subxact upgrades it
(say by deleting the row) and the subxact aborts, the original row lock
is lost. With the patched code, the original lock is no longer lost.
I completely agree with the idea that we need some mitigation against
repeated lookups of mxids that contain committed updates.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
