Slow connections to MySQL Server
I have an app that connects to a MySQL Server on a different box. Only one connection is created and it is held for the duration of the app. The minimal time it takes to connect to the database server is not that important so while it bothered me, the 2-5 seconds it took to establish that connection wasn’t troubling enough to look into until recently.
I connect directly to the server in question by specifying its IP address (no firewalls in between). Imagine my surprise when I figure out that it is actually DNS lookups causing the problem. No only that, but the DNS lookup that is slowing things down is the lookup for the client connection, not the database server.
It appears that MySQL does a reverse lookup to identify the client box. Why? Think about security in MySQL. You can give permissions to login from a box (by IP or by DNS name). For this to work, MySQL would need to attempt a reverse DNS lookup to properly establish the level of security this client would enjoy. A DNS lookup will try your primary DNS server, then your secondary DNS server, and possibly your tertiary DNS server before failing completely. This can take 3-5 seconds for all the failures before MySQL decides it doesn’t know your name.
One way to fix this is you could setup your DNS server with a reverse lookup entry for the client box so that the reverse lookup would succeed right away. The DNS query would finish quickly instead of waiting for multiple servers to fail.
There is another possible solution that you could implement if you have control of the MySQL server. You could add the following parameter when starting up mysqld:
--skip-name-resolve
This would tell MySQL NOT to lookup the name of connecting clients. The downside to this would be that you could no longer enable permissions for a DNS name (IP Only!).
I added the reverse lookup option and now mine connects so fast I can barely read the message before it moves on.
Ray Pulsipher
Owner
Computer Magic And Software Design
