Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B516B632718 for ; Fri, 13 Mar 2009 14:30:12 -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 34546-05 for ; Fri, 13 Mar 2009 14:30:02 -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 0FF7C63239E for ; Fri, 13 Mar 2009 14:29:53 -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; Fri, 13 Mar 2009 10:29:52 -0700 From: Scott Carey To: "Jignesh K. Shah" , "pgsql-performance@postgresql.org" Date: Fri, 13 Mar 2009 10:29: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: Acmj+t5hYaY0hcpbSYORK8uKw4PHRAABnIoM Message-ID: In-Reply-To: <49BA8CF6.6090604@sun.com> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DFE61F3426scottrichrelevancecom_" 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/169 X-Sequence-Number: 33091 --_000_C5DFE61F3426scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/13/09 9:42 AM, "Jignesh K. Shah" wrote: Now with a modified Fix (not the original one that I proposed but something that works like a heart valve : Opens and shuts to minimum default way thus controlling how many waiters are waked up ) Is this the server with 128 thread capability or 64 threads? Idle time is = reduced but other locks are hit. With 200ms sleeps, no lock change: Peak throughput 102000/min @ 1000 users.avg response time is 23ms. Linear = ramp up until 900 users @98000/min and 12ms response time. At 2000 users, response time is 229ms and throughput is 90000/min. With 200ms sleeps, lock modification 1 (wake all) Peak throughput at 1701112/min @2000 users and avg response time 63ms. Pla= teau starts at 1600 users and 160000/min throughput. As before, plateau sta= rts when response time breaches 20ms, indicating contention. Lets call the above a 65% throughput improvement with large connection coun= t. ----------------- Now, with 0ms delay, no threading change: Throughput is 136000/min @184 users, response time 13ms. Response time has= not jumped too drastically yet, but linear performance increases stopped a= t about 130 users or so. ProcArrayLock busy, very busy. CPU: 35% user, 11%= system, 54% idle With 0ms delay, and lock modification 2 (wake some, but not all) Throughput is 161000/min @328 users, response time 28ms. At 184 users as b= efore the change, throughput is 147000/min with response time 0.12ms. Perf= ormance scales linearly to 144 users, then slows down and slightly increase= s after that with more concurrency. Throughput increase is between 15% and 25%. What I see in the above is twofold: This change improves throughput on this machine regardless of connection co= unt. The change seems to help with more connection count and the wait - in fact,= it seems to make connection count at this level not be much of a factor at= all. The two changes tested are different, which clouds things a bit. I wonder = what the first change would do in the second test case. In any event, the second detail above is facinating - it suggests that thes= e locks are what is responsible for a significant chunk of the overhead of = idle or mostly idle connections (making connection pools less useful, thoug= h they can never fix mid-transaction pauses which are very common). And in= any event, on large multiprocessor systems like this postgres is lock limi= ted regardless of using a connection pool or not. --_000_C5DFE61F3426scottrichrelevancecom_ 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/13/09 9:42 AM, "Jignesh K. Shah" <J.K.Shah@Sun.COM> wrote:


Now with a modified Fix (not the original one that I proposed but
something that works like a heart valve : Opens and shuts to minimum
default way thus  controlling how many waiters are waked up )