MySQL sleeping connections - understanding and controling them

What are MySQL sleeping processes and do I have them?

MySQL sleeping processes are simply client connections that are still lingering around for any of the reasons. One of the legitimate reasons is opening a persistent connection to MySQL server. What happens in that case is that the connection is not closed once the first set of operations is completed, but instead the connection is left opened and reused for subsequent operations. This is VERY good for websites with very high load where opening a new connection for each loading of the website is a waste of precious time. However, you might find your self in a situation where this is completely uncalled for. This might be a server where you have many small and lightly visited websites. In this sort of scenario you would be left with a bunch of sleeping connections that can exhaust your servers maximum allowed number of connections and that would certainly defeat the purpose of having persistent connections in the first place.

So if you want to check weather you have sleeping connections go ahead and log into your MySQL server as a root user. After you've logged in, execute the following statement:

mysql> SHOW FULL PROCESSLIST;

You should get something like the following:

mysql> SHOW FULL PROCESSLIST;
+------+-------------+-----------------+-------------+---------+------+-------+
| Id   | User        | Host            | db          | Command | Time | State |
+------+-------------+-----------------+-------------+---------+------+-------+
| 3904 | powerdns    | localhost:44526 | powerdns    | Sleep   |   34 |       |
| 3906 | powerdns    | localhost:44528 | powerdns    | Sleep   |   38 |       |
| 3907 | powerdns    | localhost:44529 | powerdns    | Sleep   |   38 |       |
| 3908 | powerdns    | localhost:44530 | powerdns    | Sleep   |   17 |       |
| 4062 | powerdns    | localhost:37415 | powerdns    | Sleep   |   27 |       |
| 4779 | vexim       | localhost       | vexim       | Sleep   | 151 |       |
| 5550 | valdez      | localhost       | valdez      | Sleep   | 288 |       |
| 5559 | docplanet   | localhost       | docplanet   | Sleep   | 184 |       |
| 5568 | valdez      | localhost       | valdez      | Sleep   |   70 |       |
| 5570 | valdez      | localhost       | valdez      | Sleep   | 220 |       |
| 5573 | valdez      | localhost       | valdez      | Sleep   | 160 |       |
| 5575 | valdez      | localhost       | valdez      | Sleep   | 136 |       |
| 5576 | valdez      | localhost       | valdez      | Sleep   | 122 |       |
| 5577 | valdez      | localhost       | valdez      | Sleep   |   53 |       |
| 5578 | valdez      | localhost       | valdez      | Sleep   |   31 |       |
| 5579 | valdez      | localhost       | valdez      | Sleep   | 238 |       |
| 5580 | valdez      | localhost       | valdez      | Sleep   |    9 |       |
| 5581 | valdez      | localhost       | valdez      | Sleep   | 175 |       |
| 5582 | portal      | localhost       | portal      | Sleep   | 251 |       |
| 5583 | portal      | localhost       | portal      | Sleep   |   33 |       |
| 5584 | portal      | localhost       | portal      | Sleep   |   35 |       |
| 5585 | docplanet   | localhost       | docplanet   | Sleep   | 244 |       |
| 5590 | valdez      | localhost       | valdez      | Sleep   | 167 |       |
| 5593 | valdez      | localhost       | valdez      | Sleep   |   62 |       |
| 5594 | valdez      | localhost       | valdez      | Sleep   | 144 |       |
| 5595 | docplanet   | localhost       | docplanet   | Sleep   | 124 |       |
| 5596 | zavjese     | localhost       | zavjese     | Sleep   | 103 |       |
| 5597 | kamini_posa | localhost       | kamini_posa | Sleep   | 105 |       |
| 5599 | kamini_posa | localhost       | kamini_posa | Sleep   |   89 |       |
| 5600 | zavjese     | localhost       | zavjese     | Sleep   |   81 |       |
| 5601 | root        | localhost       | NULL        | Query   |    0 | NULL |
| 5602 | ivanicplast | localhost       | ivanicplast | Sleep   |   76 |       |
| 5603 | docplanet   | localhost       | docplanet   | Sleep   |   64 |       |
| 5604 | portal      | localhost       | portal      | Sleep   |   39 |       |
| 5605 | portal      | localhost       | portal      | Sleep   |   35 |       |
| 5608 | valdez      | localhost       | valdez      | Sleep   |   18 |       |
| 5609 | marko       | localhost       | marko       | Sleep   |    5 |       |
| 5610 | docplanet   | localhost       | docplanet   | Sleep   |    4 |       |
+------+-------------+-----------------+-------------+---------+------+-------+
38 rows in set (0.00 sec)

You can also do a real time monitoring using the following:

watch -n 1 mysqladmin -uroot -prootpass processlist

The time column will not contain anything larger than wait_timeout seconds (wait_timeout is specified in my.cnf).

This list should give you an idea of how much sleeping processes there are. In case this server had problem with too many open connections, the solution that comes to my mind is to turn off persistent connections for sites that have under 1000 visits a day since the connections time out between the requests anyways.

If you wanted to kill any of the open connections you could use the kill statement:

kill query 5610;

This would kill the last connection on the list (the one belonging to docplanet).

Your thoughts

dokma, 06-10-10 11:40

It's part of [mysqld] section. If you don't have one just add it.

the london dude, 03-10-10 15:08

my my.cnf does not have a wait_timeout line at all. is it part of maybe global variables?

Add comment