For cases where an application fails to close a connection it is no longer using, a low wait_timeout value can help to avoid hitting max_connections simply due to sleeping idle connections that are not in a transaction and will not be reused.
wait_timeout: is a variable representing the amount of time that MySQL will wait before killing an idle connection.
The default wait_timeout variable is 28800 seconds, which is 8 hours. That’s a lot.
A MySQL connection in the Sleep state may occur when below process happens:
- A PHP script connects to MySQL.
- and some queries are executed and then the PHP script does some stuff that takes time without disconnecting from the DB
- and finally, the PHP script ends which means it disconnects from the MySQL server.
- So basically it can end up with many processes in a Sleep state when there are lots of PHP processes that stay connected, without actually doing anything on the database side.
Load on the server can occur when below process happens:
- There are more and more requests coming to Apache which means many pages to generate.
- and then each PHP script, in order to generate a page, connects to the database and does some queries.
- and then these queries take more and more time, as the load on the database server increases Which means more processes keep stacking up.
A solution that can help is to:
- Reduce the time your queries take – optimizing the longest ones.
- Setting MySQL wait_timeout to a suitable value.
- Using below command in MySQL, you can get the current value set for wait_timeout. (Its 28800 second by default)
SHOW SESSION VARIABLES LIKE “wait_timeout”;
SHOW GLOBAL VARIABLES LIKE “wait_timeout”; - Using below command in MySQL, you can set the value for wait_timeout. (you need to log in as a root/admin users for MySQL)
SET @@GLOBAL.wait_timeout=300 - Since next time when the server restarts, the session variables will be set to the default value i.e. 28800 so add/modify the configuration for MySQL in my.cnf file. (sudo vi /etc/my.cnf)
[mysqld]
interactive_timeout=300
wait_timeout=300
NOTE: The interactive timeout does not affect any web application connections.
A high interactive_timeout but a low wait_timeout is normal and is the best practice.
- Using below command in MySQL, you can get the current value set for wait_timeout. (Its 28800 second by default)
References:
http://www.rackspace.com/knowledge_center/article/how-to-change-the-mysql-timeout-on-a-server
http://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-to-many-connections
http://club.orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/
Thank You, Sandeep! It appears your interactive_timeout and wait_timeout values are the sweet spot between “ERROR 2006 (HY000) at line 27652: MySQL server has gone away” and the hardening of the database that we are trying to achieve.