From: Brad Nicholson <bnichols@ca.afilias.info>
To: Kevin Kempter <kevin@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Monitoring buffercache...
Date: Mon, 24 Nov 2008 14:45:22 -0500
Message-ID: <1227555922.18990.94.camel@bnicholson-desktop> (raw)
In-Reply-To: <200811241143.57182.kevin@kevinkempterllc.com>
References: <200811241143.57182.kevin@kevinkempterllc.com>
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.
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: bnichols@ca.afilias.info, kevin@consistentstate.com
Subject: Re: Monitoring buffercache...
In-Reply-To: <1227555922.18990.94.camel@bnicholson-desktop>
* 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