Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 53B984769F6 for ; Thu, 5 Sep 2002 20:41:06 -0400 (EDT) Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id B90984764C6 for ; Thu, 5 Sep 2002 20:41:05 -0400 (EDT) Received: from [216.135.165.74] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1635430 for ; Thu, 05 Sep 2002 17:41:07 -0700 From: "Josh Berkus" Subject: Hardware performance for large updates To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Thu, 05 Sep 2002 17:41:07 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/29 X-Sequence-Number: 9306 Folks, I'm doing some massive data transformations on Postgresql, and they're a lot slower than they should be. I'm looking for some tips on improving things. If the PGSQL-PERFORMANCE list was ever created, please tell me and I'll go over there. The update: A series of 7 update statements which cull data from a 1.5 million row table to update a 120,000 row table. The Machine: A dual-processor RAID 5 UW SCSI server. The postgresql.conf settings: Connections: 128 Shared Buffers: 256 Sort Mem: 1024 Checkpoint Segments: 16 Stats on. Light debug logging. The problem: The update series (done as a function) takes 10-15 minutes. During this time, the CPU is never more than 31% busy, only 256mb of 512 is in use, and the disk channel is only 25% - 50% saturated. As such, is seems like we could run things faster. What does everybody suggest tweaking? -Josh Berkus