pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Greg Smith <gsmith@gregsmith.com>
To: John Huttley <John@mib-infotech.co.nz>
Cc: pgsql-performance@postgresql.org, Dan Langille <dan@langille.org>
Subject: Re: Slow updates, poor IO
Date: Thu, 25 Sep 2008 17:57:35 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0809251750130.16879@westnet.com> (raw)
In-Reply-To: <48DBE587.3050406@mib-infotech.co.nz>
References: <48DBE587.3050406@mib-infotech.co.nz>

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@gregsmith.com http://www.gregsmith.com Baltimore, MD

view thread (22+ messages)  latest in thread

Message-ID: <Pine.GSO.4.64.0809251750130.16879@westnet.com>
Permalink:  ../Pine.GSO.4.64.0809251750130.16879@westnet.com/
Also on:    postgresql.org/message-id/Pine.GSO.4.64.0809251750130.16879@westnet.com

copy link · copy postgr.es

reply

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, dan@langille.org
  Subject: Re: Slow updates, poor IO
  In-Reply-To: <Pine.GSO.4.64.0809251750130.16879@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