Re: strange performance regression between 7.4 and 8.1

At 10:16 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Florian Weimer <fweimer(at)bfk(dot)de> 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

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?)

c= you went from a Sun box to a "white box" AND
(memory subsystem differences? other differences?)

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?

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.

Cheers,
Ron Peacetree