Re: strange performance regression between 7.4 and 8.1

[Ron <rjpeace(at)earthlink(dot)net>]

At 11:03 AM 3/2/2007, Alex Deucher wrote:
> On 3/2/07, Ron <rjpeace(at)earthlink(dot)net> 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