MySQL too many connection 问题分析

问题缘由

线上一个网站在运行一段时间后，页面打开速度变慢随之出现502 bad gateway的错误。

问题分析

由502 bad gateway的错误，我们知道就是nginx链接后端的php，而php程序没有及时返回，造成了超时。
那么造成php超时的原因也有多种，比如：

php-fpm资源消耗光
调用外部资源超时，比如外部的web service、数据库等等
….

出现问题，我们先登录服务器查看相关日志。

结合我们的业务，首先想到了mysql数据库，先查看了一下mysql数据库的状态，通过show full processlist命令发现有大量的链接处于sleep状态。

sleep状态的意思就是说，某个客户端一直占着这个链接，但是什么事也不干，或者是客户端压根儿就已经断开了，而服务端却不知道。

我们知道，mysql的连接数是有限制的，比如默认是151个，那么当大量的链接处于sleep状态时，php程序就无法同mysql建立链接，就会发生超时现象。

那么造成sleep的原因，有三个，下面是mysql手册给出的解释：

客户端程序在退出之前没有调用mysql_close()。[写程序的疏忽，或者数据库的db类库没有自动关闭每次的连接。。。]
客户端sleep的时间在wait_timeout或interactive_timeout规定的秒内没有发出任何请求到服务器. [类似常连，类似于不完整的tcp ip协议构造，服务端一直认为客户端仍然存在（有可能客户端已经断掉了）]
客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了。 [参看：tcp ip协议的三次握手]

那么知道了问题所在，就要找到是什么原因导致的sleep线程的存在，

通过上面的信息，我们知道是 192.168.1.2这个IP的20318端口和mysql建立的链接，而192.168.1.2正是我们的web服务器，

于是ssh登录服务器，通过netstat -tunp找到端口20318所对应的进程和pid，一看就是php-fpm引起的。

下面就是要看一下这个php-fpm是调用的哪一个php文件，找到了具体的php文件就好办了。

具体可以通过lsof列出这个pid打开的文件，也可以通过strace跟踪进程的系统调用。

下面是lsof的部分输出：

# lsof -p 23018
COMMAND   PID   USER   FD   TYPE     DEVICE     SIZE       NODE NAME
php-fpm 15687 daemon  cwd    DIR      104,2     4096   69437193 /xxx/daemon.php
php-fpm 15687 daemon  rtd    DIR      104,6     4096          2 /
php-fpm 15687 daemon  txt    REG      104,5 27714205    3466635 /app/php/sbin/php-fpm

从中可以看到，是我们的daemon.php引起的，这个程序是我们向ios设备推送通知的程序，其中要跟苹果（Apple）的服务器建立链接，可能是苹果服务器不稳定,超时引起的。

程序大致流程：

mysql_connect();  //建立链接

$queryBid = mysql_query("select bundleId from apps");
while ($row = mysql_fetch_assoc($queryBid)) {
	//取出通知内容，连接苹果服务器，进行推送
	//这里有时候会花一二十分钟，此时mysql服务器那里的连接一直是sleep状态
}

mysql_close();

解决方法

方案一：修改的思路也很简单，我们先通过mysql把数据取出来，之后马上关掉mysql连接，释放mysql资源，剩下的就慢慢干好了。修改后的程序是这样的：

mysql_connect();  //建立链接

$arr_bid=array();
$queryBid = mysql_query("select bundleId from apps");
while($row = mysql_fetch_assoc($queryBid)) {
	$arr_bid[] = $row;
}
mysql_close(); //从mysql中取完数据就马上关闭连接

foreach($arr_bid as $row){
	//取出通知内容，连接苹果服务器，进行推送
	//这里有时候会花一二十分钟
}

方案二：[不治本，有弊端]
写一个定时脚本，每分钟检查下mysql连接数，超过sleep时间的自动kill掉

<?php  
define('MAX_SLEEP_TIME'， 120);  
  
$hostname = "localhost";  
$username = "root";  
$password = "password";  
  
$connect = mysql_connect($hostname， $username， $password);  
$result = mysql_query("SHOW PROCESSLIST"， $connect);  
while ($proc = mysql_fetch_assoc($result)) {  
	if ($proc["Command"] == "Sleep" && $proc["Time"] > MAX_SLEEP_TIME) {  
	@mysql_query("KILL " . $proc["Id"]， $connect);  
}  
}
mysql_close($connect);

加入到crontab定时计划里

1	* * * * * php /usr/local/sbin/kill-mysql-sleep-proc.php

方案三：[不推荐]
修改mysql配置

[mysqld]
wait_timeout=10

#或者
mysql> set global wait_timeout=10;