Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B089F65035C for ; Tue, 25 Nov 2008 15:38:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27669-08 for ; Tue, 25 Nov 2008 15:38:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from smtp5.clear.net.nz (smtp5.clear.net.nz [203.97.33.68]) by mail.postgresql.org (Postfix) with ESMTP id 9D56A64FC83 for ; Tue, 25 Nov 2008 15:38:01 -0400 (AST) Received: from zmori.markir.net (121-73-180-85.dsl.telstraclear.net [121.73.180.85]) by smtp5.clear.net.nz (CLEAR Net Mail) with ESMTP id <0KAW00LH5MJAJX00@smtp5.clear.net.nz> for pgsql-performance@postgresql.org; Wed, 26 Nov 2008 08:37:59 +1300 (NZDT) Date: Wed, 26 Nov 2008 08:37:57 +1300 From: Mark Kirkwood Subject: Re: Monitoring buffercache... In-reply-to: To: Scott Marlowe Cc: Brad Nicholson , Kevin Kempter , pgsql-performance@postgresql.org Message-id: <492C5415.6090205@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <200811241143.57182.kevin@kevinkempterllc.com> <1227556326.18990.98.camel@bnicholson-desktop> User-Agent: Thunderbird 2.0.0.17 (X11/20081109) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/256 X-Sequence-Number: 31987 Scott Marlowe wrote: > On Mon, Nov 24, 2008 at 12:52 PM, Brad Nicholson > wrote: > >>> I just ran it in a loop over and over on my 8 core opteron server and >>> it ran the load factor up by almost exactly 1.0. Under our normal >>> daily load, it sits at 1.9 to 2.5, and it climbed to 2.9 under the new >>> load of running that query over and over. So, it doesn't seem to be >>> blocking or anything. >>> >> The internal docs for pg_buffercache_pages.c state: >> >> "To get a consistent picture of the buffer state, we must lock all >> partitions of the buffer map. Needless to say, this is horrible >> for concurrency. Must grab locks in increasing order to avoid >> possible deadlocks." >> > > Well, the pg hackers tend to take a parnoid view (it's a good thing > TM) on things like this. My guess is that the period of time for > which pg_buffercache takes locks on the buffer map are short enough > that it isn't a real big deal on a fast enough server. On mine, it > certainly had no real negative effects for the 5 minutes or so it was > running in a loop. None I could see, and we run hundreds of queries > per second on our system. > > Of course, for certain other types of loads it could be a much bigger > issue. But for our load, on our machine, it was virtually > unnoticeable. > > Yeah, I wouldn't worry about accessing it every 15 minutes! I put the comment there to make it clear that (like pg_locks) selecting from it *very frequently* could effect performance. Cheers Mark