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

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