Re: Monitoring buffercache...

On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter
<kevin(at)consistentstate(dot)com> 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.