Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 89F236348C6 for ; Thu, 12 Mar 2009 14:48:26 -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 88982-05 for ; Thu, 12 Mar 2009 14:48:23 -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 B1A436348D5 for ; Thu, 12 Mar 2009 14:48:15 -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; Thu, 12 Mar 2009 10:48:14 -0700 From: Scott Carey To: Tom Lane CC: Kevin Grittner , "pgsql-performance@postgresql.org" , "Jignesh K. Shah" Date: Thu, 12 Mar 2009 10:48:12 -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: AcmivPBm57qJa3KhQxSwKZPodInmHAAfcX5w Message-ID: In-Reply-To: <29836.1236826066@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_C5DE98EC3381scottrichrelevancecom_" 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/130 X-Sequence-Number: 33052 --_000_C5DE98EC3381scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/11/09 7:47 PM, "Tom Lane" wrote: Scott Carey writes: > If there is enough lock contention and a common lock case is a short live= d shared lock, it makes perfect sense sense. Fewer readers are blocked wai= ting on writers at any given time. Readers can 'cut' in line ahead of writ= ers within a certain scope (only up to the number waiting at the time a sha= red lock is at the head of the queue). Essentially this clumps up shared a= nd exclusive locks into larger streaks, and allows for higher shared lock t= hroughput. > Exclusive locks may be delayed, but will NOT be starved, since on the nex= t iteration, a streak of exclusive locks will occur first in the list and t= hey will all process before any more shared locks can go. That's a lot of sunny assertions without any shred of evidence behind them... The current LWLock behavior was arrived at over multiple iterations and is not lightly to be toyed with IMHO. Especially not on the basis of one benchmark that does not reflect mainstream environments. Note that I'm not saying "no". I'm saying that I want a lot more evidence *before* we go to the trouble of making this configurable and asking users to test it. regards, tom lane All I'm adding, is that it makes some sense to me based on my experience in= CPU / RAM bound scalability tuning. It was expressed that the test itself= didn't even make sense. I was wrong in my understanding of what the change did. If it wakes ALL wa= iters up there is an indeterminate amount of time a lock will wait. However, if instead of waking up all of them, if it only wakes up the share= d readers and leaves all the exclusive ones at the front of the queue, ther= e is no possibility of starvation since those exclusives will be at the fro= nt of the line after the wake-up batch. As for this being a use case that is important: * SSDs will drive the % of use cases that are not I/O bound up significant= ly over the next couple years. All postgres installations with less than a= bout 100GB of data TODAY could avoid being I/O bound with current SSD techn= ology, and those less than 2TB can do so as well but at high expense or wit= h less proven technology like the ZFS L2ARC flash cache. * Intel will have a mainstream CPU that handles 12 threads (6 cores, 2 thr= eads each) at the end of this year. Mainstream two CPU systems will have a= ccess to 24 threads and be common in 2010. Higher end 4CPU boxes will have= access to 48 CPU threads. Hardware thread count is only going up. This i= s the future. --_000_C5DE98EC3381scottrichrelevancecom_ 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/11/09 7:47 PM, "Tom Lane" <<a href=3D"tgl@sss.pgh.p= a.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> > If there is enough lock contention and a common lock case is a short l= ived shared lock, it makes perfect sense sense.  Fewer readers are blo= cked 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 t= he 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 hi= gher shared lock throughput.<BR> > Exclusive locks may be delayed, but will NOT be starved, since on the = next iteration, a streak of exclusive locks will occur first in the list an= d they will all process before any more shared locks can go.<BR> <BR> That's a lot of sunny assertions without any shred of evidence behind<BR> them...<BR> <BR> The current LWLock behavior was arrived at over multiple iterations and<BR> is not lightly to be toyed with IMHO.  Especially not on the basis of<= BR> one benchmark that does not reflect mainstream environments.<BR> <BR> Note that I'm not saying "no".  I'm saying that I want a lot= more<BR> evidence *before* we go to the trouble of making this configurable<BR> and asking users to test it.<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'><BR> All I’m adding, is that it makes some sense to me based on my experie= nce in CPU / RAM bound scalability tuning.  It was expressed that the = test itself didn’t even make sense.<BR> <BR> I was wrong in my understanding of what the change did.  If it wakes A= LL waiters up there is an indeterminate amount of time a lock will wait.<BR= > However, if instead of waking up all of them, if it only wakes up the share= d readers and leaves all the exclusive ones at the front of the queue, ther= e is no possibility of starvation since those exclusives will be at the fro= nt of the line after the wake-up batch.<BR> <BR> As for this being a use case that is important:<BR> <BR> *  SSDs will drive the % of use cases that are not I/O bound up signif= icantly over the next couple years.  All postgres installations with l= ess than about 100GB of data TODAY could avoid being I/O bound with current= SSD technology, and those less than 2TB can do so as well but at high expe= nse or with less proven technology like the ZFS L2ARC flash cache.<BR> *  Intel will have a mainstream CPU that handles 12 threads (6 cores, = 2 threads each) at the end of this year.  Mainstream two CPU systems w= ill have access to 24 threads and be common in 2010.  Higher end 4CPU = boxes will have access to 48 CPU threads.  Hardware thread count is on= ly going up.  This is the future.<BR> <BR> </SPAN></FONT> </BODY> </HTML> --_000_C5DE98EC3381scottrichrelevancecom_--