Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 364FC37BA28 for ; Sun, 28 Sep 2008 22:40:32 -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 89425-01 for ; Sun, 28 Sep 2008 22:40:28 -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 5931E37BA27 for ; Sun, 28 Sep 2008 22:40:29 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m8T1eN8u026683; Sun, 28 Sep 2008 21:40:23 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m8T1eNLm026679; Sun, 28 Sep 2008 21:40:23 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Sun, 28 Sep 2008 21:40:22 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: John Huttley cc: pgsql-performance@postgresql.org Subject: Re: Slow updates, poor IO In-Reply-To: <48E003AC.5000306@mib-infotech.co.nz> Message-ID: References: <48DBE587.3050406@mib-infotech.co.nz> <20080926131556.GA1005@commandprompt.com> <48DD6A4A.10209@mib-infotech.co.nz> <48DEB4D4.1020100@mib-infotech.co.nz> <6110.1222612388@sss.pgh.pa.us> <48DFFCDB.3020000@mib-infotech.co.nz> <48E003AC.5000306@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/246 X-Sequence-Number: 31559 On Mon, 29 Sep 2008, John Huttley wrote: > checkpoint _segments=16 is fine, going to 64 made no improvement. You might find that it does *after* increasing shared_buffers. If the buffer cache is really small, the checkpoints can't have very much work to do, so their impact on performance is smaller. Once you've got a couple of hundred MB on there, the per-checkpoint overhead can be considerable. > It would be nice if thing like > * The effect of updates on indexed tables > * Fill Factor > * reindex after restore > Were mentioned in the 'performance' section of the manual, since that's > the part someone will go to when looking for a solution. If you have to reindex after restore to get good performance, that means what you should do instead is drop the indexes on the table during the restore and then create them once the data is there. The REINDEX is more aimed at when the system has been running for a while and getting fragmented. Unfortunately most of the people who know enough about those topics to really do a good treatment of them are too busy fixing slow systems to have time to write about it. There are many articles on this general topic trickling out at http://wiki.postgresql.org/wiki/Performance_Optimization you might find valuable in addition to the manual. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD