Re: BUG #8470: 9.3 locking/subtransaction performance regression

Oskari Saarenmaa wrote:
> Thanks for looking at this and sorry about the late reply, I finally got
> around to testing your latest patch.
>
> 20.12.2013 22:47, Alvaro Herrera kirjoitti:
> >> Removing the BEGIN/EXCEPTION/END block and just doing a 'SELECT FOR
> >> UPDATE' for a suitable row is significantly slower in 9.3.0 (314.765 ms
> >> vs 118.894 ms on 9.2.4).  A 'SELECT' without a FOR UPDATE and
> >> BEGIN/EXCEPTION/END has the same performance on 9.2.4 and 9.3.0.
> >
> > I have come up with the attached patch.  As far as I can tell it
> > restores performance roughly to the level of 9.2 for this test case;
> > could you please try it out and see if it fixes things for you?  It'd be
> > particularly good if you can check not only the test case you submitted
> > but also the one that made you explore this in the first place.
>
> I didn't try this patch, but I built today's REL9_3_STABLE with the patch
> from your mail on the 31st of Dec
> (http://github.com/saaros/postgres/tree/alvaro-multixact-optimize-9.3) and
> ran the older version of my appliaction's test suite which has a case that
> times out after 3 minutes with unpatched 9.3.  With the current patch the
> test case successfully completes in ~20 seconds which is a major improvement
> although it's still slower than 9.2  It also passes all other test cases in
> my test suite.
>
> Do you think it'll make it to 9.3.3?

Well, I gave this patch a long, careful look and eventually noticed that
there was a rather subtle but serious design bug in it, and I had to
ditch it.  I have the intention to come back to this problem again
later, but right now I cannot.

I'm not sure whether I will have something for 9.3.3, but it's not
likely because that'll happen only in a week or two.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services