On 3/2/07, Ron <rjpeace@xxxxxxxxxxxxx> wrote:
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.
How would I go about doing that? Thanks, Alex
