MySQL Connection Sleep

by janani 2014-02-21 18:50:52

Some time our mysql processlist will show the following , which may result in mysql "too many connections" issue.

mysql> show processlist;
+------+------+-----------------+---------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------------+---------------+---------+------+-------+------------------+
| 3773 | root | localhost | dbName | Sleep | 5300 | | NULL |
| 3861 | root | localhost:41338 | dbName | Sleep | 3038 | | NULL |
| 3862 | root | localhost:41428 | dbName | Sleep | 2974 | | NULL |
| 3863 | root | localhost:41443 | dbName | Sleep | 2959 | | NULL |
| 3864 | root | localhost:57044 | dbName | Sleep | 2880 | | NULL |
| 3865 | root | localhost:57055 | dbName | Sleep | 2866 | | NULL |
| 3866 | root | localhost:51797 | dbName | Sleep | 2281 | | NULL |
| 4015 | root | localhost | NULL | Query | 5 | NULL | show processlist |



Update the following variables:




mysql>interactive_timeout=180;
mysql>set wait_timeout=180;


and restart your MySQL.

If you don't want to restart your MySQL just Use the below :


mysql>SET GLOBAL interactive_timeout = 180;
mysql>SET GLOBAL wait_timeout = 180;


(OR)

we can also update the above in /etc/my.cnf file.


This will not close the connections already open but, it will cause the new connections to close in 180 sec.

Also we should check for the mysql connection in our coding .That should be properly closed.



Tagged in:

781
like
0
dislike
0
mail
flag

You must LOGIN to add comments