Message-ID: <48E003AC.5000306@mib-infotech.co.nz>
Date: Mon, 29 Sep 2008 11:22:36 +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>
In-Reply-To: <48DFFCDB.3020000@mib-infotech.co.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks to everyone that responded.
I've done some benchmarking

checkpoint _segments=16 is fine, going to 64 made no improvement.
Using "update file set size=99" as a statement, but changing 99 on each 
run..

With 32M shared memory, time in sec and leaving the system idle long 
enough between runs for auto vacuum to complete.

415
421
470

The I decided to drop the Db and restore from a dump

1150
1500
1018
1071
1077
1140

Then I tried shared_mem=256M as suggested.

593
544

So thats made a big difference. vmstat showed a higher, more consistent, 
IO level

I wondered why it slowed down after a restore. I thought it would 
improve, less fragmentation
and all that. So I tried a reindex on all three indexes.

209
228

So thats it! lots of ram and reindex as part of standard operation.

Interestingly, the reindexing took about 16s each. The update on the 
table with no indexes took about 48sec
So the aggregate time for each step would be about 230s. I take that as 
being an indicator that it is
now maximally efficient.


The option of having more spindles for improved IO request processing 
isn't feasible in most cases.
With the requirement for redundancy, we end with a lot of them, needing 
an external enclosure.
They would have to be expensive SCSI/SAS/FC drives too,  since SATA just 
don't have the IO processing.

It will be interesting to see what happens when good performing SSD's 
appear.

Meanwhile RAM is cheaper than that drive array!

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.


Again, thanks to everyone,

--John