poor mysql performance - too much IO or table locking issue?

MySQL & MariaDB Other MySQL® Questions
1

My mysql 5.1.30 database sometimes gets very bogged down. I have a health checker that does a very simple DB query and usually it takes < 10 ms but during high traffic it can return very slowly (> 1 s).

It could be that I simply have too much IO happening on my disk but the thing that concerns me most is that ‘show innodb status’ frequently displays ‘mysql tables in use 1,locked 1’. Does this in and of itself mean the entire table is locked?

I put more data into a google document: the schema for the table that could be locking, the query that is getting run, iostat, and show table status.

[URL]http://docs.google.com/Doc?id=dfn6b52f_0d4sg25gb[/URL]

Thank you for all your help!
andrew

2

Reading your post it looks like you have a lot of IO wait but it doesn’t seem to be so many queries.

What is your setting of the server variable:
innodb_flush_log_at_trx_commit?

If it is set to 1 which is default then InnoDB will flush the transaction log after each update (to be robust in case of power failure) and this flush can only happen about 100-150 times per second depending on disk seek times.

But if you are willing to trade robustness for performance you can change this variable to the value 2 instead:
innodb_flush_log_at_trx_commit = 2

Or see if you can setup/equip the Amazon storage with a write cache (in case it works like a normal RAID controller).