A defense against some MySQL connection problems

A couple of times in the last week, we’ve seen one of our MySQL database servers have an unusually high number of connections. That’s a serious issue: If there are too many connections to a MySQL server, customer scripts won’t be able to connect to a database, so we’ve spent some time looking at the cause and fixing it.

MySQL normally limits the number of connections that one user can make, preventing most problems like this. However, when this problem was happening, MySQL’s “show processlist” command showed hundreds of connections like this:

+--------+----------------------+--------------------+------+---------+------+-------+--------+
| Id     | User                 | Host               | db   | Command | Time | State | Info   |
+--------+----------------------+--------------------+------+---------+------+-------+--------+
| 269869 | unauthenticated user | 192.0.2.161:45186  | NULL | Connect | NULL | login | NULL   |
| 269870 | unauthenticated user | 192.0.2.161:45187  | NULL | Connect | NULL | login | NULL   |
| 269871 | unauthenticated user | 192.0.2.161:45189  | NULL | Connect | NULL | login | NULL   |
| 269872 | unauthenticated user | 192.0.2.161:45190  | NULL | Connect | NULL | login | NULL   |

The connections would sit in this state for a long time, even though MySQL is supposed to disconnect them within three seconds if they don’t authenticate. (The IP address has been changed for privacy; it was actually a remote connection from outside our network.)

Some troubleshooting revealed that the problem was that the remote IP address didn’t have working reverse DNS, and MySQL was trying a DNS lookup that took many seconds to time out.

To prevent this from being an issue in the future, we now use MySQL’s “–skip-name-resolve” feature, which skips the DNS lookups. That also required slightly changing the way our MySQL access permissions work: for the geeks among you, instead of just allowing connections from “localhost”, we also have to explicitly allow connections from “127.0.0.1”. We tested these changes thoroughly and they’re now live on our servers, which should dramatically reduce the possibility of a MySQL server running out of connections.

In the extremely unlikely event you notice any MySQL connection problems due to this change, don’t hesitate to let us know.