Re: High IOWAIT times, low iops? Need Help with configuration

On Wed, 27 Jun 2007, Evan Reiser wrote:

> For some reason our database seems to have trouble handling 10+ inserts
> per second which seems to be a pretty trivial load for this hardware,
> we're seeing very high %iowait

Two things come to mind:

1) Is the table you're inserting into very complicated, with lots of
indexes or triggers on it?  Low I/O rates but high wait times are typical
of when the data needed to update is spread out across the disk
considerably, so there's lots of disk seeking involved even though the
writes are relatively small.  That can happen if there are lots of index
blocks to be updated every time you do an insert.  Taking a look at VACCUM
VERBOSE ANALYZE may either fix the problem or give you an idea what's
going on.  You might want to cluster your indexes at some point to help
out with this.

2) If you still have checkpoint_segments at the default of 3, your system
could be basically in a continuous checkpoint.  Try making that 10X bigger
as a start just to see if it improves things; you may end up settling for
a much larger value before you're done.

> 8GB RAM, core 2 duo - quad core
> shared_buffers = 256MB                  # min 128kB or max_connections*16kB

And while not necessarily causing the problem you asked about, this is off
by an order of magnitude if this server is mainly for PostgreSQL, and you
should be setting effective_cache_size as well if you're not doing that.
See http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm for a
quick intro to things to consider.

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