At 11:03 AM 3/2/2007, Alex Deucher wrote:
On 3/2/07, Ron <rjpeace@xxxxxxxxxxxxx> 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
