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