DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=s12DJJdVpcnt2Zv5mTxnzPPO0mVIspXhLRtwrWHvxrx61B4NkKsmk+dft6KUdqQ4GA
	NlrYjQeUzD2hu6PjO9qtormIHwnh84+8cOslkoJB0QN82RpHck31jtW6N5n5fcPVnVn0
	TQkCOl06hfsRkjISTbysVB8oQ3qIT5G9L6rrk=
MIME-Version: 1.0
In-Reply-To: <C5E680C6.3790%scott@richrelevance.com>
References: <603c8f070903121829r31cf1472ha9876e5c480a542e@mail.gmail.com>
	<C5E680C6.3790%scott@richrelevance.com>
Date: Wed, 18 Mar 2009 17:25:10 -0400
Message-ID: <603c8f070903181425r6fff6f7eq11e2dac6e867a7d0@mail.gmail.com>
Subject: Re: Proposal of tunable fix for scalability of 8.4
From: Robert Haas <robertmhaas@gmail.com>
To: Scott Carey <scott@richrelevance.com>
Cc: Greg Smith <gsmith@gregsmith.com>, "Jignesh K. Shah" <J.K.Shah@sun.com>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Mar 18, 2009 at 1:43 PM, Scott Carey <scott@richrelevance.com> wrot=
e:
>>> Its worth ruling out given that even if the likelihood is small, the fi=
x is
>>> easy. =A0However, I don=B9t see the throughput drop from peak as more
>>> concurrency is added that is the hallmark of this problem < usually wit=
h a
>>> lot of context switching and a sudden increase in CPU use per transacti=
on.
>>
>> The problem is that the proposed "fix" bears a strong resemblence to
>> attempting to improve your gas mileage by removing a few non-critical
>> parts from your card, like, say, the bumpers, muffler, turn signals,
>> windshield wipers, and emergency brake.
>
> The fix I was referring to as easy was using a connection pooler -- as a
> reply to the previous post. Even if its a low likelihood that the connect=
ion
> pooler fixes this case, its worth looking at.

Oh, OK.  There seem to be some smart people saying that's a pretty
high-likelihood fix.  I thought you were talking about the proposed
locking change.

>> While it's true that the car
>> might be drivable in that condition (as long as nothing unexpected
>> happens), you're going to have a hard time convincing the manufacturer
>> to offer that as an options package.
>
> The original poster's request is for a config parameter, for experimentat=
ion
> and testing by the brave. My own request was for that version of the lock=
 to
> prevent possible starvation but improve performance by unlocking all shar=
ed
> at once, then doing all exclusives one at a time next, etc.

That doesn't prevent starvation in general, although it will for some workl=
oads.

Anyway, it seems rather pointless to add a config parameter that isn't
at all safe, and adds overhead to a critical part of the system for
people who don't use it.  After all, if you find that it helps, what
are you going to do?  Turn it on in production?  I just don't see how
this is any good other than as a thought-experiment.

At any rate, as I understand it, even after Jignesh eliminated the
waits, he wasn't able to push his CPU utilization above 48%.  Surely
something's not right there.  And he also said that when he added a
knob to control the behavior, he got a performance improvement even
when the knob was set to 0, which corresponds to the behavior we have
already anyway.  So I'm very skeptical that there's something wrong
with either the system or the test.  Until that's understood and
fixed, I don't think that looking at the numbers is worth much.

> I alluded to the three main ways of dealing with lock contention elsewher=
e.
> Avoiding locks, making finer grained locks, and making locks faster.
> All are worthy. =A0Some are harder to do than others. =A0Some have been h=
eavily
> tuned already. =A0Its a case by case basis. =A0And regardless, the unfair=
 lock
> is a good test tool.

In view of the caveats above, I'll give that a firm maybe.

...Robert