Last updated on: 2020-07-13
Authored by: Rose Contreras
When an application fails to close an unused connection, a low wait_timeout
value helps you avoid
exceeding the permitted number of connections. Use the following instructions to set this vakue:
Log in to your server by using Secure Shell® (SSH).
Use the sudo command to edit my.cnf
, the MySQL® configuration file.
$ sudo vi /etc/my.cnf
Locate the timeout configuration and make the adjustments that fit your server.
wait_timeout = 28800
interactive_timeout = 28800
The interactive_timeout
value does not affect any web application connections. A low
wait_timeout
is a normal best practice.
Stateless PHP environments do well with a 60-second timeout or less. Applications that use a
connection pool (Java®, .NET®, and so on) need to adjust the wait_timeout
value
to match their connection pool settings. The default 8 hours = 28800
seconds works well with
properly-configured connection pools.
Configure the wait_timeout
to be slightly longer than the application connection pool’s
expected connection lifetime as a safety check. Consider changing the value online because that
does not require a MySQL restart, and you can adjusted it while the server runs without
incurring downtime. Change the value to set global wait_timeout=60
, and any newly created
sessions inherit it. Be sure to preserve the setting in my.cnf
. Any existing
connections need to hit the old value of wait_timeout
if the application abandoned
the connection. If you do have reporting jobs that do longer local processing while in
a transaction, you might consider having such jobs issue set session wait_timeout=3600
upon connecting.
Save the changes and exit the editor.
Use the following command to restart MySQL and apply the changes, if required:
$ sudo /etc/init.d/mysql restart
©2020 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License