MySQL Connection Sleep
by janani[ Edit ] 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.
