Skip to content
Home » MySQL wait_timeout and sleep connections.

MySQL wait_timeout and sleep connections.

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:

  1. Reduce the time your queries take – optimizing the longest ones.
  2. 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.

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/

1 thought on “MySQL wait_timeout and sleep connections.”

  1. 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.

Leave a Reply

Your email address will not be published.

0 Shares
Tweet
Pin
Share
Share
Share
%d bloggers like this: