How and Why You Should Change MySQL’s wait_timeout and interactive_timeout Variables

wait_timeout variable represents 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.

I’ve read in different forums/blogs that putting wait_timeout too low (e.g. 30, 60, 90) can result in MySQL has gone away error messages.
So you’ll have to decide for your configuration.

Requirement: You will need admin/root access to the server.

Step 1) Edit your /etc/my.cnf file and enter the following 2 values.

[mysqld]
interactive_timeout=300
wait_timeout=300

Step 2) run the command and enter your root password
mysql -uroot -p -e”SET GLOBAL wait_timeout=300; SET GLOBAL interactive_timeout=300;”

If you are connected from the mysql console
e.g. mysql> you can run this command which will show you global and session variables.

SELECT @@global.wait_timeout, @@global.interactive_timeout, @@session.wait_timeout, @@session.interactive_timeout;

+———————–+——————————+————————+——————————-+
| @@global.wait_timeout | @@global.interactive_timeout | @@session.wait_timeout | @@session.interactive_timeout |
+———————–+——————————+————————+——————————-+
|                   300 |                          300 |                  28800 |                         28800 |
+———————–+——————————+————————+——————————-+
1 row in set (0.00 sec)

To see the the current values you can run this command
mysql> show global variables like ‘%timeout%’;

+—————————-+———-+
| Variable_name              | Value    |
+—————————-+———-+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 300      |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 300      |
+—————————-+———-+
10 rows in set (0.00 sec)

Credits:
Step1 instructions from RolandoMySQLDBA
http://serverfault.com/questions/355750/mysql-lowering-wait-timeout-value-to-lower-number-of-open-connections

Step 2) Eliot Kristan’s blog http://www.eliotk.net/12/21/mysql-wait_timeout-default-is-set-too-high/