Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A63BF9FB7E5 for ; Fri, 8 Jun 2007 12:44:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 46391-05 for ; Fri, 8 Jun 2007 12:44:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.180]) by postgresql.org (Postfix) with ESMTP id 7C75C9FB90B for ; Fri, 8 Jun 2007 12:44:17 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c21so890318ika for ; Fri, 08 Jun 2007 08:44:16 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=eHrELOuOKaJQbUWttBKa+6UVVzzi1gr5f9UG0WBpQ7n0Aq6DlSIFGqZjpKixM3KxBjpZWQm1Cif8q4E4dhZNkfp/E3EtYmD3ooCLy05MRU82rMtXGjWngA2sYQIgzpXewYNfiR7OvG9yv5CBTH5VAfIBprqJKouxkn73qkAltaQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=PvOFBURieLeTjndm3hoZxESNWiP5W4zWKN9jVCXTB8Mc1CRmyMn1/ry1AsC1riQvRr3S+eyGzBsdh25dxqPCMb5mYlPM3K+neUltEQEp5WNFY/cBzJ8NG4Uctl132QVfW4aCPQ96/ksHaxQJRO3ZF5LIsUgkIP6hau6lcfnq1Ps= Received: by 10.78.184.2 with SMTP id h2mr1289767huf.1181317456079; Fri, 08 Jun 2007 08:44:16 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Fri, 8 Jun 2007 08:44:16 -0700 (PDT) Message-ID: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com> Date: Fri, 8 Jun 2007 11:44:16 -0400 From: "Chris Hoover" To: "PGSQL Performance" Subject: Please help me understand these numbers MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_58278_423218.1181317456062" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/154 X-Sequence-Number: 25187 ------=_Part_58278_423218.1181317456062 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_58278_423218.1181317456062 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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


------=_Part_58278_423218.1181317456062--