Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 8028D633AE8 for ; Thu, 12 Mar 2009 14:39:23 -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 87028-04 for ; Thu, 12 Mar 2009 14:39:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id B08526323D3 for ; Thu, 12 Mar 2009 14:39:09 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Thu, 12 Mar 2009 10:39:07 -0700 From: Scott Carey To: Kevin Grittner , "pgsql-performance@postgresql.org" , "Jignesh K. Shah" Date: Thu, 12 Mar 2009 10:39:05 -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: AcmjJSXrBpujDftYTWaI1v7/1iL65gAFEpsO Message-ID: In-Reply-To: <49B8E044.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_C5DE96C9337Escottrichrelevancecom_" 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/128 X-Sequence-Number: 33050 --_000_C5DE96C9337Escottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/12/09 8:13 AM, "Kevin Grittner" wrote: >>> Scott Carey wrote: > "Kevin Grittner" wrote: > >> I'm a lot more interested in what's happening between 60 and 180 >> than over 1000, personally. If there was a RAID involved, I'd put >> it down to better use of the numerous spindles, but when it's all >> in RAM it makes no sense. > > If there is enough lock contention and a common lock case is a short > lived shared lock, it makes perfect sense sense. Fewer readers are > blocked waiting on writers at any given time. Readers can 'cut' in > line ahead of writers within a certain scope (only up to the number > waiting at the time a shared lock is at the head of the queue). > Essentially this clumps up shared and exclusive locks into larger > streaks, and allows for higher shared lock throughput. You misunderstood me. I wasn't addressing the affects of his change, but rather the fact that his test shows a linear improvement in TPS up to 1000 connections for a 64 thread machine which is dealing entirely with RAM -- no disk access. Where's the bottleneck that allows this to happen? Without understanding that, his results are meaningless. -Kevin They are not meaningless. It is certainly more to understand, but the test= is entirely valid without that. In a CPU bound / RAM bound case, as concu= rrency increases you look for the throughput trend, the %CPU use trend and = the context switch rate trend. More information would be useful but the te= st is validated by the evidence that it is held up by lock contention. The reasons for not scaling with user count at lower numbers are numerous: = network, client limitations, or 'lock locality' (if test user blocks acces= s data in an organized pattern rather than random distribution neighbor cli= ents are more likely to block than non-neighbor ones). Furthermore, the MOST valid types of tests don't drive each user in an ASAP= fashion, but with some pacing to emulate the real world. In this case you= expect the user count to significantly be greater than CPU core count befo= re saturation. We need more info about the relationship between "users" an= d active postgres backends. If each user sleeps for 100 ms between queries= (or processes results and writes HTML for 100ms) your assumption that it s= hould take about users to saturate the CPUs is flawed. Either way, the result here demonstrates something powerful with respect to= CPU scalability and just because 300 clients isn't where it peaks does not= mean its invalid, it merely means we don't have enough information to unde= rstand the test. The fact is very simple: Increasing concurrency does not saturate all the= CPUs due to lock contention. That can be shown by the results demonstrate= d without more information. User count is irrelevant - performance is increasing linearly with user cou= nt for quite a while and then peaks and slightly dips. This is the typical= curve for all tests with a measured pacing per client. We want to know more though. More data would help (active postgres backend= s, %CPU, context switch rate would be my top 3 extra columns in the data se= t). From there all that we want to know is what the locks are and if that c= ontention is artificial. What tools are available to show what locks are m= ost contended with Postgres? Once the locks are known, we want to know if = the locking can be tuned away by one of three general types of strategies: = Less locking via smart use of atomics or copy on write (non-blocking strat= egies, probably fully investigated already); finer grained locks (most defi= nitely investigated); improved performance of locks (looked into for sure, = but is highly hardware dependant). --_000_C5DE96C9337Escottrichrelevancecom_ 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 8:13 AM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

>>> Scott Carey <scott@richrelevance.com> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
>
>> I'm a lot more interested in what's happening between 60 and 180 >> than over 1000, personally.  If there was a RAID involved, I'= d put
>> it down to better use of the numerous spindles, but when it's all<= BR> >> in RAM it makes no sense.
>
> If there is enough lock contention and a common lock case is a short > lived shared lock, it makes perfect sense sense.  Fewer readers a= re
> blocked waiting on writers at any given time.  Readers can 'cut' = in
> line ahead of writers within a certain scope (only up to the number > waiting at the time a shared lock is at the head of the queue).
> Essentially this clumps up shared and exclusive locks into larger
> streaks, and allows for higher shared lock throughput.

You misunderstood me.  I wasn't addressing the affects of his change,<= BR> but rather the fact that his test shows a linear improvement in TPS up
to 1000 connections for a 64 thread machine which is dealing entirely
with RAM -- no disk access.  Where's the bottleneck that allows this to happen?  Without understanding that, his results are meaningless.
-Kevin