Re: Please help me understand these numbers

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