Re: Monitoring buffercache...

On Mon, 2008-11-24 at 11:43 -0700, Kevin Kempter 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.

I wouldn't routinely run pg_buffercache on a busy database. Plus, I
don't think that pg_buffercache will answer this question for you. It
will tell you whats currently in the buffer pool and the clean/dirty
status, but that's not the first place I'd look, but what you really
need is to figure out the hit ratio on the buffer pool and go from
there.

> Does anyone have any suggestions per a better approach or maybe a way to
> improve the performance for the above query ?

You should be able to use the blocks hit vs block read data in the
pg_stat_database view (for the overall database), and drill down into
pg_statio_user_tables/pg_statio_all_tables to get more detailed data if
you want.

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.