Message-ID: <48E036F4.3040609@mib-infotech.co.nz>
Date: Mon, 29 Sep 2008 15:01:24 +1300
From: John Huttley <John@mib-infotech.co.nz>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
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>
	<Pine.GSO.4.64.0809282125550.24903@westnet.com>
In-Reply-To: <Pine.GSO.4.64.0809282125550.24903@westnet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Greg Smith wrote:
> 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.
>
Ahh bugger, I've just trashed my test setup.
I've settled on  64Mb shared memory since I've only got 1Gb or RAM and 
the system impact of 256M is severe.
Also it uses FB-DIMMS which cost arm+leg+first born


>> 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.

I thought that the pg_dump generated files did that, so I dismissed it 
initially. Maybe I did a data only restore into an existing schema..
>
> 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.
>
An of course this is now in mail archive!


> -- 
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
>