Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4D1C764FC32 for ; Thu, 25 Sep 2008 18:57:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33999-10 for ; Thu, 25 Sep 2008 18:57:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id A2C4064FC31 for ; Thu, 25 Sep 2008 18:57:48 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m8PLva5l023233; Thu, 25 Sep 2008 17:57:36 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m8PLvZfb023224; Thu, 25 Sep 2008 17:57:35 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Thu, 25 Sep 2008 17:57:35 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: John Huttley cc: pgsql-performance@postgresql.org, Dan Langille Subject: Re: Slow updates, poor IO In-Reply-To: <48DBE587.3050406@mib-infotech.co.nz> Message-ID: References: <48DBE587.3050406@mib-infotech.co.nz> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/230 X-Sequence-Number: 31543 On Fri, 26 Sep 2008, John Huttley wrote: > running update file set perms='0664' took about 10 mins What do you have checkpoint_segments and shared_buffers set to? If you want something that's doing lots of updates to perform well, you need to let PostgreSQL have a decent size chunk of memory to buffer the index writes with, so it's more likely they'll get combined into larger and therefore more easily sorted blocks rather than as more random ones. The randomness of the writes is why your write rate is so slow. You also need to cut down on the frequency of checkpoints which are very costly on this type of statement. Also: which version of PostgreSQL? 8.3 includes an improvement aimed at updates like this you might benefit from. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD