Re: Please help me understand these numbers - Mailing list pgsql-performance

In response to "Chris Hoover" <revoohc@gmail.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.

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@collaborativefusion.com
Phone: 412-422-3463x4023