A MySQL problem or something else?

A MySQL problem or something else?: "Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.



The Problem



You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state, probably “Sleeping”. You speak with the lead developer and they state that there have not been any code changes for 1 month. It’s an odd problem, when you see it; however, the solution is not that obscure when you think about how MySQL uses DNS.



The Solution



When you restart MySQL with “skip-name-resolve” enabled you might think that all of your problems are gone based on the issue describe above. Well, just make sure that all of the hosts in the mysql.user table are IPs or IP ranges and not FQDN.



Not only will enabling 'skip-name-resolve” save time and money if DNS goes down, it is also good for performance. Here are some quick tests to illustrate the performance gains:



-- Performance Benefit



Without “skip-name-resolve” enabled



shell> date; mysql -u root -h 192.168.0.1 ; date

Mon Oct 5 15:20:40 PDT 2009

ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server

Mon Oct 5 15:20:43 PDT 2009



Note the time change is 3 seconds



With “skip-name-resolve” enabled:



shell> date; mysql -u root -h 192.168.0.1 ; date

Mon Oct 5 15:22:01 PDT 2009

ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server

Mon Oct 5 15:22:01 PDT 2009



Note that the server comes back immediately with the same error



Best Practice

I always make sure the “skip-name-resolve” is in any MySQL configuration file (my.cnf) in all of my installations.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"