pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Kevin Kempter <kevin@consistentstate.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Monitoring buffercache...
Date: Mon, 24 Nov 2008 12:46:30 -0700
Message-ID: <dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com> (raw)
In-Reply-To: <200811241143.57182.kevin@kevinkempterllc.com>
References: <200811241143.57182.kevin@kevinkempterllc.com>

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.



view thread (9+ messages)  latest in thread

Message-ID: <dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com>
Permalink:  ../dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com/
Also on:    postgresql.org/message-id/dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: scott.marlowe@gmail.com, kevin@consistentstate.com
  Subject: Re: Monitoring buffercache...
  In-Reply-To: <dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com>
  References: <200811241143.57182.kevin@kevinkempterllc.com> <dcc563d10811241146l95302b1wd0e88756883e4401@mail.gmail.com>

* 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