Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 478A5632C8D for ; Thu, 12 Mar 2009 18:46:16 -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 49281-06 for ; Thu, 12 Mar 2009 18:46:10 -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 27F7E632C87 for ; Thu, 12 Mar 2009 18:46:01 -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 14:45:59 -0700 From: Scott Carey To: Tom Lane CC: Kevin Grittner , "pgsql-performance@postgresql.org" , "Jignesh K. Shah" Date: Thu, 12 Mar 2009 14:45:54 -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: AcmjQEtsuB+iVVFSRdaem2bV0bmPQAAG5/H3 Message-ID: In-Reply-To: <12916.1236882481@sss.pgh.pa.us> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DED0A233ABscottrichrelevancecom_" 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/143 X-Sequence-Number: 33065 --_000_C5DED0A233ABscottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/12/09 11:28 AM, "Tom Lane" wrote: Scott Carey writes: > They are not meaningless. It is certainly more to understand, but the te= st is entirely valid without that. In a CPU bound / RAM bound case, as con= currency increases you look for the throughput trend, the %CPU use trend an= d the context switch rate trend. More information would be useful but the = test is validated by the evidence that it is held up by lock contention. Er ... *what* evidence? There might be evidence somewhere that proves that, but Jignesh hasn't shown it. The available data suggests that the first-order performance limiter in this test is something else. Otherwise it should be possible to max out the performance with a lot less than 1000 active backends. regards, tom lane Evidence: Ramp up the concurrency, measure throughput. Throughput peaks at X with lo= w CPU utilization, linear ramp up until then. Change lock code. Throughp= ut scales past that point to much higher CPU load. That's evidence. Please explain a scenario that proves otherwise. Your la= st statement above is true but not applicable here. The test is not 1000 b= ackends, it lists 1000 users. There is a key difference between users and backends. In fact, the evidenc= e is that the result can't be backends (the column is labeled users). If i= ts not I/O bound it must cap out at roughly the number of active backends n= ear the number of CPU or less, and as noted it does not. This isn't proof= that there is something wrong with the test, its proof that the 1000 numbe= r cannot be active backends. I spent a decade solving and tuning CPU scalability problems in CPU/memory = bound systems. Sophisticated tests peak at a user count >> CPU count, beca= use real users don't execute as fast as possible. Through a chain of serve= rs several layers deep, each tier can have different levels of concurrent a= ctivity. Its useful to measure concurrency at each tier, but almost imposs= ible in postgres (easy in oracle / mssql). Most systems have a limited thr= ead pool but can queue much more than that number. Postgres and many datab= ases don't do that so clients must via connection pools. But the result be= havior of too much concurrency is thrashing and inefficiency - this shows u= p in a test that ramps up concurrency by peak throughput followed by a stee= p drop off in throughput as concurrency goes into the thrashing state. At = this thrashing time a lot of context switching and sometimes RAM pressure i= s a typical symptom. The only way to construct a test that shows the current described behavior = (linear ramp up, then plateau) is to have lock contention, I/O bottlenecks= , or CPU saturation. The number of users is irrelevant, the trend is the s= ame regardless of the relationship between user count and active backend co= unt (0 delay or 1 second delay, same result different X axis). If it was a= n I/O or client bottleneck, changing the lock code wouldn't have made it fa= ster. The evidence is 100% certain that the first test result is limited by locks= , and that changing them increased throughput. --_000_C5DED0A233ABscottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Proposal of tunable fix for scalability of 8.4 </TITLE= > </HEAD> <BODY> <FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:= 11pt'>On 3/12/09 11:28 AM, "Tom Lane" <<a href=3D"tgl@sss.pgh.= pa.us">tgl@sss.pgh.pa.us</a>> wrote:<BR> <BR> </SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Calibri, Verdana, Helvetica, Arial"= ><SPAN STYLE=3D'font-size:11pt'>Scott Carey <<a href=3D"scott@richreleva= nce.com">scott@richrelevance.com</a>> writes:<BR> > They are not meaningless.  It is certainly more to understand, bu= t the test is entirely valid without that.  In a CPU bound / RAM bound= case, as concurrency increases you look for the throughput trend, the %CPU= use trend and the context switch rate trend.  More information would = be useful but the test is validated by the evidence that it is held up by l= ock contention.<BR> <BR> Er ... *what* evidence?  There might be evidence somewhere that proves= <BR> that, but Jignesh hasn't shown it.  The available data suggests that t= he<BR> first-order performance limiter in this test is something else.<BR> Otherwise it should be possible to max out the performance with a lot<BR> less than 1000 active backends.<BR> <BR>             &nb= sp;           regard= s, tom lane<BR> <BR> </SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Calibri, Verdana, Helvetica, Arial= "><SPAN STYLE=3D'font-size:11pt'>Evidence:<BR> <BR> Ramp up the concurrency, measure throughput.  Throughput peaks at X wi= th low CPU utilization, linear ramp up until then.   Change lock = code.  Throughput scales past that point to much higher CPU load.<BR> That’s evidence.  Please explain a scenario that proves otherwis= e.  Your last statement above is true but not applicable here.  T= he test is not 1000 backends, it lists 1000 users.<BR> <BR> There is a key difference between users and backends.  In fact, the ev= idence is that the result can’t be backends (the column is labeled us= ers).  If its not I/O bound it must cap out at roughly the number of a= ctive backends near the number of CPU or less,  and as noted it does n= ot.  This isn’t proof that there is something wrong with the tes= t, its proof that the 1000 number cannot be active backends.<BR>  <BR> I spent a decade solving and tuning CPU scalability problems in CPU/memory = bound systems.  Sophisticated tests peak at a user count >> CPU = count, because real users don’t execute as fast as possible.  Th= rough a chain of servers several layers deep, each tier can have different = levels of concurrent activity.  Its useful to measure concurrency at e= ach tier, but almost impossible in postgres (easy in oracle / mssql).  = ;Most systems have a limited thread pool but can queue much more than that = number.  Postgres and many databases don’t do that so clients mu= st via connection pools.  But the result behavior of too much concurre= ncy is thrashing and inefficiency — this shows up in a test that ramp= s up concurrency by peak throughput followed by a steep drop off in through= put as concurrency goes into the thrashing state.  At this thrashing t= ime a lot of context switching and sometimes RAM pressure is a typical symp= tom.<BR> <BR> The only way to construct a test that shows the current described behavior = (linear ramp up, then plateau) is to  have lock contention, I/O bottle= necks, or CPU saturation.  The number of users is irrelevant, the tren= d is the same regardless of the relationship between user count and active = backend count (0 delay or 1 second delay, same result different X axis). &n= bsp;If it was an I/O or client bottleneck, changing the lock code wouldn= 217;t have made it faster.  <BR> <BR> The evidence is 100% certain that the first test result is limited by locks= , and that changing them increased throughput. <BR> </SPAN></FONT> </BODY> </HTML> --_000_C5DED0A233ABscottrichrelevancecom_--