From: Kevin Kempter <kevin@consistentstate.com>
To: pgsql-performance@postgresql.org
Subject: Monitoring buffercache...
Date: Mon, 24 Nov 2008 11:43:56 -0700
Message-ID: <200811241143.57182.kevin@kevinkempterllc.com> (raw)
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kevin@consistentstate.com
Subject: Re: Monitoring buffercache...
In-Reply-To: <200811241143.57182.kevin@kevinkempterllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox