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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2008-11-24 19:23:10 Re: limit clause produces wrong query plan
Previous Message Andrus 2008-11-24 17:26:22 Re: limit clause produces wrong query plan