From: Brad Nicholson <bnichols@ca.afilias.info>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Kevin Kempter <kevin@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Monitoring buffercache...
Date: Mon, 24 Nov 2008 14:52:06 -0500
Message-ID: <1227556326.18990.98.camel@bnicholson-desktop> (raw)
In-Reply-To: <dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com>
References: <200811241143.57182.kevin@kevinkempterllc.com>
<dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com>
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 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.
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.
--
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, scott.marlowe@gmail.com, kevin@consistentstate.com
Subject: Re: Monitoring buffercache...
In-Reply-To: <1227556326.18990.98.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