mysql 解决数据库连接变慢

最近发现网站访问速度变慢，有十几秒左右的延迟，跟踪代码发现sql查询非常快。查询mysql日志文件发现大量警告日志：

[Warning] IP address 'x,x,x,x' could not be resolved: Temporary failure in name resolution. IP是本机的外网地址

在mysql网站上找到一篇文章

http://dev.mysql.com/doc/refman/5.5/en/host-cache.html

文章主题：

8.12.6.2 DNS Lookup Optimization and the Host Cache

The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information. The server uses this cache for nonlocal TCP connections. It does not use the cache for TCP connections established using a loopback interface address ( 127.0.0.1  or  ::1 ), or for connections established using a Unix socket file, named pipe, or shared memory.

第一段讲到Mysql会在内存维护一个 host cache，这个cache不包括本机的Tcp连接，或通过Unix socket等建立的连接。看到这里感觉是本机外网IP在Host Cache 中未找到，既然本机连接不包括在Host Cache中，尝试修改数据库连接ip为127,0,0,1。再次访问网站果然速度变快很明显。因为数据库和

程序在同一服务器上所有这里可以用127.0.0.1。接着往下看

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.

The server performs host name resolution using the thread-safe gethostbyaddr_r() and gethostbyname_r()calls if the operating system supports them. Otherwise, the thread performing the lookup locks a mutex and callsgethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve host names that are not in the host cache until the thread holding the mutex lock releases it.

大致意思客户端host过来后首选会检测host cache中有没有，否则就要通过gethostbyaddr_r() 和ethostbyname_r()来解析主机，这两个方法都是阻塞的，有互斥锁。

The server uses the host cache for several purposes:

• By caching the results of IP-to-host name lookups, the server avoids doing a DNS lookup for each client connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that host.

• The cache contains information about errors that occur during the connection process. Some errors are considered “blocking.” If too many of these occur successively from a given host without a successful connection, the server blocks further connections from that host. The max_connect_errors system variable determines the number of permitted errors before blocking occurs. See Section B.5.2.6, “Host 'host_name' is blocked”.

这么做的目的是为了维护一个IP-主机的表，避免每次客户端进行DNS解析，这个cache包含了数据库连接执行过程中的一些错误信息，这些错误信息被认为是阻塞的，会阻塞后续的这个host的数据库连接，当错误达到系统变量max_connect_errors所设置的值时，就会发生阻塞




看到这里就不难明白为什么网站会变慢了

找到了原因，后面就不贴原文了，下面是讲的解决方法

1.可以通过FLUSH HOSTS命令或 mysqladmin flush-hosts command来解锁

2.启动mysql时添加--skip-host-cache参数选项，关闭host cache，默认是开启的。

 添加--skip-name-resolve 启动选项，关闭DNS解析，这样的话用户只能通过IP地址来连接数据库，分配权限时要注意，以前grant的权限也要进行修改。