Re: strange performance regression between 7.4 and 8.1

On 3/2/07, Ron <rjpeace(at)earthlink(dot)net> wrote:
> 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
>

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