Home / server / Questions / 480609
In Process
Sudowned
Asked: 2013-02-21 02:43:28 +0800 CST

MySQL using large amounts of CPU while idle

  • 772

A webserver (Apache + MySQL) locked so badly that I had to powercycle it, and immediately logged in to see what was going on afterward. Load average was over 3%, with MySQL hovering between 20% and 80% of the CPU. Curious, I ran SHOW PROCESSLIST; during a spike of activity, only to find...

+-------+--------+-----------+--------+---------+------+-------+------------------+
| Id    | User   | Host      | db     | Command | Time | State | Info             |
+-------+--------+-----------+--------+---------+------+-------+------------------+
| 10762 | root   | localhost | NULL   | Query   |    0 | NULL  | SHOW PROCESSLIST |
| 11754 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11755 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11756 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11758 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11759 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11761 | elba   | localhost | elba   | Sleep   |    0 |       | NULL             |
| 11763 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11764 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11765 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11766 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11767 | phorum | localhost | phorum | Sleep   |    0 |       | NULL

Just for kicks, I enabled the slow query log and there's nothing there, either. Load average (as well as MySQL's resource usage) has dropped back to normal levels since, but it's still unclear what happened.

Any insight as to what happened? Failing that, what should I log to help get to the bottom of it next time?

linux

1 Answers

  1. Just for kicks, I enabled the slow query log and there's nothing there, either.

    Have you enabled the log-queries-not-using-indexes option?

    I would also suggest you set the long_query_time to zero, then install the Percona Toolkit and run:

    pt-query-digest /path/to/the/slow/log | tee $HOME/mysql-slow_`date +%F`.profile

    to see what happens.

    • 2