Limiting the amount of idle connections on your MySQL server

If you find that your MySQL fills up with idle connections as your users are not closing their connections correctly or using persistent connections, you can tune this with a few MySQL variables as follows:

In your config file /etc/my.cnf add the following lines under the [mysqld] section:

[mysqld]
#set maximum connections in total
max_connections = 200
#set max num of connections per user
max_user_connections = 10
#set connection timeout to 30 mins
set-variable = interactive_timeout=1800
set-variable = wait_timeout=1800

Here we limit the total number of current connections, connections per user and the time in which they can idle for.

With MySQL v4.0 you will need to set interactive_timeout in order to set wait_timeout to the same value.

Now restart the daemon and check the settings with the SHOW VARIABLES; MySQL query.

Last updated: 07/11/2005