Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)
share|improve this question
    
Does show processlist show nothing or nothing strange. –  Hyppy Jun 12 '12 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? –  vlad Jun 12 '12 at 17:36
1  
Are these outputs from after you killed the process? –  faker Jun 12 '12 at 17:38
    
@faker No. The process still sleep. –  vlad Jun 12 '12 at 17:48

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  vlad Jun 12 '12 at 18:13
    
You can log all queries that are run for a bit, and compare IDs with the sleeping processes. –  Hyppy Jun 12 '12 at 18:14
    
But Id in mysql isn't equal PID of Linux? –  vlad Jun 12 '12 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 –  Hyppy Jun 12 '12 at 18:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.