Loads of sleeping MySQL connections?

5 March 2012 — Leave a comment

Then probably the code is not properly disconnecting the MySQL connection. The time MySQL waits for the next command to be send, can be controlled by the ‘wait_timeout’ parameter. The default is a massive 28800 seconds (8h). Setting this value too low gives ‘MySQl server has gone away’ errors, so be careful.

Debugging scripts that create many ‘sleeping’ connections is challenging. An easy solution is setting the ‘wait_timeout’ parameter to something low after the MySQL connection is started. This setting is then valid only for this current session.

Issue this query after every connect:

SET wait_timeout=60;

Database administrators can permanently change this setting in ‘/etc/mysql/my.cnf’ or by issuing a

SET GLOBAL wait_timeout=60;

to change it during runtime. Don’t forget to edit ‘my.cnf’ also, otherwise the setting is back to default the next time MySQL restarts.

No Comments

Be the first to start the conversation!

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s