pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Hoover <revoohc@gmail.com>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Please help me understand these numbers
Date: Fri, 8 Jun 2007 13:05:22 -0400
Message-ID: <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com> (raw)
In-Reply-To: <20070608120941.1749bde1.wmoran@collaborativefusion.com>
References: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com>
	<20070608120941.1749bde1.wmoran@collaborativefusion.com>

On 6/8/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
>
> In response to "Chris Hoover" <revoohc@gmail.com>:
>
> > I need some help.  I have started taking snapshots of performance of my
> > databases with concerns to io.  I created a view on each cluster defined
> as:
> >  SELECT pg_database.datname AS database_name,
> > pg_stat_get_db_blocks_fetched(pg_database.oid) AS blocks_fetched,
> > pg_stat_get_db_blocks_hit(pg_database.oid) AS blocks_hit,
> > pg_stat_get_db_blocks_fetched(pg_database.oid) -
> > pg_stat_get_db_blocks_hit(pg_database.oid) AS physical_reads
> >    FROM pg_database
> >   WHERE pg_stat_get_db_blocks_fetched(pg_database.oid) > 0
> >   ORDER BY pg_stat_get_db_blocks_fetched(pg_database.oid) -
> > pg_stat_get_db_blocks_hit(pg_database.oid) DESC;
> >
> > I am taking 5 minute snapshots of this view.
> >
> > When I look at my data, I am getting row like this:
> > database_name: xxx
> > blocks_fetched: 2396915583
> > blocks_hit: 1733190669
> > physical_reads: 663724914
> > snapshot_timestamp: 2007-06-08 09:20:01.396079
> >
> > database_name: xxx
> > blocks_fetched: 2409671770
> > blocks_hit: 1733627788
> > physical_reads: 676043982
> > snapshot_timestamp: 2007-06-08 09:25:01.512911
> >
> > Subtracting these 2 lines gives me a 5 minute number of
> > blocks_fetched: 12756187
> > blocks_hit: 437119
> > physical_reads: 12319068
> >
> > If I am interpreting these number correctly, for this 5 minute interval
> I
> > ended up hitting only 3.43% of the requested data in my shared_buffer,
> and
> > ended up requesting 12,319,068 blocks from the os?  Since a postgres
> block
> > is 8KB, that's 98,553,544 KB (~94GB)!
> >
> > Are my assumptions correct in this?
>
> It certainly seems possible.
>
> > I am just having a hard time fathoming
> > this.  For this particular db, that is almost 1/2 of the total database
> (it
> > is a 200GB+ db) requested in just 5 minutes!
>
> What are your share_buffers setting and the total RAM available to the OS?
>
> My guess would be that you have plenty of RAM in the system (8G+ ?) but
> that
> you haven't allocated very much of it to shared_buffers (only a few 100
> meg?).
> As a result, PostgreSQL is constantly asking the OS for disk blocks that
> it
> doesn't have cached, but the OS has those disk blocks cached in RAM.
>
> If my guess is right, you'll probably see improved performance by
> allocating
> more shared memory to PostgreSQL, thus avoiding having to move data from
> one area in memory to another before it can be used.
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>

Wow, that's amazing.  You pretty much hit my config on the head.  9GB ram
with 256MB shared_buffers.

I have just started playing with my shared_buffers config on another server
that tends to be my main problem server.  I just ran across these
informational functions the other day, and they are opening up some great
territory for me that I have been wanting to know about for a while.

I was starting to bump my shared_buffers up slowly.  Would it be more
advisable to just push them to 25% of my ram and start there or work up
slowly.  I was going slowly since it takes a database restart to change the
parameter.

Any advise would be welcome.

Chris

view thread (5+ messages)  latest in thread

Message-ID: <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com>
Permalink:  ../1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com/
Also on:    postgresql.org/message-id/1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@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: revoohc@gmail.com, wmoran@collaborativefusion.com
  Subject: Re: Please help me understand these numbers
  In-Reply-To: <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com>
  References: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com> <20070608120941.1749bde1.wmoran@collaborativefusion.com> <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@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