Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 01A4D9FBC5D for ; Fri, 8 Jun 2007 14:05:29 -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 67699-06 for ; Fri, 8 Jun 2007 14:05:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 020D59FBEF5 for ; Fri, 8 Jun 2007 14:05:23 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so1096869uge for ; Fri, 08 Jun 2007 10:05:22 -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:cc:in-reply-to:mime-version:content-type:references; b=SdXW5mBnrvr8z43j1/tW97XloO+8+AH8EwVcqNzrmuND2V+gmSgGIc9q4vz6NVryurErhNnpkbhIoYWQWzU/cgGdEKy2opBnfKIsj1Ip3T9idMGfpC6paLJBNdTRRqeR3Uu2h3WGlNB3zQmxhi91R5rMkOHGZaG9Ul57LCm+p34= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Rw77JEcvrdIRKvGJSkTP0KPQS/56t0aYLJVMMyhLE7bxSX+ser3HXG9p8Cg8jQl/qUmGyNzpDSTcAmlOrc5gWspszHrsMc2qKga+Rt/DF52wWOs/HDwJksk38jWfNm1A8DOK+fe74tfCcU9r78KKcA5FEZG5QpoZrofBqXBn+WE= Received: by 10.78.204.1 with SMTP id b1mr1327986hug.1181322322232; Fri, 08 Jun 2007 10:05:22 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Fri, 8 Jun 2007 10:05:22 -0700 (PDT) Message-ID: <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com> Date: Fri, 8 Jun 2007 13:05:22 -0400 From: "Chris Hoover" To: "Bill Moran" Subject: Re: Please help me understand these numbers Cc: "PGSQL Performance" In-Reply-To: <20070608120941.1749bde1.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_59210_21895877.1181322322202" References: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com> <20070608120941.1749bde1.wmoran@collaborativefusion.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/161 X-Sequence-Number: 25194 ------=_Part_59210_21895877.1181322322202 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/8/07, Bill Moran wrote: > > In response to "Chris Hoover" : > > > 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 > Wow, that's amazing. You pretty much hit my config on the head. 9GB ram with 256MB shared_buffers. I have just started playing with my shared_buffers config on another server that tends to be my main problem server. I just ran across these informational functions the other day, and they are opening up some great territory for me that I have been wanting to know about for a while. I was starting to bump my shared_buffers up slowly. Would it be more advisable to just push them to 25% of my ram and start there or work up slowly. I was going slowly since it takes a database restart to change the parameter. Any advise would be welcome. Chris ------=_Part_59210_21895877.1181322322202 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 6/8/07, Bill Moran <wmoran@collaborativefusion.com> wrote:
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

Wow, that's amazing.  You pretty much hit my config on the head.  9GB ram with 256MB shared_buffers.

I have just started playing with my shared_buffers config on another server that tends to be my main problem server.  I just ran across these informational functions the other day, and they are opening up some great territory for me that I have been wanting to know about for a while.

I was starting to bump my shared_buffers up slowly.  Would it be more advisable to just push them to 25% of my ram and start there or work up slowly.  I was going slowly since it takes a database restart to change the parameter.

Any advise would be welcome.

Chris
------=_Part_59210_21895877.1181322322202--