Re: Slow updates, poor IO

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD