Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6DAB663343B for ; Fri, 13 Mar 2009 15:39:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 53153-02-5 for ; Fri, 13 Mar 2009 15:39:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-2.exch018.msoutlookonline.net (exhub018-2.exch018.msoutlookonline.net [64.78.17.17]) by mail.postgresql.org (Postfix) with ESMTP id 72FBF633EAC for ; Fri, 13 Mar 2009 15:39:01 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-2.exch018.msoutlookonline.net ([64.78.17.17]) with mapi; Fri, 13 Mar 2009 11:38:59 -0700 From: Scott Carey To: Kevin Grittner , Robert Haas , Tom Lane CC: Greg Smith , "pgsql-performance@postgresql.org" , "Jignesh K. Shah" Date: Fri, 13 Mar 2009 11:38:57 -0700 Subject: Re: Proposal of tunable fix for scalability of 8.4 Thread-Topic: [PERFORM] Proposal of tunable fix for scalability of 8.4 Thread-Index: AcmkBecS+N9PQfcuQ+G08i7vbKr2EQABRCvG Message-ID: In-Reply-To: <49BA5960.EE98.0025.0@wicourts.gov> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DFF6513439scottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.000, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/173 X-Sequence-Number: 33095 --_000_C5DFF6513439scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Its an interesting question, but the answer is most likely simply that the = client can't keep up. And in the real world, no matter how incredible your= connection pool is, there will be some inefficiency, there will be some ne= twork delay, there will be some client side time, etc. I'm still not sure if we are dealing with a 64 or 128 thread machine too. The average query finishes in 6ms according to the result., so any bit of n= etwork latency will multiply the number of connections needed to saturate, = and any small delay in the client between queries, or going through a resul= t set, will make it hard to have a 100% duty cycle. The test result with zero delay stopped linear increase in performance at a= bout 128 users and 7ms average query response time, at ~2100 queries per se= cond. If this is a 128 thread machine, then that means the clients are pre= tty fast. If its a 64 thread machine, it means the clients can provide abo= ut a 50% duty cycle time, which is not horrible. This is 16.5 queries per second per client, or an average time per (query p= lus client delay) of 1/16.5 =3D ~6ms. That is to say, either this is a 128 thread machine, or the test harness is= measuring average response time and including client side delay and thus t= here is a 50% duty cycle time and ~3ms client delay per request. What would really help is a counter that tracks active postgres connection = count so one can look at that compared to the total connection count. Idle= count and idle in transaction count would also be hugely useful to be able= to track as a dynamic statistic or counter for load testing. For all of t= hese, an average value over the last second or so is much better than an in= stantaneous count for these purposes. On 3/13/09 11:02 AM, "Kevin Grittner" wrote: Tom Lane wrote: > Robert Haas writes: >> I think that changing the locking behavior is attacking the problem >> at the wrong level anyway. > > Right. By the time a patch here could have any effect, you've > already lost the game --- having to deschedule and reschedule a > process is a large cost compared to the typical lock hold time for > most LWLocks. So it would be better to look at how to avoid > blocking in the first place. That's what motivated my request for a profile of the "80 clients with zero wait" case. If all data access is in RAM, why can't 80 processes keep 64 threads (on 8 processors) busy? Does anybody else think that's an interesting question, or am I off in left field here? -Kevin --_000_C5DFF6513439scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Proposal of tunable fix for scalability of 8.4 Its an interesting question, but the answer is most likely simply tha= t the client can’t keep up.  And in the real world, no matter ho= w incredible your connection pool is, there will be some inefficiency, ther= e will be some network delay, there will be some client side time, etc.

I’m still not sure if we are dealing with a 64 or 128 thread machine = too.

The average query finishes in 6ms according to the result., so any bit of n= etwork latency will multiply the number of connections needed to saturate, = and any small delay in the client between queries, or going through a resul= t set, will make it hard to have a 100% duty cycle.

The test result with zero delay stopped linear increase in performance at a= bout 128 users and 7ms average query response time, at ~2100 queries per se= cond.  If this is a 128 thread machine, then that means the clients ar= e pretty fast.  If its a 64 thread machine, it means the clients can p= rovide about a 50% duty cycle time, which is not horrible.
This is 16.5 queries per second per client, or an average time per (query p= lus client delay) of 1/16.5 =3D ~6ms.
That is to say, either this is a 128 thread machine, or the test harness is= measuring average response time and including client side delay and thus t= here is a 50% duty cycle time and ~3ms client delay per request.

What would really help is a counter that tracks active postgres connection = count so one can look at that compared to the total connection count.  = ;Idle count and idle in transaction count would also be hugely useful to be= able to track as a dynamic statistic or counter for load testing.  Fo= r all of these, an average value over the last second or so is much better = than an instantaneous count for these purposes.


On 3/13/09 11:02 AM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

Tom Lane <= tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.co= m> writes:
>> I think that changing the locking behavior is attacking the proble= m
>> at the wrong level anyway.
>
> Right.  By the time a patch here could have any effect, you've > already lost the game --- having to deschedule and reschedule a
> process is a large cost compared to the typical lock hold time for
> most LWLocks.  So it would be better to look at how to avoid
> blocking in the first place.

That's what motivated my request for a profile of the "80 clients with=
zero wait" case.  If all data access is in RAM, why can't 80 proc= esses
keep 64 threads (on 8 processors) busy?  Does anybody else think
that's an interesting question, or am I off in left field here?

-Kevin

--_000_C5DFF6513439scottrichrelevancecom_--