≡ Menu

MySQL List Idle Connections

Question – How do I list idle connections in mysql? I sometimes see a too many connections error. I need to terminate idle connections automatically. How do I do that?

You can list all processes that MySQL is currently running using the following command:

First open a mysql command prompt:

mysql -u dbusername -p

Then when you get the mysql command prompt:

SHOW FULL PROCESSLIST;

This will output something like the following:

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                                                                                      

The above command shows that there are three idle processes (sleep) and the time since last activity. The first process has been idle for 169 seconds!

You can manually kill the processes using the Id column in the above output:

kill 398780

Another solution is to use the wait_timeout variable in your my.cnf file. The default wait_timeout is 28800 seconds (8 hours). You may want to reduce it to 180 seconds (3 minutes). This means that if a connection is idle for more than 3 minutes, it will be terminated.

mysql>
mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

So add the following to your my.cnf file. First, locate your my.cnf file:

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

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

Then, add or update the wait_timeout in your my.cnf file:

wait_timeout=180

Note that if you have processes running for a long time which hold on to the connections, this will cause them to break! You will now need to continuously check if the connections are open (and not automatically closed) before running the SQL queries. But reducing the wait_timeout will definitely reduce the number of idle connections and so improve the database performance of MySQL. It will also fix the too many connections error that you are getting.

Comments on this entry are closed.