Skip site navigation (1) Skip section navigation (2)

Monitoring buffercache...

From: Kevin Kempter <kevin(at)consistentstate(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Monitoring buffercache...
Date: 2008-11-24 18:43:56
Message-ID: 200811241143.57182.kevin@kevinkempterllc.com (view raw or flat)
Thread:
Lists: pgsql-performance
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...

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-11-24 19:23:10
Subject: Re: limit clause produces wrong query plan
Previous:From: AndrusDate: 2008-11-24 17:26:22
Subject: Re: limit clause produces wrong query plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group