From: Chris Hoover <revoohc@gmail.com>
To: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Please help me understand these numbers
Date: Fri, 8 Jun 2007 11:44:16 -0400
Message-ID: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: revoohc@gmail.com
Subject: Re: Please help me understand these numbers
In-Reply-To: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox