1

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)
Improve this question
4
  • Does show processlist show nothing or nothing strange. Commented Jun 12, 2012 at 17:17
  • Looks like nothing special is happened. Most processes sleep. Added result in question post. I'm not sure, but sleep processes should displayed? Commented Jun 12, 2012 at 17:36
  • 1
    Are these outputs from after you killed the process? Commented Jun 12, 2012 at 17:38
  • @faker No. The process still sleep. Commented Jun 12, 2012 at 17:48

1 Answer 1

Reset to default
2

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.

Improve this answer
4
  • Thanks. I will try to reduce to 100 processes. Unfortunately the website has a very much code (~200mb of code only) and find the problem looks is unrealistic.. Plus this bad process starts is very random. Commented Jun 12, 2012 at 18:13
  • You can log all queries that are run for a bit, and compare IDs with the sleeping processes. Commented Jun 12, 2012 at 18:14
  • But Id in mysql isn't equal PID of Linux? Commented Jun 12, 2012 at 18:16
  • The MySQL general query log (log = /var/log/mysql.log in my.cnf) should be verbose enough for you to determine which queries are being run before the connections are left open. Don't run this log for very long, though; it kills performance Commented Jun 12, 2012 at 18:21

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.