MySQL Name Resolve引发的数据库连接瓶颈

问题现象

Atlassian Confluence系统页面加载速度慢,包括打开页面、展开分支和确认提交,卡顿有时候会持续1-2分钟。

系统CPU空闲率高,内存使用量低,网络连接无延时。

查看catalina.out输入,无明显异常。

查看mysql会话列表

mysql> show processlist;
+--------+----------------------+---------------------+--------------+---------+------+-------+--------------
| Id     | User                 | Host                | db           | Command | Time | State | Info         
+--------+----------------------+---------------------+--------------+---------+------+-------+--------------
| 173513 | unauthenticated user | 192.168.30.29:41880 | NULL         | Connect | NULL | login | NULL         
| 173514 | unauthenticated user | 192.168.30.29:41881 | NULL         | Connect | NULL | login | NULL         
| 173515 | unauthenticated user | 192.168.30.29:41882 | NULL         | Connect | NULL | login | NULL         
+--------+----------------------+---------------------+--------------+---------+------+-------+--------------
28 rows in set (0.00 sec)

mysql> show variables like '%skip_name_resolve%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+
1 row in set (0.00 sec)

查看mysqld.log,发现以下异常:

2016-06-07 17:50:43 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution
2016-06-07 17:50:43 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution
2016-06-07 17:51:23 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution
2016-06-07 17:51:23 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution
2016-06-07 17:51:23 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution
2016-06-07 17:51:43 1799 [Warning] IP address '192.168.30.29' could not be resolved: Temporary failure in name resolution

该警告信息,在37天内,出现的条目多大9万多

[root@jira log]# grep -c  "Temporary failure in name resolution"  mysqld.log
95621

故障排除

一般的,name resolution failure错误的原因是,Mysql配置参数中,--skip-name-reslove默认值为OFF。在此情况下,对于每一个建立的连接,MySQL都会尝试去DNS查找匹配的FQDN条目,进行反解析,以验证连接请求来源的可靠性。

MySQL的这个安全机制,主要用于防止DoS攻击等非授权访问。但是,大部分的真实场景,MySQL都是内部连接,且内网不部署DNS服务,导致MySQL浪费大量时间在寻找FQDN条目,直至超时放弃。

关闭name reslove的步骤,直接编辑my.conf,加入以下行

--skip-name-reslove

启用该参数后,所有的MySQL连接串,都不能再使用主机名连接,包括localhost也不行。

以下是MySQL官方文档,对该问题的解释:

To disable DNS host name lookups, start the server with the --skip-name-resolve option. In this case, the server uses only IP addresses and not host names to match connecting hosts to rows in the MySQL grant tables. Only accounts specified in those tables using IP addresses can be used. (Be sure that an account exists that specifies an IP address or you may not be able to connect.)