From: Alex Deucher <alexdeucher@gmail.com>
To: Ron <rjpeace@earthlink.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange performance regression between 7.4 and 8.1
Date: Fri, 2 Mar 2007 11:03:06 -0500
Message-ID: <a728f9f90703020803r2ab76789sa63def90642fe5c@mail.gmail.com> (raw)
In-Reply-To: <E1HN9zd-0001tV-Gi@elasmtp-galgo.atl.sa.earthlink.net>
References: <a728f9f90703011244r577d56d7y422ab4bd71bb760e@mail.gmail.com>
<82slco6n9t.fsf@mid.bfk.de>
<a728f9f90703020716g45b2d281u9894973abad3e68a@mail.gmail.com>
<E1HN9zd-0001tV-Gi@elasmtp-galgo.atl.sa.earthlink.net>
On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> At 10:16 AM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Florian Weimer <fweimer@bfk.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alexdeucher@gmail.com, rjpeace@earthlink.net
Subject: Re: strange performance regression between 7.4 and 8.1
In-Reply-To: <a728f9f90703020803r2ab76789sa63def90642fe5c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox