MySQL started to consume about 40% of system CPU time and got unresponsive suddenly

Question

I use Debian 6.0.3 x86_64 and MySQL 5.5.20-1~dotdeb.0-log from the Dotdeb repository. MySQL process started to consume a lot of "sy" CPU time serveral hours ago according to this graph. I was not able to connect to running mysqld process and had to kill it. I found nothing useful in logs. My setup seems to be quite common (I assume Dotdeb just redistributes stock MySQL versions) and I have never seen anything like this before. What is the possible root cause of this? How can I prevent this situation in the future?

Answer 1

If it happens again, run strace -p <PID> -T or swap the -T with -c.

The -T option prints a time delta after each system call that lets you know how long it took. If you see particular calls like stat() and open() taking a long time you can infer one thing and if you see ones like semop() or poll() taking a long time you can infer something else. The same goes for seeing a lot of them but this can be more difficult to see which is where the next option comes in.

The -c option waits until either the program finishes or you break out with ctrl-c and then prints a summary of all the system calls and how much time was spent in each type.

With any luck, this should let you know what MySQL is doing. Then you just have to figure out what to do about it.