Skip site navigation (1) Skip section navigation (2)

Re: Please help me understand these numbers

From: "Chris Hoover" <revoohc(at)gmail(dot)com>
To: "Bill Moran" <wmoran(at)collaborativefusion(dot)com>
Cc: "PGSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Please help me understand these numbers
Date: 2007-06-08 17:52:26
Message-ID: 1d219a6f0706081052r3331b4f0n23e50e052dcbb26e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 6/8/07, Bill Moran <wmoran(at)collaborativefusion(dot)com> wrote:
>
> In response to "Chris Hoover" <revoohc(at)gmail(dot)com>:
>
> > On 6/8/07, Bill Moran <wmoran(at)collaborativefusion(dot)com> wrote:
> > >
> > > In response to "Chris Hoover" <revoohc(at)gmail(dot)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(at)collaborativefusion(dot)com
> > > Phone: 412-422-3463x4023
> > >
> >
> > Wow, that's amazing.  You pretty much hit my config on the head.  9GB
> ram
> > with 256MB shared_buffers.
>
> Some days are better than others :)
>
> > 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.
>
> Have a look at the pg_buffercache module, which can be pretty useful for
> figuring out what data is being accessed.
>
> > 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.
>
> I looked back through and couldn't find which version of PostgreSQL you
> were using.  If it's 8.X, the current wisdom is to start with 25 - 30% of
> your unused RAM for shared buffers (by "unused", it's meant to take into
> account any other applications running on the same machine and their
> RAM requirements) and then tune down or up as seems to help.  So, my
> recommendation would be to bump shared_buffers up to around 2G and go
> from there.
>
> Another thing that I realized wasn't in your original email is if you're
> having any sort of problems?  If there are slow queries or other
> performance issues, do before/after tests to see if you're adjusting
> values in the right direction.  If you don't have any performance issues
> outstanding, it can be easy to waste a lot of time/energy tweaking
> settings that don't really help anything.
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran(at)collaborativefusion(dot)com
> Phone: 412-422-3463x4023
>


Sorry, I am on 8.1.3 (move to 8.1.9 is being started).  I do have some
performance issues but they are sporadic.  I am trying to make sure my
servers are all running well.  I believe that they are ok most of the time,
but we are walking on the edge.  They can easily be pushed over and have my
customers complaining of slowness.  So, I am trying to look at tuning back
away from the edge.

Thanks for your help,

Chris

In response to

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2007-06-08 18:10:17
Subject: Re: [OT] Re: How much ram is too much
Previous:From: Zoltan BoszormenyiDate: 2007-06-08 17:38:42
Subject: [OT] Re: How much ram is too much

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group