Home>

Through the intranet connection to the mysql service of another machine, I did find that the speed n was slow! Waited for tens of seconds before waiting for the password.Very anxiousIs there a way to solve the slow mysql database connection in the LAN?Here we lead everyone to solve this problem,Let's take a look with interested friends

Through the intranet connection to the mysql service of another machine, I did find that the speed n was slow! Waited for tens of seconds before waiting for the password. But ping mysql's server is fast! Thinking of having a similar experience a long time ago, Some services, such as telnet,Will do some reverse domain name resolution (if reverse resolution is not smooth, It ’s estimated that it will be delayed a lot, The feeling is that the connection speed n is slow! )

I searched the solution online, Two types were found:

1. The resolve.conf configured on both sides of the machine is different. It is ok to configure resolve.conf to a faster dns.

2. Turn off reverse DNS resolution in the mysql configuration. Also ok

I use the first method, After modifying the resolve.conf configuration, the speed is up!

Appendix 1:Excerpt from

Two machines on the same network segment, Connect to the same database server, One is 100 times faster than the other, and the database host is represented by the domain name. I don't understand why

Later, I checked the /etc/resolv.conf of the two machines and found that the configuration was different. Change the configuration of the fast machine, Speed ​​came up.

Domain name resolution can also cause network programs to be slow, Another gain. In addition, mysql has the problem of reverse DNS resolution, May also cause slow speed, Can be in the mysql configuration file, Turn off DNS reverse analysis.

Appendix 2:Excerpt from

When remotely accessing mysql, mysql resolves the domain name,Will cause slow access,Add the following configuration to solve this problem

#Disable mysql to do domain name resolution
  [mysqld]
  skip-name-resolve

The php remote connection to mysql is slow. Sometimes it takes 4-20 seconds to connect to mysql remotely. The local connection to mysql is normal.The mysql installed by default has reverse DNS resolution enabled.Add the sentence skip-name-resolve under [mysqld] in the file my.ini (under Windows) or my.cnf (Unix or Linux)

However, this will cause a problem:when connecting to mysql, you cannot use localhost to connect, but use the ip address;if you grant permissions to users by localhost,The user login permissions should also be modified.

Workaround for slow connection to mysql.

2 servers,One runs iis + php, one runs mysql, and configures the environment as usual.Test page everything ok

When running the application, I found that PHP is very slow to access mysql.This situation has never been seen before,Although the 2 servers are not on the same network segment,However, the ping value is basically between 1,2ms. TCP connection should not be a problem.Google found the answer later,Add:skip-name-resolve to the [mysqld] section of the my.ini file, save the file,Restart mysql, everything is OK, the speed is like flying

The new version of mysql is not configured like the previous fool.This problem tossed me all morning,Finally came back at night to solve it,Hey, learn something again.

mysql 5 server under windows 2003, this machine is very fast to connect to the mysql service,There are two linux machines in the local area network, and one has a fast connection.The other one has to wait for several seconds after entering the password before connecting.

Solution:

Adding the following configuration to the mysql server configuration is fast.

[mysqld]
  skip-name-resolve

Appendix:(how mysql uses dns)

when a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. this thread will first check if the hostname is in the hostname cache. if not the thread will call gethostbyaddr_r () and gethostbyname_r () to resolve the hostname .

if the operating system doesn "t support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr () and gethostbyname () instead. note that in this case no other thread can resolve other hostnames that is 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 have a very slow dns and many hosts, you can get 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.

The php remote connection to mysql is slow. Sometimes it takes 4-20 seconds to connect to mysql remotely. The local connection to mysql is normal. The main reason for this problem is thatThe mysql installed by default has reverse DNS resolution enabled.Add the sentence skip-name-resolve under [mysqld] in the file my.ini (under Windows) or my.cnf (Unix or Linux).

Mysql remote connection is slow

Connected to mysql of other machines in the local area network and found that the speed is very slow.It's unknown for what reason,There is always a few seconds of delay.

Later found a solution online,add in my.ini

 [mysqld]
  skip-name-resolve
  skip-grant-tables

It's fast!

skip-name-resolve

The option can disable DNS resolution, and the connection speed will be much faster.However, in this case, you cannot use the host name in the mysql authorization table, but only use the ip format.

If the –skip-grant-tables system is used, no access control will be performed on the access of any user.But you can use mysqladmin flush-privileges or mysqladmin reload to enable access control;By default, the show databases statement is open to all users.If the mysql server does not have a remote account,Just add skip-grant-tables to my.ini