Обсуждение: Please help me understand these numbers

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?  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!

Thanks for any clarification on this.

Chris

12756187

12756187

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

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

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