Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A90749FB7DB for ; Fri, 2 Mar 2007 12:03:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 22206-02 for ; Fri, 2 Mar 2007 12:03:08 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.229]) by postgresql.org (Postfix) with ESMTP id B20719FB42D for ; Fri, 2 Mar 2007 12:03:07 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so826230wxd for ; Fri, 02 Mar 2007 08:03:07 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=FhoHlc1U5ZBELJBorWYieXCVpvO7U/ryEx8mBWLSRsPWLD4E27haXOyOWetMAdqDAxqJKZgjErGNxkYMTXDAIqGd50RiTVN77SaUVlKJW3mTV9dsNPq0kSfxFAYbKk3kVvvYMlB87uL80PBAvXPl48jLJKqriJOZGQItj+fayiU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=PhW6g0kZXwXHOlP/8Bp9hAeLNavqQYTrpacgoVd7ddj92/5n66GitmQYsLDeHLOuVdR4RiEhUwtCyJp3+aZEMjC2SILT5WTprEenVwHfv0jAWk8VAmotgKSD27QOvJGW2zkk++UikuNu00RuLVxgTNsNrnXhNr5MwBQNr29tWLs= Received: by 10.90.115.9 with SMTP id n9mr3031512agc.1172851387221; Fri, 02 Mar 2007 08:03:07 -0800 (PST) Received: by 10.90.116.10 with HTTP; Fri, 2 Mar 2007 08:03:06 -0800 (PST) Message-ID: Date: Fri, 2 Mar 2007 11:03:06 -0500 From: "Alex Deucher" To: Ron Subject: Re: strange performance regression between 7.4 and 8.1 Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <82slco6n9t.fsf@mid.bfk.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/59 X-Sequence-Number: 23464 On 3/2/07, Ron wrote: > At 10:16 AM 3/2/2007, Alex Deucher wrote: > >On 3/2/07, Florian Weimer 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 > >