解决MySQL sleep连接太多

 

执行show processlist;后打印出大量状态为sleep的连接，可能会有两三百个， 这些都是客户端和

数据库通信后没有释放的。大量的连接会严重拖慢系统速度，阻塞新的MySQL连接建立，

甚至造成MySQL报错， 无法响应。

问题产生的根源有以下3种情况

1.使用了太多持久连接

2.程序中，没有及时关闭mysql连接

3.数据库查询不够优化，过度耗时

 

最根本的办法是从以上3种情况排查

1.程序中，不使用持久链接，即使用mysql_connect而不是pconnect

2.程序执行完毕，应该显式调用mysql_close

3.只能逐步分析系统的SQL查询，开启慢查询日志，找出低效的SQL语句，然后优化

这是从研发角度排查，如果不方便在代码方面变更， 又想解决问题，

那么就要依靠mysql本身的超时功能解决，命令行键入

mysql> show global variables like “%timeout%”;

+—————————-+———-+

| Variable_name | Value |

+—————————-+———-+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| slave_net_timeout | 3600 |

| wait_timeout | 28800 |

+—————————-+———-+

10 rows in set (0.00 sec)

通过 wait_timeout 和 interactive_timeout的值28800发现超时时间是8小时

我们可以减小wait_timeout和interactive_timeout的值，处理超时断开，比如

900秒，即15分钟

set global wait_timeout = 900;

set global interactive_timeout=900;

但是这个配置再重启后就会失效了。

我们可以在配置文件my.cnf中添加

[mysqld]

wait_timeout = 900

interactive_timeout = 900

保存退出，重启MySQL即可生效

 

另一种办法是编写shell脚本强行断开sleep连接

脚本如下：

#!/bin/sh

user=user

passwd=passwd

host=127.0.0.1

while :

do

n=`mysqladmin processlist -u$user -p$passwd -h$host | grep -i sleep | wc -l`

date=`date +%Y%m%d[%H:%M:%S]`

echo $n

if [ “$n” -gt 50 ]

then

for i in `mysqladmin processlist -u$user -p$passwd -h$host | grep -i sleep | awk ‘{print $2}’`

do mysqladmin -u$user -p$passwd -h$host kill $i

done

echo “sleep is too many ,and killed” >> /tmp/sleep.log

echo “$date : $n” >> /tmp/sleep.log

else

exit 0

fi

sleep 1

done

保存为cls_sleep.sh，结合任务计划，就可以定期清除sleep连接了。