Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 93EAF64FD87 for ; Mon, 24 Nov 2008 15:46:35 -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 09433-10 for ; Mon, 24 Nov 2008 15:46:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f22.google.com (mail-gx0-f22.google.com [209.85.217.22]) by mail.postgresql.org (Postfix) with ESMTP id 2036964FD0D for ; Mon, 24 Nov 2008 15:46:31 -0400 (AST) Received: by gxk3 with SMTP id 3so1238327gxk.19 for ; Mon, 24 Nov 2008 11:46:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=u0+F5haKxnbgLBX24mFzfTO9TcbjEsPDga0ucxZxxQ8=; b=X0vmVU4TrTaxMC63He3tsu8eUGCCXvb3hAgtGoTIdodKrgFvJOhbUVssjh7kJDpQKI L3vKr+Ti9YNJOs1WDPm1OfiyoYNpxGeVKOb3Lt/oArcEWvvn3iPoJyFT06EmXCCEgFB6 m7qb3PdsL5m4cdvsiS7mdd7iEEu4UBetDnLBo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=Q4nDlhqgYZmotO7vFtswyUCEj/XNoGTgX5lMRg9+7GDtK6x5+DK/8Ort2BNCXcyVWr /30dH2t33/htGTsPsAS5D5N9ICiKqqT11x8VobKtmINTSS6FX252nlNd0ZDUN7W7S7/1 A31Qo7GS97FGoQVtTOOxu70JHBLOY0fucwh9s= Received: by 10.115.59.4 with SMTP id m4mr2093367wak.37.1227555990440; Mon, 24 Nov 2008 11:46:30 -0800 (PST) Received: by 10.114.137.17 with HTTP; Mon, 24 Nov 2008 11:46:30 -0800 (PST) Message-ID: Date: Mon, 24 Nov 2008 12:46:30 -0700 From: "Scott Marlowe" To: "Kevin Kempter" Subject: Re: Monitoring buffercache... Cc: pgsql-performance@postgresql.org In-Reply-To: <200811241143.57182.kevin@kevinkempterllc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <200811241143.57182.kevin@kevinkempterllc.com> 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/240 X-Sequence-Number: 31971 On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter wrote: > Hi All; > > I've installed pg_buffercache and I want to use it to help define the optimal > shared_buffers size. > > Currently I run this each 15min via cron: > insert into buffercache_stats select now(), isdirty, count(*) as buffers, > (count(*) * 8192) as memory from pg_buffercache group by 1,2; > > and here's it's explain plan > explain insert into buffercache_stats select now(), isdirty, count(*) as > buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2; > QUERY PLAN > ------------------------------------------------------------------------------------------- > Subquery Scan "*SELECT*" (cost=65.00..65.23 rows=2 width=25) > -> HashAggregate (cost=65.00..65.12 rows=2 width=1) > -> Function Scan on pg_buffercache_pages p (cost=0.00..55.00 > rows=1000 width=1) > (3 rows) > > > Then once a day I will pull a report from the buffercache_stats table. The > buffercache_stats table is our own creation : > > \d buffercache_stats > Table "public.buffercache_stats" > Column | Type | Modifiers > ----------------+-----------------------------+----------- > snap_timestamp | timestamp without time zone | > isdirty | boolean | > buffers | integer | > memory | integer | > > > Here's my issue, the server that we'll eventually roll this out to is > extremely busy and the every 15min query above has the potential to have a > huge impact on performance. > > Does anyone have any suggestions per a better approach or maybe a way to > improve the performance for the above query ? I wouldn't worry about running it every 15 minutes unless it's on a REALLY slow machine. 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.