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

On Mon, 2008-11-24 at 12:46 -0700, Scott Marlowe wrote:
> On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter
> <kevin@consistentstate.com> wrote:
> > Hi All;
> >
> > I've installed pg_buffercache and I want to use it to help define the o=
ptimal
> > shared_buffers size.
> >
> > Currently I run this each 15min via cron:
> > insert into buffercache_stats select now(), isdirty, count(*) as buffer=
s,
> > (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(*) a=
s
> > 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.0=
0
> > rows=3D1000 width=3D1)
> > (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 hav=
e a
> > huge impact on performance.
> >
> > Does anyone have any suggestions per a better approach or maybe a way t=
o
> > improve the performance for the above query ?
>=20
> I wouldn't worry about running it every 15 minutes unless it's on a
> REALLY slow machine.
>=20
> 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.

The internal docs for pg_buffercache_pages.c state:

"To get a consistent picture of the buffer state, we must lock all
partitions of the buffer map.  Needless to say, this is horrible
for concurrency.  Must grab locks in increasing order to avoid
possible deadlocks."

I'd be concerned about that running routinely.

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