Subject: Re: Monitoring buffercache...
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Kevin Kempter <kevin@consistentstate.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <200811241143.57182.kevin@kevinkempterllc.com>
References: <200811241143.57182.kevin@kevinkempterllc.com>
Content-Type: text/plain; charset=utf-8
Date: Mon, 24 Nov 2008 14:45:22 -0500
Message-Id: <1227555922.18990.94.camel@bnicholson-desktop>
Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable

On Mon, 2008-11-24 at 11:43 -0700, Kevin Kempter wrote:
> Hi All;
>=20
> I've installed pg_buffercache and I want to use it to help define the opt=
imal=20
> shared_buffers size.=20
>=20
> Currently I run this each 15min via cron:
> insert into buffercache_stats select now(), isdirty, count(*) as buffers,=
=20
> (count(*) * 8192) as memory from pg_buffercache group by 1,2;
>=20
> and here's it's explain plan
> explain insert into buffercache_stats select now(), isdirty, count(*) as=20
> buffers, (count(*) * 8192) as memory from pg_buffercache group by 1,2;
>                                         QUERY PLAN
> -------------------------------------------------------------------------=
------------------
>  Subquery Scan "*SELECT*"  (cost=3D65.00..65.23 rows=3D2 width=3D25)
>    ->  HashAggregate  (cost=3D65.00..65.12 rows=3D2 width=3D1)
>          ->  Function Scan on pg_buffercache_pages p  (cost=3D0.00..55.00=
=20
> rows=3D1000 width=3D1)
> (3 rows)
>=20
>=20
> Then once a day I will pull a report from the buffercache_stats table. Th=
e=20
> buffercache_stats table is our own creation :
>=20
> \d buffercache_stats
>              Table "public.buffercache_stats"
>      Column     |            Type             | Modifiers
> ----------------+-----------------------------+-----------
>  snap_timestamp | timestamp without time zone |
>  isdirty                    | boolean                     |
>  buffers                  | integer                       |
>  memory                | integer                       |
>=20
>=20
> Here's my issue, the server that we'll eventually roll this out to is=20
> extremely busy and the every 15min query above has the potential to have =
a=20
> 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=20
> 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.

--=20
=EF=BB=BFBrad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.