X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 46DDDD1B48E for ; Tue, 9 Dec 2003 17:36:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 75503-09 for ; Tue, 9 Dec 2003 13:36:13 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 46C4ED1B487 for ; Tue, 9 Dec 2003 13:36:12 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4049825; Tue, 09 Dec 2003 09:37:02 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Jack Coates , Tom Lane Subject: Re: tuning questions Date: Tue, 9 Dec 2003 09:35:04 -0800 User-Agent: KMail/1.4.3 Cc: pgsql-performance References: <1070553983.6498.52.camel@cletus.lyris.com> <22185.1070911198@sss.pgh.pa.us> <1070989072.16079.19.camel@cletus.lyris.com> In-Reply-To: <1070989072.16079.19.camel@cletus.lyris.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200312090935.04658.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/160 X-Sequence-Number: 5020 Jack, > Right, because re-architecture of a cross-platform query makes sense if > performance is bad on all systems, but is questionable activity when > performance is fine on some systems and lousy on others. Hence my > statement that while SQL optimization is certainly something we want to > do for across-the-board performance increase, I wanted to focus on other > issues for troubleshooting this problem. I will be back to ask about > data access models later :-) Yes, but an EXPLAIN ANALYZE will also help show issues like sorts running out of memory, etc. Really, we don't currently have enough information to do more than speculate; it's like trying to repair a car engine wearing a blindfold. Particularly since it's possible that there are only 1 or 2 "bad queries" which are messing everything else up. For that matter, it would really help to know: -- How many simulatneous connections are running update queries during this process? -- How about some sample VACUUM VERBOSE results for the intra-process vacuums? > I ended up going back to a default postgresql.conf and reapplying the > various tunings one-by-one. Turns out that while setting fsync = false > had little effect on the slow IDE box, it had a drastic effect on this > faster SCSI box and performance is quite acceptable now (aside from the > expected falloff of about 30% after the first twenty minutes, which I > believe comes from growing and shrinking tables without vacuumdb > --analyzing). Well, that brings 2 things immediately to mind: 1) That may improve performance, but it does mean that if your machine loses power you *will* be restoring from backup. It's risky to do. 2) Your IDE system has write-caching enabled. Once again, this is a nice performmance boost, if you don't mind database corruption in a power-out. -- Josh Berkus Aglio Database Solutions San Francisco