现状:
睡眠连接过多,会对mysql服务器造成什么影响?
严重消耗mysql服务器资源(主要是cpu, 内存),并可能导致mysql崩溃。
原因分析:
造成睡眠连接过多的原因?
1. 使用了太多持久连接(在高并发系统中,不适合使用持久连接)
2. 程序中,没有及时关闭mysql连接
3. 数据库查询不够优化,过度耗时。
解决:
那么,如果要从根本上解决sleep连接过多,就得从以上三点反复检查,但是见效并不快。
mysql的配置文件中,有一项:
wait_timeout, 即可设置睡眠连接超时秒数,如果某个连接超时,会被mysql自然终止,多好的办法!
如在MySQL的my.ini中设置:
wait_timeout=100 #即设置mysql连接睡眠时间为100秒,任何sleep连接睡眠时间若超过100秒,将会被mysql服务自然终止。
那么,对于正在运行中的生产服务器,在不能停止服务情况下,修改此项怎么办?很简单,以root用户登录到mysql,执行:
set global interactive_timeout=30;
set global wait_timeout=100;
更根本的方法,还是从以上三点排查之:
- 在PHP程序中,不使用持久链接,即使用mysql_connect而不是pconnect。
- PHP程序执行完毕,应该显式调用mysql_close
- Java程序调整连接池(C3P0)或者调整Java服务(Tomcat有关连接池参数)
- 逐步分析系统的SQL查询,找到查询过慢的SQL,进行优化
显示连接列表
在Navicat Premium中可以看到很多处于sleep状态的连接,那怎么让MySQL自动关闭这些处理sleep状态的连接呢?
wait_timeout、interactive_timeout 默认值:
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
set global wait_timeout=30;
set global interactive_timeout=30;
注:wait_timeout要与interactive_timeout一起修改才能起效。
这种修改方式在重启mysql服务后,会失效,所以最好还是把这两个属性配置到mysql配置文件中。
附注:
利用mysql -e 参数引出的重要命令:
-- 1. 查看数据库正在执行的SQL语句,可能无法看全完整的SQL语句
show processlist;
-- 2. 查看正在执行的完整的SQL语句,完整显示
show full processlist;
-- 3. 查看配置参数,my.cnf里参数的生效情况
show variables;
-- 4. 杀掉SQL线程
kill ID;
-- 5. 查看当前会话的数据库状态信息
show session status;
-- 6. 查看整个数据库运行的状态信息,很重要,要分析并要做好监控
show global status;
-- 7. 显示innodb引擎的性能状态
show engine innodb status;