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

Please help me understand these numbers

From: "Chris Hoover" <revoohc(at)gmail(dot)com>
To: "PGSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Please help me understand these numbers
Date: 2007-06-08 15:44:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.



pgsql-performance by date

Next:From: Bill MoranDate: 2007-06-08 16:09:41
Subject: Re: Please help me understand these numbers
Previous:From: Andrew SullivanDate: 2007-06-08 15:10:57
Subject: Re: [ADMIN] reclaiming disk space after major updates

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