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 D58F8D1B46F for ; Mon, 8 Dec 2003 17:44:27 +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 65976-05 for ; Mon, 8 Dec 2003 13:43:56 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by svr1.postgresql.org (Postfix) with ESMTP id 3D613D1B491 for ; Mon, 8 Dec 2003 13:43:54 -0400 (AST) Received: from luna.lyris.com (luna.lyris.com [216.91.57.116]) by luna.lyris.com (8.12.8/8.12.8) with ESMTP id hB8HhlIt007581 for ; Mon, 8 Dec 2003 09:43:47 -0800 Received: from luna.lyris.com ([216.91.57.116]) by luna.lyris.com (MailMonitor for SMTP v1.2.2 ) ; Mon, 8 Dec 2003 09:43:47 -0800 (PST) Received: from cletus.lyris.com (cletus.lyris.com [216.91.56.155]) by luna.lyris.net with SMTP (MailShield v2.04 - LINUX Jul 17 2001 16:58:31); Mon, 08 Dec 2003 09:43:47 -0800 Subject: Re: tuning questions From: Jack Coates To: pgsql-performance In-Reply-To: <1070673761.13542.534.camel@cletus.lyris.com> References: <1070553983.6498.52.camel@cletus.lyris.com> <200312042347.54461.dev@archonet.com> <1070584326.18838.235.camel@cletus.lyris.com> <200312050926.05155.josh@agliodbs.com> <1070673761.13542.534.camel@cletus.lyris.com> Content-Type: text/plain Organization: Lyris Technologies, Inc. Message-Id: <1070905424.16088.63.camel@cletus.lyris.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.4-9mdk Date: Mon, 08 Dec 2003 09:43:45 -0800 Content-Transfer-Encoding: 7bit X-SMTP-HELO: cletus.lyris.com X-SMTP-MAIL-FROM: jack@lyris.com X-SMTP-RCPT-TO: pgsql-performance@postgresql.org X-SMTP-PEER-INFO: cletus.lyris.com [216.91.56.155] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/154 X-Sequence-Number: 5014 On Fri, 2003-12-05 at 17:22, Jack Coates wrote: ... > That's it, I'm throwing out this whole test series and starting over > with different hardware. Database server is now a dual 2GHz Xeon with > 2GB RAM & 2940UW SCSI, OS and PG's logs on 36G drive, PG data on 9GB > drive. Data is importing now and I'll restart the tests tonight. Sorry to reply at myself, but thought I'd note that the performance is practically unchanged by moving to better hardware and separating logs and data onto different spindles. Although the disks are twice as fast by hdparm -Tt, their behavior as shown by iostat and vmstat is little different between dual and dev (single P4-2GHz/512MB/(2)IDE drives). Dual is moderately faster than my first, IDE-based testbed (about 8%), but still only 30% as fast as the low-powered dev. I've been running vacuumdb --analyze and/or vaccuumdb --full between each config change, and I also let the job run all weekend. Saturday it got --analyze every three hours or so, Sunday it got --analyze once in the morning. None of these vacuumdb's are making any difference. Theories at this point, in no particular order: a) major differences between my 7.3.4 from source (compiled with no options) and dev's 7.3.2-1PGDG RPMs. Looking at the spec file doesn't reveal anything glaring to me, but is there something I'm missing? b) major differences between my kernel 2.4.18-14smp (RH8) and dev's kernel 2.4.18-3 (RH7.3). c) phase of the moon. While SQL optimization is likely to improve performance across the board, it doesn't explain the differences between these two systems and I'd like to avoid it as a theory until the fast box can perform as well as the slow box. Any ideas? Thanks in advance, -- Jack Coates, Lyris Technologies Applications Engineer 510-549-4350 x148, jack@lyris.com "Interoperability is the keyword, uniformity is a dead end." --Olivier Fourdan