mysql数据库中sleep链接过多的问题

2015-09-14 22:11:54
mysql的sleep链接过多,最常见的原因是因为没有及时关闭数据库链接,也就是没有调用mysql_close()方法。
最近公司的web服务器经常在周末的时候发生故障,前两次都是给负责运维的同事打电话,
让他重启一下web服务,基本就可以解决问题。
而这个周末,故障又不期而至了,好像成了必然现象,事不过三,于是决定查一下具体的原因。

我们的系统是nginx+php-fpm+mysql的简单的架构, 出问题的时候,症状就是页面打开很慢,之后就出现502 bad gateway的错误。

由502 bad gateway的错误,我们知道就是nginx链接后端的php,而php程序没有及时返回,造成了超时。
那么造成php超时的原因也有多种,比如
1、逻辑异常复杂
2、调用外部资源超时,比如外部的web service、数据库等等

结合我们的业务,首先想到了mysql数据库,先查看了一下mysql数据库的状态,通过show full processlist命令发现有大量的链接处于sleep状态,下面就是其中的一条,已经sleep了半个小时了


 192.168.1.2:20318 news Sleep 1860 ---

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

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


那么知道了问题所在,就要找到是什么原因导致的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跟踪进程的系统调用,不过这些都需要root权限,

在我们运帷同事的协助下,得到了如下的信息:

下面是lsof的部分输出

COMMAND   PID   USER   FD   TYPE     DEVICE     SIZE       NODE NAME
php-fpm 15687 daemon  cwd    DIR      104,2     4096   69437193 /data/www/hutuseng.com/news/apns_news.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

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


我们的程序大致是这样的:

mysql_connect();  //建立链接

$queryBid = mysql_query("select bundleId from apps");
while($row = mysql_fetch_assoc($queryBid)) {

	//取出通知内容,连接苹果服务器,进行推送
	//这里有时候会花一二十分钟,此时mysql服务器那里的连接一直是sleep状态
}

修改的思路也很简单,我们先通过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){
	//取出通知内容,连接苹果服务器,进行推送
	//这里有时候会花一二十分钟
}