Re: strange performance regression between 7.4 and 8.1

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