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