MySQL server running out of connections w/o reason

[Marcel de Boer]

Hello!

I'm having a problem on one of our MySQL servers (a replication slave) and I'm looking for information on how to debug this problem and fix it and maybe work around it.

The setup: for fast response times, we have three MySQL servers on different locations in the world in a replicated setup. All servers are running version 5.0.27 on RHEL 4 or CentOS 4.5. The replication master and one of the slaves are working without any trouble, but on the other slave, after a random amount of time, the incoming connections from my network management machine (basic connect/disconnect tests to see if MySQL is still working) start locking up, causing the MySQL server to run out of connections.

Extra information I have found so far (xxx.xxx.xxx.xxx is my network management machine, yyy.yyy.yyy.yyy is the problematic MySQL slave):

- Show processlist, taken when the sessions were locking up, but we had not yet run out of connections (once we do run out, I can't login anymore to view the processlist). At the <...>, there are about 100 more entries:

mysql> show processlist;
+-------+----------------------+-----------------------+------+---------+---------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------------+-----------------------+------+---------+---------+-----------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 1058194 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 210 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 12120 | unauthenticated user | xxx.xxx.xxx.xxx:54072 | NULL | Connect | NULL | login | NULL |
| 12121 | unauthenticated user | xxx.xxx.xxx.xxx:54183 | NULL | Connect | NULL | login | NULL |
| 12132 | unauthenticated user | xxx.xxx.xxx.xxx:54284 | NULL | Connect | NULL | login | NULL |
<...>

- netstat -pn, showing that the connections are hanging in CLOSE_WAIT (i.e. the MySQL server isn't releasing the connection)

root@jester mysql]# netstat -p -n | grep 3306
tcp 1 0 yyy.yyy.yyy.yyy:3306 xxx.xxx.xxx.xxx:59504 CLOSE_WAIT 921/mysqld
tcp 1 0 yyy.yyy.yyy.yyy:3306 xxx.xxx.xxx.xxx:54395 CLOSE_WAIT 921/mysqld
<...>

The PID here appears to always be the lowest numbered process from the active MySQLd processes, not the master process. If I 'kill -9' this PID, everything is cleared up and I can connect again (no need to restart mysqld).

- I appear to have lost the tcpdump traces of the network connections; I'll see if I can take them again. They showed that MySQLd was no longer replying to the TCP FIN sent by the network management machine.

The problem is that this happens randomly, so if I don't see it happening in time, I can't get any data from MySQL anymore.

Gtnx
Marcel