Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AC03764FC44 for ; Mon, 24 Nov 2008 14:49:56 -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 92626-01 for ; Mon, 24 Nov 2008 14:49:53 -0400 (AST) X-Greylist: delayed 00:05:52.503553 by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.238]) by mail.postgresql.org (Postfix) with ESMTP id 54E5565034F for ; Mon, 24 Nov 2008 14:49:53 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so2129662rvf.43 for ; Mon, 24 Nov 2008 10:49:52 -0800 (PST) Received: by 10.141.28.4 with SMTP id f4mr2012172rvj.37.1227552239191; Mon, 24 Nov 2008 10:43:59 -0800 (PST) Received: from fedora9-32 ([67.106.45.254]) by mx.google.com with ESMTPS id l31sm13051182rvb.2.2008.11.24.10.43.58 (version=SSLv3 cipher=RC4-MD5); Mon, 24 Nov 2008 10:43:58 -0800 (PST) Organization: Kevin Kempter, LLC To: pgsql-performance@postgresql.org Subject: Monitoring buffercache... Date: Mon, 24 Nov 2008 11:43:56 -0700 User-Agent: KMail/1.9.10 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200811241143.57182.kevin@kevinkempterllc.com> From: Kevin Kempter 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/236 X-Sequence-Number: 31967 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 ? Thanks in advance...