From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: High IOWAIT times, low iops? Need Help with configuration
Date: Thu, 28 Jun 2007 09:39:38 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0706280928530.6275@westnet.com> (raw)
In-Reply-To: <5f33a0cb0706271738x1cd5dacdpaf925120f7a1130c@mail.gmail.com>
References: <5f33a0cb0706271614q2bd34f58qb87a307f6424fba6@mail.gmail.com>
<5f33a0cb0706271738x1cd5dacdpaf925120f7a1130c@mail.gmail.com>
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
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
Subject: Re: High IOWAIT times, low iops? Need Help with configuration
In-Reply-To: <Pine.GSO.4.64.0706280928530.6275@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