On Fri, 26 Apr 2013, James Harper <james.harper@bendigoit.com.au> wrote:
Which does indicate that the io is struggling when the mysql query is running...
I'm converting to xfs from ext3 to see if there is something gone bad with the filesystem.
XFS performs very well. But unless you happen to be triggering some obscure corner case (which seems unlikely with a program as common as mysqld) I don't think that XFS will give you a huge benefit over Ext3. Eventually they all come down to seek times on disk.
Converting back to xfs is something I'd been meaning to do for a while - the VM is only about 10GB is size so it only takes a few minutes to cp -a.
If you convert filesystem then before you do it please restart mysqld, umount and mount the filesystem, and do any other changes that might happen as part of the XFS conversion while still using Ext3. Then if you get a performance benefit you'll know that it's related to XFS not something else.
I'd already done a reboot that covered this. It's not measurably better after converting as you predicted. I'm now tinkering with innodb options to see if I can improve things. Looking at the strace there are lots of: pwrite(count=512) fsync() pwrite(count=1024) fsync() with the fsync taking around 0.1-0.2 units of time (seconds I guess), which would explain why I'm limited to about 5-10 inserts a second, so the next thing I'll try is changing innodb_flush_method to something else... assuming temporary tables use innodb. Something else to look up I guess. If I can establish that the fsync() is where it's going wrong then the next thing is to find out exactly why this has changed - I still have no explanation for why the system went from flying along with the speed limited pretty much by USB throughput down to almost zero. As I said it has done this before several months ago and then spontaneously went back to the original behaviour before I had a chance to properly investigate. James