A slow way to resolve remote connections to MySQL

The MySQL database connection to the test server that developed an application connection company was slow to open, but the local MySQL database was quickly started to think it might be caused by a network connection problem, and it was found that network traffic was normal after pinging and route. And on the MySQL machine local connection discovery is very fast, so the network problem is basically excluded, so want to see whether MySQL configuration problem. After querying the MySQL related documents and web search, we found a configuration that seems to solve the problem of adding the following configuration parameters to the MySQL configuration file:

[Mysqld]
Skip-name-resolve

Under Linux the config file is/etc/my.cnf, under Windows Config file is the My.ini file under the MySQL installation directory. Note that this configuration is added under [mysqld], after you change the configuration and save it, and then restart MySQL and connect the test remotely, everything is restored. The official explanatory information for this parameter is as follows:

How MySQL uses DNS

When a new thread connects to Mysqld, Mysqld would spawn a new thread to handle the request. This thread would first check if the hostname is in the hostname cache. If not the thread would call Gethostbyaddr_r () and Gethostbyname_r () to resolve the hostname.

If the operating system doesn ' t support the above Thread-safe calls, the thread would lock a mutex and call gethostbyaddr () and gethostbyname () instead. Note that in this case no other thread can resolve other hostnames that's not in the hostname cache until the first Threa D is ready.

You can disable DNS host lookup by starting mysqld With–skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.

If you had a very slow DNS and many hosts, you can get to more performance by either disabling DNS Lookop with–skip-name-re Solve or by increasing the Host_cache_size define (default:128) and recompile mysqld.

You can disable the hostname cache with–skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.

If you don ' t want to allow connections over TCP/IP, you can do this by starting mysqld with–skip-networking.

According to the documentation, if your MySQL host query DNS is slow or there are many client hosts will cause the connection is very slow, because our development machine is not able to connect the external network, so the DNS resolution is impossible to complete, and thus understand why the connection is so slow. Also, note that after adding this configuration parameter, the Host field in the MySQL authorization table will not be able to use the domain name and only be able to use the IP address, as this is a forbidden result of domain name resolution.

A slow way to resolve remote connections to MySQL