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)
vlad
  • 129
  • 1
  • 6

1 Answers1

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.

Hyppy
  • 15,608
  • 1
  • 38
  • 59
  • 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