Skip site navigation (1) Skip section navigation (2)

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

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: High IOWAIT times, low iops? Need Help with configuration
Date: 2007-06-28 13:39:38
Message-ID: Pine.GSO.4.64.0706280928530.6275@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2007-06-28 13:52:54
Subject: Re: PostgreSQL 8.0 occasionally slow down
Previous:From: Evan ReiserDate: 2007-06-28 13:17:59
Subject: Re: High IOWAIT times, low iops? Need Help with configuration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group