diagnose and avoid MySQL cpu spikes

Question

I am trying to discover why mysqld sometimes saturates the cpu and stalls.

I suspect it's something to do with updating indices or other such maintenance. I'd like to prove this hypothesis and look at options for avoiding it.

Here's the situation. I have dozens of tables, but based on activity, it seems there are at least two which consistently suffer from this. Let's call them Big and Small. Big contains about 6,000 rows totalling 1Mb (so not all that big) and Small has dozens of rows, about 50 bytes each. Big has a foreign key to Small (InnoDB, on delete cascade, not null).

There are two situations which seem to trigger the problem: a) a modification of a Big.small_id value, or b) the addition of a row to Small.

I would intuitively expect a) to be pretty damn quick, O(log(size of Big)) and b) to be virtually instant because Small is so small and none of Big's references to it have changed.

In each case the subsequent SELECT takes something like twenty gigacycles (!); the one after that takes no time at all. There are other tables which have foreign keys to both of these tables, but there are all fairly small and I assume they're not responsible for this spike.

How can I find out which indices MySQL is updating and how long each takes?

Or, if it isn't updating indices, how can I find out what else is taking so long?

Finally, can I set up mysqld to give a lower thread priority to this work, and/or temporarily disable the index to allow non-indexed (non-blocking) selects to happen concurrently with the maintenance task?

Answer 1

Another diagnostic tool you might look at is mytop. It's basically a wrapper for SHOW PROCESSLIST, but it provides quicker access to this data when you see a problem happening and don't have a mysql cli handy/available to run commands.

Also, have a look at: MySQL "Sending data" horribly slow

Answer 2

There's probably a better solution, but I previously had a situation where I needed to find what db/table was using lots of CPU occasionally. I cronjobbed a "show processlist" and had the output appended to a rolling log. I was doing this every second and kept a rolling window of 6 hours.