On 3/2/07, Ron <rjpeace@xxxxxxxxxxxxx> wrote:
At 10:16 AM 3/2/2007, Alex Deucher wrote: >On 3/2/07, Florian Weimer <fweimer@xxxxxx> wrote: >>* Alex Deucher: >> >> > I have noticed a strange performance regression and I'm at a loss as >> > to what's happening. We have a fairly large database (~16 GB). >> >>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of >>16 *GB*? >> >>If it's really 16 GB, you should check if it's cheaper to buy more RAM >>than to fiddle with the existing infrastructure. > >Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even >sure that will help. The new system should be faster, or at least as >fast, so I'd like to sort out what's going on before I buy more ram. > OK. You a= went from pg 7.4.x to 8.1.4 AND
yes.
b= you changed from 4 SPARC CPUs (how many cores? If this is > 4...) to 2 2C Opterons AND (SPEC and TPC bench differences between these CPUs?)
4 single core 800 Mhz sparcs to 2 dual core 2.2 Ghz opterons.
c= you went from a Sun box to a "white box" AND (memory subsystem differences? other differences?)
The new hardware is Sun as well. X4100s running Linux. It should be faster all around because the old server is 5 years old.
d= you went from local HD IO to a SAN (many differences hidden in that one line... ...and is the physical layout of tables and things like pg_xlog sane on the SAN?) ...and you did this by just pulling over the old DB onto the new HW?
We rebuild the DB from scratch on the new server. Same table structure though. We reloaded from the source material directly.
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
Cheers, Ron Peacetree
