Re: [PERFORM] Proposal of tunable fix for scalability of 8.4 </TITLE= > </HEAD> <BODY> On 3/13/09 10:16 AM, "Tom Lane" <<a href=3D"tgl@sss.pgh.pa.us"= >tgl@sss.pgh.pa.us</a>> wrote: <BLOCKQUOTE>Robert Haas <<a href=3D"robertmhaas@gmai= l.com">robertmhaas@gmail.com</a>> writes: > I think that changing the locking behavior is attacking the problem at= > the wrong level anyway. Right. By the time a patch here could have any effect, you've already= lost the game --- having to deschedule and reschedule a process is a large cost compared to the typical lock hold time for most LWLocks. S= o it would be better to look at how to avoid blocking in the first place. &nb= sp; regard= s, tom lane </BLOCKQUOTE>In an earlier post in this thread I mentio= ned the three main ways to solve scalability problems with respect to locki= ng: Avoid locking (atomics, copy-on-write, etc), finer grained locks (data stru= cture partitioning, etc) and optimizing the locks themselves. I don’t know which of the above has the greatest opportunity in postg= res. My base assumption was that lock avoidance was something t= hat had been worked on significantly already, and that since lock algorithm= optimization is rediculously hardware dependant, there was probably low ha= nging fruit there. Messing with unfair locks does not have to be the solution to the problem, = but it can be a means to an end: It takes less time and lines of code to change the lock and see what the be= nefit less locking would cause, than it does to change the code to avoid th= e locks. So what we have here, is a tool — not necessarily what you want to us= e in production, but a handy tool. If you switch to unfair locks, and= things speed up, you’re lock bound and avoiding those locks will mak= e things faster. The Dtrace data is also a great tool, that is showin= g the same thing but without the ability to know how large or small the gai= n is or being sure what the next bottleneck will be. </BODY> </HTML> --_000_C5DFE7333427scottrichrelevancecom

Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D964E63343B for ; Fri, 13 Mar 2009 14:34:40 -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 35848-05-2 for ; Fri, 13 Mar 2009 14:34:31 -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 DED05632DAB for ; Fri, 13 Mar 2009 14:34:31 -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:34:30 -0700 From: Scott Carey To: Tom Lane , Robert Haas CC: Greg Smith , "Jignesh K. Shah" , Kevin Grittner , "pgsql-performance@postgresql.org" Date: Fri, 13 Mar 2009 10:34:27 -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/4Q0dtR/MiH7RRW6f0wVGDw/cwAAnDbv Message-ID: In-Reply-To: <12292.1236964604@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_C5DFE7333427scottrichrelevancecom_" 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/170 X-Sequence-Number: 33092 --_000_C5DFE7333427scottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/13/09 10:16 AM, "Tom Lane" wrote: Robert Haas writes: > I think that changing the locking behavior is attacking the problem at > the wrong level anyway. Right. By the time a patch here could have any effect, you've already lost the game --- having to deschedule and reschedule a process is a large cost compared to the typical lock hold time for most LWLocks. So it would be better to look at how to avoid blocking in the first place. regards, tom lane In an earlier post in this thread I mentioned the three main ways to solve = scalability problems with respect to locking: Avoid locking (atomics, copy-on-write, etc), finer grained locks (data stru= cture partitioning, etc) and optimizing the locks themselves. I don't know which of the above has the greatest opportunity in postgres. = My base assumption was that lock avoidance was something that had been wor= ked on significantly already, and that since lock algorithm optimization is= rediculously hardware dependant, there was probably low hanging fruit ther= e. Messing with unfair locks does not have to be the solution to the problem, = but it can be a means to an end: It takes less time and lines of code to change the lock and see what the be= nefit less locking would cause, than it does to change the code to avoid th= e locks. So what we have here, is a tool - not necessarily what you want to use in p= roduction, but a handy tool. If you switch to unfair locks, and things spe= ed up, you're lock bound and avoiding those locks will make things faster. = The Dtrace data is also a great tool, that is showing the same thing but w= ithout the ability to know how large or small the gain is or being sure wha= t the next bottleneck will be. --_000_C5DFE7333427scottrichrelevancecom_ 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> On 3/13/09 10:16 AM, "Tom Lane" <<a href=3D"tgl@sss.pgh.pa.us"= >tgl@sss.pgh.pa.us</a>> wrote: <BLOCKQUOTE>Robert Haas <<a href=3D"robertmhaas@gmai= l.com">robertmhaas@gmail.com</a>> writes: > I think that changing the locking behavior is attacking the problem at= > the wrong level anyway. Right. By the time a patch here could have any effect, you've already= lost the game --- having to deschedule and reschedule a process is a large cost compared to the typical lock hold time for most LWLocks. S= o it would be better to look at how to avoid blocking in the first place. &nb= sp; regard= s, tom lane </BLOCKQUOTE>In an earlier post in this thread I mentio= ned the three main ways to solve scalability problems with respect to locki= ng: Avoid locking (atomics, copy-on-write, etc), finer grained locks (data stru= cture partitioning, etc) and optimizing the locks themselves. I don’t know which of the above has the greatest opportunity in postg= res. My base assumption was that lock avoidance was something t= hat had been worked on significantly already, and that since lock algorithm= optimization is rediculously hardware dependant, there was probably low ha= nging fruit there. Messing with unfair locks does not have to be the solution to the problem, = but it can be a means to an end: It takes less time and lines of code to change the lock and see what the be= nefit less locking would cause, than it does to change the code to avoid th= e locks. So what we have here, is a tool — not necessarily what you want to us= e in production, but a handy tool. If you switch to unfair locks, and= things speed up, you’re lock bound and avoiding those locks will mak= e things faster. The Dtrace data is also a great tool, that is showin= g the same thing but without the ability to know how large or small the gai= n is or being sure what the next bottleneck will be. </BODY> </HTML> --_000_C5DFE7333427scottrichrelevancecom_--