How to Increase/Reduce wait_timeout and interactive_timeout in mySQL

wait_timeout is time for which mySQL waits before closing the idle connection. On most of the linux servers wait_timeout is high and it is better to reduce it to a lower value. Sleeping idle connections in the server will lead to issues because building of idle connections will use high server resources and will result in high server load. Type the command “mysqladmin proc stat” OR “mysqladmin processlist” to see the number of mysql connections in sleep state.

Do not set mySQL wait_timeout to a very low value because you might hit the timeout limit frequently. You will see the error “MySQL server has gone away” if wait_timeout/interactive_timeout and is set too low.

How to check the current mySQL wait_timeout value ?

You must have server root access to increase/decrease the mySQL wait_timeout and interactive_timeout value. You must contact your webhosting provider if you do not have server root access.

1. Login to server via SSH as ‘root’ user

2 Run the command “mysqladmin variables | grep wait_timeout” to check the current wait_timeout value

Example :

[root@server ~]# mysqladmin variables | grep wait_timeout

wait_timeout 28800

On my server wait_timeout value is 28800 seconds. This is the default wait_timeout value on linux servers and it is too high (8 hours wait_timeout).

Method 2

1. Log in to linux server via SSH as ‘root’ user

2. Log in to mySQL server

2. Type “SHOW GLOBAL VARIABLES LIKE “wait_timeout”;” to check timeout value
 

 
On the above server you can see that mySQL wait_timeout and interactive_timeout is 28800.
 

How to Increase / Reduce the mySQL wait_timeout

1. Log into your linux server via SSH as root

2. First take a backup of the mySQL configuration file /etc/my.cnf

cp -a /etc/my.cnf /etc/my.cnf.bakk

3. Add the line “wait_timeout=200” in the mySQL configuration file like shown in the below picture

Save the my.cnf file and exit.
 

 
5. Restart the mySQL service

Command : Service mysql restart