Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7AB26632F38 for ; Thu, 12 Mar 2009 19:16:19 -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 55954-05 for ; Thu, 12 Mar 2009 19:15:55 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-4.exch018.msoutlookonline.net (exhub018-4.exch018.msoutlookonline.net [64.78.17.19]) by mail.postgresql.org (Postfix) with ESMTP id 9AE81632DFD for ; Thu, 12 Mar 2009 19:15:55 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-4.exch018.msoutlookonline.net ([64.78.17.19]) with mapi; Thu, 12 Mar 2009 15:15:53 -0700 From: Scott Carey To: Greg Smith , "Jignesh K. Shah" CC: Kevin Grittner , "pgsql-performance@postgresql.org" Date: Thu, 12 Mar 2009 15:15:51 -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: AcmjUhwk+vBu7qCVRZW+Edm9TpVYGgADf4k3 Message-ID: In-Reply-To: Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DED7A733B2scottrichrelevancecom_" 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/144 X-Sequence-Number: 33066 --_000_C5DED7A733B2scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/12/09 1:35 PM, "Greg Smith" wrote: On Thu, 12 Mar 2009, Jignesh K. Shah wrote: > As soon as I get more "cycles" I will try variations of it but it would > help if others can try it out in their own environments to see if it > helps their instances. What you should do next is see whether you can remove the bottleneck your test is running into via using a connection pooler. I doubt it is running into a bottleneck due to that, the symptoms aren't ri= ght. He can change his test to have near zero delay to simulate such a con= nection pool. If it was an issue due to concurrency at that level, the results would not = have scaled linearly with user count to a plateau the way they did. There = would be a steep drop-down from thrashing as concurrency kept going up. Co= ntext switch data would help, since the thrashing ends up as a measurable t= here. No evidence of concurrency thrashing yet that I see, but more tests = and data would help. The disconnect, is that the Users column in his data does not represent bac= k-ends. It represents concurrent users on the front-end. Whether these wh= ile idle pool or not is not clear. It would be useful to rule that possibi= lity out but that looks like an improbable diagnosis to me given the lack o= f performance decrease as concurrency goes up. Furthermore, if the problem was due to too much concurrency in the database= with active connections, its hard to see how changing the lock code would = change the result the way it did - increasing CPU and throughput accordingl= y. Again, context switch rate info would help rule out many possibilities. That's what I think most informed people would do were you to ask how to setup an optimal environment using PostgreSQL that aimed to serve thousands of clients. If that makes your bottleneck go away, that's what you should be recommending to customers who want to scale in this fashion too. First just run a test with a tiny delay (5ms? 0?) and fewer users to compar= e. If your theory that a connection pooler would help, that test would pro= vide higher throughput with low user count and not be lock limited. This m= ay be easier to run than setting up a pooler, though he should investigate = one regardless. If the bottleneck moves to somewhere else, that new hot spot might be one people care more about. Given that there are multiple good pooling solutions floating around already, it's hard to justify dumping coding and testing resources here if that makes the problem move somewhere else. Its worth ruling out given that even if the likelihood is small, the fix is= easy. However, I don't see the throughput drop from peak as more concurre= ncy is added that is the hallmark of this problem - usually with a lot of c= ontext switching and a sudden increase in CPU use per transaction. The biggest disconnect in load testing almost always occurs over the defini= tion of "concurrent users". Think of an HTTP app, backed by a db - about as simple as it gets these day= s (this is fun with 5, 6 tier fanned out stuff). "Users" could mean: Number of application user logins used. Number of test harness threads or processes that are active. Number of open HTTP connections Number of HTTP requests being processed Number of connections from the app to the db Number of active connections from the app to the db Knowing which of these is the topic, and what that means in relation to all= the others, is often messy. Without knowing which one it is in a result, = you can still learn a lot. The data in the results here prove its not the = last one on the list above, nor the first one. It could still be any of th= e middle four, but is most likely #2 or the second to last one (which might= be equivalent). --_000_C5DED7A733B2scottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On 3/12/09 1:35 PM, "Greg Smith" <gsmith@gregsmith.com> wrote:

On Thu, 12 Mar 2009, Jignesh K. Shah wrote:=

> As soon as I get more "cycles" I will try variations of it b= ut it would
> help if others can try it out in their own environments to see if it > helps their instances.

What you should do next is see whether you can remove the bottleneck your test is running into via using a connection pooler.  


That's what I think
most informed people would do were you to ask how to setup an optimal
environment using PostgreSQL that aimed to serve thousands of clients.
If that makes your bottleneck go away, that's what you should be
recommending to customers who want to scale in this fashion too.  

If the
bottleneck moves to somewhere else, that new hot spot might be one people care more about.  Given that there are multiple good pooling solutions=
floating around already, it's hard to justify dumping coding and testing resources here if that makes the problem move somewhere else.