Обсуждение: High IOWAIT times, low iops? Need Help with configuration

I was wondering if you guys have some suggested settings for our server, i think we are not hardware limited but the configureation is set up incorrectly.  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, this is a pretty typical output for #iostat -m 5

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
                 0.41    0.00     0.41       96.28       0.00     2.90

Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
sda              90.63         0.08         0.56          0          2
sdc               0.00         0.00         0.00          0          0
sdd              94.09         0.19         1.74          0          8


sda = 2x320GB 7200rpm in RAID1
sdc = 2x150GB 10krpm in RAID1    (transaction log is on this array)
sdd = 6x150GB 10krpm in RAID 10 (database is on the array)

raid controller = 3ware 9650 12port - 256MB cache

Evan Reiser wrote:
> I was wondering if you guys have some suggested settings for our server, i
> think we are not hardware limited but the configureation is set up
> incorrectly.  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, this is a pretty typical output
> for #iostat -m 5
>
> avg-cpu:  %user   %nice %system %iowait  %steal   %idle
>                 0.41    0.00     0.41       96.28       0.00     2.90
>
> Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
> sda              90.63         0.08         0.56          0          2
> sdc               0.00         0.00         0.00          0          0
> sdd              94.09         0.19         1.74          0          8
>
>
> sda = 2x320GB 7200rpm in RAID1
> sdc = 2x150GB 10krpm in RAID1    (transaction log is on this array)
> sdd = 6x150GB 10krpm in RAID 10 (database is on the array)

OK, so no write activity on the transaction log, and hardly any reading
on sdd. Your disks are practically idle, and yet iowait is at 96% - very
strange.

> raid controller = 3ware 9650 12port - 256MB cache
>
> 8GB RAM, core 2 duo - quad core
>
> it would seem like the io subsystem is the limiting factor, but i feel like
> we should be barely hitting a wall, you can see from the example its
> writing
> < 2MB/s to the array
>
> Here's some of our settings
>
> shared_buffers = 256MB                  # min 128kB or max_connections*16kB
> temp_buffers = 32MB                     # min 800kB
> max_prepared_transactions = 50          # can be 0 or more
> work_mem = 32MB                         # min 64kB
> maintenance_work_mem = 32MB             # min 1MB
> max_stack_depth = 7MB                   # min 100kB
>
> max_fsm_pages = 512000          # min max_fsm_relations*16, 6 bytes

Well, you might want to tweak these, but they're not going to completely
kill your io.

> fsync = off                             # turns forced synchronization

You'll be turning this back on in production, I take it?

Hmm - ideas
1. Run a VACUUM FULL on your database(s) and see what happens with your
io then
2. Test a block copy, something like (but a directory on sdd):
    dd if=/dev/zero of=/tmp/empty count=1000000
    That should show an upper limit for your write speed.
3. Google around and check there aren't any issues with your raid
controller and kernel/driver versions.

--
   Richard Huxton
   Archonet Ltd

we've tried benchmarking the array, the data array can write at
800mb/s for files less than 256mb (raid write cache), after which it
can sustain 300mb/s, it seems like it can also handle 6-700 iops when
benchmarking. it seems to work as expected outside of postgres, I
guess we can look at the drivers, let me know if you guys have any
other suggestions, thanks for your help, -evan

On 6/28/07, Richard Huxton <dev@archonet.com> wrote:
> Evan Reiser wrote:
> > I was wondering if you guys have some suggested settings for our server, i
> > think we are not hardware limited but the configureation is set up
> > incorrectly.  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, this is a pretty typical output
> > for #iostat -m 5
> >
> > avg-cpu:  %user   %nice %system %iowait  %steal   %idle
> >                 0.41    0.00     0.41       96.28       0.00     2.90
> >
> > Device:            tps    MB_read/s    MB_wrtn/s    MB_read    MB_wrtn
> > sda              90.63         0.08         0.56          0          2
> > sdc               0.00         0.00         0.00          0          0
> > sdd              94.09         0.19         1.74          0          8
> >
> >
> > sda = 2x320GB 7200rpm in RAID1
> > sdc = 2x150GB 10krpm in RAID1    (transaction log is on this array)
> > sdd = 6x150GB 10krpm in RAID 10 (database is on the array)
>
> OK, so no write activity on the transaction log, and hardly any reading
> on sdd. Your disks are practically idle, and yet iowait is at 96% - very
> strange.
>
> > raid controller = 3ware 9650 12port - 256MB cache
> >
> > 8GB RAM, core 2 duo - quad core
> >
> > it would seem like the io subsystem is the limiting factor, but i feel
> like
> > we should be barely hitting a wall, you can see from the example its
> > writing
> > < 2MB/s to the array
> >
> > Here's some of our settings
> >
> > shared_buffers = 256MB                  # min 128kB or
> max_connections*16kB
> > temp_buffers = 32MB                     # min 800kB
> > max_prepared_transactions = 50          # can be 0 or more
> > work_mem = 32MB                         # min 64kB
> > maintenance_work_mem = 32MB             # min 1MB
> > max_stack_depth = 7MB                   # min 100kB
> >
> > max_fsm_pages = 512000          # min max_fsm_relations*16, 6 bytes
>
> Well, you might want to tweak these, but they're not going to completely
> kill your io.
>
> > fsync = off                             # turns forced synchronization
>
> You'll be turning this back on in production, I take it?
>
> Hmm - ideas
> 1. Run a VACUUM FULL on your database(s) and see what happens with your
> io then
> 2. Test a block copy, something like (but a directory on sdd):
>     dd if=/dev/zero of=/tmp/empty count=1000000
>     That should show an upper limit for your write speed.
> 3. Google around and check there aren't any issues with your raid
> controller and kernel/driver versions.
>
> --
>    Richard Huxton
>    Archonet Ltd
>

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