MySQL Connection slow problem

A few days ago to the company's developers virtual out a MySQL server, using SSH login after show databases, show tables are quick, but colleagues use the connection tool to connect data

Libraries need to wait a few seconds, with the program to connect to MySQL, may wait a half minutes or even longer, in vain to delay the developer's time.

Developers and operations are in the same local area network, speed is not a problem, the problem of the network is excluded. Virtual server, plus 2G of memory, the problem remains unresolved. Baidu a bit, found a

A workaround is to disable MySQL for DNS resolution of external connections.

VI/ETC/MY.CNF file

[Mysqld]

Skip-name-resolve

Indicates skipping reverse parsing

This occurs because the MySQL server receives a remote IP access, the default will be to check the IP reverse resolution, the reverse lookup process will be slow, if the IP is not

There is a counter-solution, and MySQL may also be stuck on this connection.

Prevents MySQL from DNS parsing of external connections, resulting in a large number of connections in MySQL that are connected to connect, affecting MySQL usage. Use this option to eliminate MySQL for DNS

The time of resolution. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!

The MySQL official web site found an explanation of how MySQL uses DNS, as follows:

When a new thread is connects to Mysqld, mysqld'll span 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 thread 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-

Resolve 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.

Link Address: http://live.dadanini.at/cds/mysql/doc/D/N/DNS.html

This article is from "Jacky Xin" blog, please be sure to keep this source http://jackyxin.blog.51cto.com/1976631/1662254

Problems with MySQL connection slow