Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B44119FC18D for ; Fri, 8 Jun 2007 14:52:32 -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 07453-07 for ; Fri, 8 Jun 2007 14:52:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.239]) by postgresql.org (Postfix) with ESMTP id F2FC39FC0C1 for ; Fri, 8 Jun 2007 14:52:28 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 25so888949wry for ; Fri, 08 Jun 2007 10:52:28 -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=On4noplvKDViXYJDQXFYJABzTFfyRShrji2UP0Tp/b8XNl2J+mmQgF72oeF3FLLUhw+Tm83ok3u/U2zSGqf5u5nycUlpWfIXuHHo2ZpXKwQdtMxLuy1oNeGFhEfX74kfqLgDf4ZZ/JaTmGJFIjW3Iw14lhjMfG0UsXBm/4HvzFY= 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=ptf7itu0qh7VFMI8IwiiJhy1OrRL9DWzGpEp3Bmow3wDVZVCQgk07cWaOeklAHArAC5evTK9Msp9sAxurRZsff2Oetk4Tk93hypoL8+919cPEzJDS4JMornlFSMfAuy+dc5Jr/P1D7ueKoPNIl/WxzPBtL2mCUgFOE/wncEw494= Received: by 10.78.201.10 with SMTP id y10mr1365605huf.1181325146894; Fri, 08 Jun 2007 10:52:26 -0700 (PDT) Received: by 10.78.201.16 with HTTP; Fri, 8 Jun 2007 10:52:26 -0700 (PDT) Message-ID: <1d219a6f0706081052r3331b4f0n23e50e052dcbb26e@mail.gmail.com> Date: Fri, 8 Jun 2007 13:52:26 -0400 From: "Chris Hoover" To: "Bill Moran" Subject: Re: Please help me understand these numbers Cc: "PGSQL Performance" In-Reply-To: <20070608133718.c99faf3e.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_59915_28903990.1181325146867" References: <1d219a6f0706080844k278960e8oa7bf537a5b8a87d8@mail.gmail.com> <20070608120941.1749bde1.wmoran@collaborativefusion.com> <1d219a6f0706081005g4056bae7q5d4adc04eb6d2676@mail.gmail.com> <20070608133718.c99faf3e.wmoran@collaborativefusion.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/167 X-Sequence-Number: 25200 ------=_Part_59915_28903990.1181325146867 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" : > > > 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. > > Some days are better than others :) > > > 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. > > Have a look at the pg_buffercache module, which can be pretty useful for > figuring out what data is being accessed. > > > 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. > > I looked back through and couldn't find which version of PostgreSQL you > were using. If it's 8.X, the current wisdom is to start with 25 - 30% of > your unused RAM for shared buffers (by "unused", it's meant to take into > account any other applications running on the same machine and their > RAM requirements) and then tune down or up as seems to help. So, my > recommendation would be to bump shared_buffers up to around 2G and go > from there. > > Another thing that I realized wasn't in your original email is if you're > having any sort of problems? If there are slow queries or other > performance issues, do before/after tests to see if you're adjusting > values in the right direction. If you don't have any performance issues > outstanding, it can be easy to waste a lot of time/energy tweaking > settings that don't really help anything. > > -- > Bill Moran > Collaborative Fusion Inc. > http://people.collaborativefusion.com/~wmoran/ > > wmoran@collaborativefusion.com > Phone: 412-422-3463x4023 > Sorry, I am on 8.1.3 (move to 8.1.9 is being started). I do have some performance issues but they are sporadic. I am trying to make sure my servers are all running well. I believe that they are ok most of the time, but we are walking on the edge. They can easily be pushed over and have my customers complaining of slowness. So, I am trying to look at tuning back away from the edge. Thanks for your help, Chris ------=_Part_59915_28903990.1181325146867 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>:

> 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.

Some days are better than others :)

> 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.

Have a look at the pg_buffercache module, which can be pretty useful for
figuring out what data is being accessed.

> 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.

I looked back through and couldn't find which version of PostgreSQL you
were using.  If it's 8.X, the current wisdom is to start with 25 - 30% of
your unused RAM for shared buffers (by "unused", it's meant to take into
account any other applications running on the same machine and their
RAM requirements) and then tune down or up as seems to help.  So, my
recommendation would be to bump shared_buffers up to around 2G and go
from there.

Another thing that I realized wasn't in your original email is if you're
having any sort of problems?  If there are slow queries or other
performance issues, do before/after tests to see if you're adjusting
values in the right direction.  If you don't have any performance issues
outstanding, it can be easy to waste a lot of time/energy tweaking
settings that don't really help anything.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023


Sorry, I am on 8.1.3 (move to 8.1.9 is being started).  I do have some performance issues but they are sporadic.  I am trying to make sure my servers are all running well.  I believe that they are ok most of the time, but we are walking on the edge.  They can easily be pushed over and have my customers complaining of slowness.  So, I am trying to look at tuning back away from the edge.

Thanks for your help,

Chris
------=_Part_59915_28903990.1181325146867--