≡ Menu

Change MySQL wait_timeout variable

MySQL wait_timeout variable represents the number of seconds that MySQL will wait till it closes idle connections. The default value is 28800 seconds (8 hours). This may be too high for most websites!

If you have a very high traffic website and your database queries all complete is less than a few seconds, there is no need to keep the wait_timeout to anything more than a few minutes. On the other hand, if like on my server, you have lots of background processes running that continuously update the database and hold on to mysql connections, the default value of wait_timeout should be what you should be using.

So, first classify your website into one of the following:

1) A very busy website with lots of pages rendering data from the database. SQL queries and connections do not last more than a few seconds.
2) A busy website with lots of background processes (like web crawling) that hold on to old connections for hours

So if you fall in the first category, you should reduce your wait_timeout.

Another thing to consider is how many idle connections are present at a given time:

mysql> show full processlist;
------------------------------------------------------------+
| Id     | User   | Host      | db       | Command | Time | State      | Info                                                                                      |
------------------------------------------------------------+
| 398780 | dbuser | localhost | database | Sleep   |  169 |            | NULL                                                                |
| 399056 | dbuser | localhost | database | Sleep   |  129 |            | NULL                                                                                      |
| 399491 | dbuser | localhost | database | Sleep   |   87 |            | NULL                                                                          

It looks like not many processes are idle on my server. If you see hundreds of processes idle on the server in the sleep state, it might be wise to reduce the wait_timeout variable to a few minutes.

How to check what is the value of wait_timeout?

To check your wait_timeout variable, just use the MySQL command line tool:

mysql -u databaseusername -p
mysql> SHOW VARIABLES LIKE "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

So on my server, the wait_timeout is set to 8 hours (28800 seconds).

How to change the wait_timeout variable?

First, locate your my.cnf file:

ewhathow@ewhathow.com [~]# mysql --help | grep my.cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
ewhathow@ewhathow.com [~]#

On my Linux CentOS box, my.cnf is located at /etc/my.cnf.

Now check if you have wait_timeout set:

ewhathow@ewhathow.com [~]# grep -i wait_timeout /etc/my.cnf
ewhathow@ewhathow.com [~]#

So on my server, I do not have it set.

Then, add or change the wait_timeout variable in my.cnf:

wait_timeout=300

The above will set the wait_timeout to 300 seconds (5 minutes).


So first decide which category your site belongs to and then choose your wait_timeout wisely. Keeping it high might not do any harm if you don’t have many idle processes. But if you get errors like too many connections, it is time to remove all those idle connections by decreasing the value of wait_timeout!

Incoming search terms:

  • mysql set time out in my ini (1)
  • mysql wait_timeout (1)

{ 0 comments… add one }

Leave a Comment