Tracking down MySQL connection leaks

Question

I have an application server (jetty 6 on a linux box) hosting 15 individuals applications (individual war's). Every 3 or 4 days I get an alert from nagios regarding the number of open TCP connections. Upon inspection, I see that the vast majority of these connections are to the MySQL server.

netstat -ntu | grep TIME_WAIT

Shows 10,000+ connections on the MySQL server from the application server (notice the state is TIME_WAIT). If I restart jetty the connections drop to almost zero.

Some interesting values from a show status:

mysql> show status;
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| Aborted_clients          | 244       |
| Aborted_connects         | 695853860 |
| Connections              | 697203154 |
| Max_used_connections     | 77        |
+--------------------------+-----------+

A "show processlist" doesn't show anything out of the ordinary (which is what I would expect since most of the connections are idle - remember the TIME_WAIT state from above).

I have a TEST env for this server but it never has any issues. It obviously doesn't get much traffic and the application server is constantly getting restarted so debugging there isn't much help. I guess I could dig into each individual app and write a load test which would hit the database code, but this would take a lot of time / hassle.

Any ideas how I could track down the application that is grabbing all these connections and never letting go?

Answer 1

The answer seems to be adding the following entries in my.cnf under [mysqld] :

wait_timeout=60
interactive_timeout=60

I found it here (all the way at the bottom): http://community.livejournal.com/mysql/82879.html

The default wait time to kill a stale connection is 22800 seconds. To verify:

mysql> show variables like 'wait_%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+

EDIT: I forgot to mention, I also added the following to my /etc/sysctl.conf:

net.ipv4.tcp_fin_timeout = 15

This is supposed to help lower the threshold the OS waits before reusing connection resources.

EDIT 2: /etc/init.d/mysql reload won't really reload your my.cnf (see the link below)