From: Greg Smith <gsmith@gregsmith.com>
To: John Huttley <John@mib-infotech.co.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
Date: Sun, 28 Sep 2008 21:40:22 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0809282125550.24903@westnet.com> (raw)
In-Reply-To: <48E003AC.5000306@mib-infotech.co.nz>
References: <48DBE587.3050406@mib-infotech.co.nz>
<20080926131556.GA1005@commandprompt.com>
<48DD6A4A.10209@mib-infotech.co.nz>
<dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
<48DEB4D4.1020100@mib-infotech.co.nz>
<6110.1222612388@sss.pgh.pa.us>
<48DFFCDB.3020000@mib-infotech.co.nz>
<48E003AC.5000306@mib-infotech.co.nz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com, John@mib-infotech.co.nz
Subject: Re: Slow updates, poor IO
In-Reply-To: <Pine.GSO.4.64.0809282125550.24903@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox