Re: strange performance regression between 7.4 and 8.1

From: Ron
Subject: Re: strange performance regression between 7.4 and 8.1
Date: ,
Msg-id: E1HNC2e-0005Cl-II@elasmtp-scoter.atl.sa.earthlink.net
(view: Whole thread, Raw)
In response to: strange performance regression between 7.4 and 8.1  ("Alex Deucher")
Responses: Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher")
List: pgsql-performance

Tree view

strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
  Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
   Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
   Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
     Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
      Re: strange performance regression between 7.4 and 8.1  (Tom Lane, )
       Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
    Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
     Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
      Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
       Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
        Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
         Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
          Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
           Re: strange performance regression between 7.4 and 8.1  ("Joshua D. Drake", )
            Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
         Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
          Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
 Re: strange performance regression between 7.4 and 8.1  (Florian Weimer, )
  Re: strange performance regression between 7.4 and 8.1  (Carlos Moreno, )
   Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
  Re: strange performance regression between 7.4 and 8.1  (Alvaro Herrera, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Scott Marlowe, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
 Re: strange performance regression between 7.4 and 8.1  (Anton Rommerskirchen, )
 Re: strange performance regression between 7.4 and 8.1  (Ron, )
  Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )
   Re: strange performance regression between 7.4 and 8.1  (Guido Neitzer, )
    Re: strange performance regression between 7.4 and 8.1  (Jeff Frost, )
     Re: strange performance regression between 7.4 and 8.1  ("Alex Deucher", )

At 11:03 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Ron <> wrote:
>
>>May I suggest that it is possible that your schema, queries, etc were
>>all optimized for pg 7.x running on the old HW?
>>(explain analyze shows the old system taking ~1/10 the time per row
>>as well as estimating the number of rows more accurately)
>>
>>RAM is =cheap=.  Much cheaper than the cost of a detective hunt
>>followed by rework to queries, schema, etc.
>>Fitting the entire DB into RAM is guaranteed to help unless this is
>>an OLTP like application where HD IO is  required to be synchronous..
>>If you can fit the entire DB comfortably into RAM, do it and buy
>>yourself the time to figure out the rest of the story w/o impacting
>>on production performance.
>
>Perhaps so.  I just don't want to spend $1000 on ram and have it only
>marginally improve performance if at all.  The old DB works, so we can
>keep using that until we sort this out.
>
>Alex
1=  $1000 worth of RAM is very likely less than the $ worth of, say,
10 hours of your time to your company.  Perhaps much less.
(Your =worth=, not your pay or even your fully loaded cost.  This
number tends to be >= 4x what you are paid unless the organization
you are working for is in imminent financial danger.)
You've already put more considerably more than 10 hours of your time
into this...

2= If the DB goes from not fitting completely into RAM to being
completely RAM resident, you are almost 100% guaranteed a big
performance boost.
The exception is an OLTP like app where DB writes can't be done
a-synchronously (doing financial transactions, real time control systems, etc).
Data mines should never have this issue.

3= Whether adding enough RAM to make the DB RAM resident (and
re-configuring conf, etc, appropriately) solves the problem or not,
you will have gotten a serious lead as to what's wrong.

...and I still think looking closely at the actual physical layout of
the tables in the SAN is likely to be worth it.

Cheers,
Ron Peacetree




pgsql-performance by date:

From: "Alex Deucher"
Date:
Subject: Re: strange performance regression between 7.4 and 8.1
From: Josh Berkus
Date:
Subject: Re: Performance Query