MySql Communication Failure and Aborted Connections

Have you got an error like this using MySql?

1. Communications link failure

2. Aborted connection

3. Last packet sent to the server was 0 ms ago.

4. Too many connections

Things you can do to figure out whats going on with mysql connections.

1. See how many connections are currently connected with this query: SHOW PROCESSLIST;

2. Check /etc/mysql/my.cnf max connection limit: max_connections = 100 #100 by default

3. Are you running out of file descriptors, check by: ulimit -a #by default its a 1000 on Ubuntu

4. Run your mysql queries and check the active sockets: netstat -na | grep 3306

5. set the file descriptor limits in /etc/security/limits.conf

When rapidly connecting and reconnecting to mysql database you may find that you run out of max connections to start with. The next thing you run out of is file descriptors because each time you connect and cleanly close the tcp socket it will wait to close the connection so that stray packets to not wonder in after you closed it. You can see if this is happening by checking ulimit -a and then watching how many are in time_wait with netstat -na while running your queries.

When you run out of file descriptors (sockets) to open up, mysql will crash and abort all the connections which could be caused on the sending and/or receiving server. MySql can handle thousands of connections at one time, although it may not be the most efficient process due to the authorization and setup cost, it gets the job done. I would recommend using connection pooling which saves time with reuse and keeping them ready to go when needed.

If you want to solve the communications failure with out pooling, raise the file descriptors limit to over 24000 or whatever number you determine suite your needs. Remember you will need alot more file descriptors than actual connections because of time_waits after a socket closes.