Users are connecting to MySQL server via MyODBC connector for Windows. The application seems hang when it hits the MySQL.
The issue seems complicated but easy to solve. I added the following to My.CNF on the server:
With these directives, ODBC connection is much faster now.
MySQL has built-in security to ascertain whether request from a given hostname is genuinely from its IP address, instead of from IP spoofing.
When a client request hit MySQL server, the server will first resolve the hostname to IP. With this the server will again do a reverse lookup from DNS to ensure that IP in the request packet tally with IP given by the DNS.
In our case, the new DNS does not keep the record of the hostname after DHCP issue a leased IP to the client.
Thus, MySQL will have to wait for timeout to response to the request, this makes MySQL seems slow.