Trying to track down mysql tables responsible for disk access spikes
Currently working on speeding up my company's database server, some mysql inserts are taking over a second during peak times, which is about 500 times longer than they usually do. Been logging statistics of cpu load, ram and block io, seems only thing regularly causing a bottleneck in the disk access, which i suppose is to be expected.
In order to combat this problem what I'd ideally like to do is find out which tables are responsible for the most disk reading and writing, our system is incredibly poorly written and I suspect there will be a lot of places I can speed things up using memory tables, memcache, or just rewriting how that part of the system works.
I'm sure I've seen a linux command somewhere that would allow me to log the read and write stats to all open files at a particular time, it was something like lsof but it also had info on how much data was queued to be written or read from each file. If anyone can tell me what that might have been, or anything similar that would be very useful, as I could just apply it to the mysql table files.
Failing that I imagine there are other ways of doing this that I'm overlooking, perhaps even in mysql itself. Any suggestions would be welcome!