I have a problem with MySQL server.
Some mysql thread for a few hours eating up the whole processor. Killing the process certainly helps, but how is it possible to track that the code is running inside?
My current top:
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ IO Command
1353 mysql 20 0 340M 70004 7652 S 31.0 1.1 1h34:28 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
4344 mysql 20 0 340M 70004 7652 S 3.0 1.1 5:17.75 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
5870 mysql 20 0 340M 70004 7652 S 2.0 1.1 1:13.46 0 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket
mysql> SHOW PROCESSLIST;
+------+-------+-----------+---------+---------+------+--------------+---------------
| Id | User | Host | db | Command | Time | State | Info
+------+-------+-----------+---------+---------+------+--------------+----------------
| 8731 | sites | localhost | mywebsite | Sleep | 2520 | | NULL
| 8734 | sites | localhost | mywebsite | Sleep | 2516 | | NULL
| 8737 | sites | localhost | mywebsite | Sleep | 2508 | | NULL
| 8741 | sites | localhost | mywebsite | Sleep | 2502 | | NULL
...
| 9848 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST
| 9952 | sites | localhost | mywebsite | Sleep | 2 | | NULL
| 9953 | sites | localhost | mywebsite | Query | 2 | Sending data | SELECT user_info.name, |
+------+-------+-----------+---------+---------+------+--------------+---------------------------
150 rows in set (0.00 sec)
Well, after killing the process (it eating up the whole cpu already) the output is changes (10 minuts after and still no empty processes):
mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 952 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
Best Answer
Sleeping MySQL processes do in fact use up CPU.
150 sleeping queries is a lot. Do you have hundreds (or more) of concurrent connections? If not, this is probably the first thing to look at.
Within your web application, make sure you close the MySQL connection after you've finished your query. mysql_close() in PHP, but implementation is based on your current setup.