MySQL: Not much activity, but thousands of network connections remain in TIME_WAIT"

Question

We are running MySQL 5.1.33 on FreeBSD 7.2-RELEASE.

This is a development database server attached to a development webserver running Silverstripe and PHP 5.3.

This morning, the development webserver was complaining that it could not connect to the database server. The PHP error was the "Can't connect to MySQL server on 'host.example.gov'".

We discovered that there are thousands of open files on the Database server, which is also unusual considering this server is mostly idle.

# lsof |grep /var/db/mysql/DATABASE  |wc -l
5135

There are also thousands of connections in TIME_WAIT status, which is unusual. I am expecting these TIME_WAIT connections to time out, but they haven't ended in the last 20 minutes.

# netstat -an | grep 3306 | grep -c TIME_WAIT 
2650

There don't seem to much activity happening on the database server:

# mysql -e "show full processlist;"
+------+------+-----------+------+---------+------+-------+-----------------------+
| Id   | User | Host      | db   | Command | Time | State | Info                  |
+------+------+-----------+------+---------+------+-------+-----------------------+
| 2489 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist | 
+------+------+-----------+------+---------+------+-------+-----------------------+

My question: From within MySQL, how can I tell what is holding on to these files? Maybe the open files are a red herring.

Answer 1

It turns out that this problem was caused by a faulty localhost firewall. mysql clients were able to connect initially (or make some connections), but subsequent packets were being ignored.

I'm still trying to figure out about the open-files, but that problem has gone away.