"reprinted" How Long was "too long" for MySQL Connections to sleep?

From:http://dba.stackexchange.com/questions/1558/how-long-is-too-long-for-mysql-connections-to-sleep

20down Votefavorite11

I logged onto my server to check the status of the database and noticed on the that SHOW PROCESSLIST; there is a large number of Conne Ctions sleeping that is very old. What duration should the connection time limit is before the connection should be killed? How does I set the time limit in MySQL 5.0.51a-3ubuntu5.4? Note: I am using PHP 5.2.x and Symfony framework 1.2. MySQL mysql-5 ShareImprove this question edited  3:33 rolandomysqldba14 132264 Asked Mar 3 ' at 20:37Patrick1,4646

Add a Comment

1 answeractiveoldestvotes

Up vote32down voteaccepted

Mysqld'll timeout DB Connections based on both (2) server options: Interactive_timeout Wait_timetout Both is 28800 seconds (8 hours) by default. You can set these options in/etc/my.cnf If Your connections is persistent (opened via Mysql_pconnect) you could lower these numbers-something reasonable like (min) or even (1 min). Or, if your app works just fine, you can leave the default. This was up to you. You must set these as follows in MY.CNF (takes effect after MySQL restart): [mysqld]interactive_timeout=180wait_timeout=180 If you don't want to restart MySQL and then run these the commands: SET GLOBAL interactive_timeout = 180;SET GLOBAL wait_timeout = 180; This would not close the connections already open. This would cause new connections to close in the + + sec. ShareImprove this answer Edited Dec ' at 22:52 answered Mar 3 ' at 21:22rolandomysqldba103k

So In/etc/my.cnf I would just put wait_timeout=180 –patrick Mar 3 ' one at 22:28 @Patrick Yes, but you need to set both Interactive_timeout and wait_timeout to 180. I updated my answer for your response. –ROLANDOMYSQLDBA Mar 3 ' one at 22:29 This file can is elsewhere dev.mysql.com/doc/refman/5.1/en/option-files.html and these options may not be explicited (writ e them by yourself) –nicolallias June ' at 15:33 Add a Comment

"reprinted" How Long was "too long" for MySQL Connections to sleep?