注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

阿飘的博客

十里平湖霜满天 寸寸青丝愁华年

 
 
 

日志

 
 

对付 MySQL 的死连接,Sleep的进程的来源探究  

2011-07-22 10:16:34|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
当前的连接数:
mysql > show status like '%Threads_connected%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 27 |
+-------------------+-------+
1 row in set (0.00 sec)

最大连接数:
show variables like '%max_connections%';
set GLOBAL max_connections=800;
flush privileges
也可以修改/etc/my.cnf中的max_connections:
max_connections = 1000

关于php应该在何时调用mysql _close()以及pconnect方式 和传统方式有何种区别收藏
以前我一直认为,当php的页面执行结束时,会自动释放掉一切。相信很多人都跟我想的一样。但事实证明并不是这样。比如session就不会随着页面执行完毕而释放。
php的垃圾回收机制,其实只针对于php本身。对于mysql ,php没权利去自动去释放它的东西。如果你在页面执行完毕前不调用mysql _close(),那么mysql 那边是不会关闭这个连接的。如果你是用的是pconnect方式,即使你在页面执行完毕前调用mysql _close(),也无法另mysql 关闭这个连接。
也许在负载低的情况下,你感受不到有何不妥。但是,一旦负载很高,就回出现很多的死链接 ,于是得杀掉它们,现象:
在php中使用pconnect方式建立连接,然后到mysql 客户端下执行show processlist;如果你的负载到一定程度的话,你可以看到很多sleep的进程,这些进程就是人们常说的死连接,它们会一直保持sleep,直到my.cnf里面设置的wait_timeout这个参数值的时间到了,mysql 才会自己 杀死它。在杀死它的时候,mysql 还会在error-log里面记录一条Aborted connection xxx to db: 'xxx' user: 'xxx' host: 'xxx'的日志,用google 翻译一下,会得到一个相当强悍的解释"胎死腹中的连接"!
那么造成sleep的原因,有三个,下面是mysql 手册给出的解释:
1.客户端程序在退出之前没有调用mysql _close().[写程序的疏忽,或者数据库 的db类库没有自动关闭每次的连接。。。]
2.客户端sleep的时间在wait_timeout或interactive_timeout规定的秒内没有发出仁何请求到服务器. [类似常连,类似于不完整的tcp ip协议构造,服务端一直认为客户端仍然存在(有可能客户端已经断掉了)]
3.客户端程序在结束之前向服务器发送了请求还没得到返回结果就结束掉了. [参看:tcp ip协议的三次握手]




网上有一个哥们写了一个,如下:




将它当中的 $password 改成你实际的数据库 密码,死连接的时间也可以修改。然后加入计划 仁务就可以了。比如用 crontab -e 命令加入:

*/2 * * * * php /usr/local/sbin/kill-mysql -sleep-proc.php就可以每隔 2 分钟检捕孝清除一次数据库 中的死连接了。


我结合自己的实际改写如下:



crontab 加入:


*/2 * * * * /usr/local/php/bin/php /usr/local/tads/htdocs/*/src/crontable/killmysqlsleepproc.php


听说可以做mysql 的设置也可以的如下:


配置MYSQL 里的参数。超时时间设置。
max_connections:
允许的同时客户的数量。负载过大时,你将经常看到 too many connections 错误。已达到最大链接数,所以会出现这种情况。 我们服务器数值是200。
wait_timeout
服务器在关闭连接之前在一个连接上等待行动的秒数,默认数值是,即如果没有事情发升,服务器在 8个小时后关闭连接。防止sleep过而导致出现too many connections


如果你的sleep进程数在同一时间内过多,再加上其他状态的连接,总数超过了max_connection的值,那mysql 除了root用户外,就无法再继续处理仁何请求无法与仁何请求建立连接或者直接down了。所以,这个问题 在大负载的情况下还是相当严重的。如果发现你的mysql 有很多死连接存在,首先要先检查你的程序是否使用的是pconnect的方式,其次,检查在页面执行完毕前是否及时调用了mysql _close(),


还有一个办法,你可以在my.cnf里面加上wait_timeout和interactive_timeout,把他们的值设的小一些 ,默认情况下wait_timeout的值是8小时的时间,你可以改成1个小时,或半个小时。这样mysql 会更快的杀死死连接。防止连接总数超过max_connection的值。或者把max_connection的值设置的更大,不过这样显然不妥,连接的数量越多,对你服务器的压力越大。实际上那些连接都是冗余的,把它们尽快杀死才是上策。




以前总是说,在使用php连接mysql 的时候,尽量不要使用pconnect的方式,看完我上面所说的那些,应该可以明白为什么了吧,因为我们使用php大多数情况下都是做web 开发,web开发是面向多用户,那么用户的数量与mysql 连接数是成正比的。使用pconnect的方式,即使你的调用mysql _close()也是无法释放数据库 连接的,那么mysql 中的死连接的数量就会越来越多了。



我认为,只有当你的应用 属于那种点对点方式,或者你能保证连接数量很少的情况,才有必要去采用pconnect的方式,因为连接数量少,那么让它一直处于连接状态,避免了重复打开关闭的过程。这样可能会比传统方式更好一些。



至于何时该去调用mysql _close(),最正确的做法是如果下面不再执行mysql 的操作了,在你上一次执行完mysql 操作后,立刻就调用mysql _close()。这才是最正确的做法,并不是总要把mysql _close()写在页面最后一行就可以了。

如果你没有修改过MySQL 的配置,缺省情况下,wait_timeout的初始值是。

wait_timeout过大有弊端,其体现就是MySQL 里大量的SLEEP进程无法及时释放,拖累系统性能,不过也不能把这个指设置的过小,否则你可能会遭遇到“MySQL has gone away”之类的问题 ,通常来说,我觉得把wait_timeout设置为10是个不错的选择,但某些情况下可能也会出问题 ,比如说有一个CRON脚本,其中两次SQL查询的间隔时间大于10秒的话,那么这个设置就有问题 了(当然,这也不是不能解决的问题 ,你可以在程序里时不时mysql _ping一下,以便服务器知道你还活着,重新计算wait_timeout时间): # vi /etc/my.cnf

[mysqld]

wait_timeout=10

# /etc/init.d/mysql restart
复制代码不过这个方法 太升硬了,线上服务重启无论如何 都应该尽可能避免,看看如何在MySQL 命令行里通过SET来设置: mysql > set global wait_timeout=10;

mysql > show global variables like '%timeout';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| wait_timeout | 10 |

+----------------------------+-------+
复 制代码这里一个容易把人搞蒙的地方是如果查询时使用的是show variables的话,会发现设置好像并没有升效,这是因为单纯使用show variables的话就同等于使用的是show session variables,查询的是会话变量,只有使用show global variables,查询的才是全局变量。

网络 上很多人都抱怨说他们set global之后使用show variables查询没有发现改变,原因就在于混淆了会话变量和全局变量,如果仅仅想修改会话变量的话,可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。

另一个值得注意的是会话变量wait_timeout初始化的问题 ,这一点在手策腼已经明确指出了,我就直接拷贝了:
On thread startup the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql _real_connect()).

MySQL 大拿Jeremy Zawodny曾在他的文章Fixing Poor MySQL Default Configuration Values里面列出了几个很恶心的MySQL 缺省设置,不过没包含wait_timeout,但我觉得它也应该算一个,每次新装MySQL 后最好都记得修改它。

以上的修改配置来源网页


mysql >show variables like '%timeout';
打印结果如下:
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| interactive_timeout | |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | |
+----------------------------+-------+

interactive_timeout 需在mysql _connect()设置CLIENT_INTERACTIVE选项后起作用,并被赋值为wait_timeout;
mysql >set wait_timeout = 10; 对当前交互链接有效
mysql >set interactive_timeout = 10; 对后续起的交互链接有效;

该超时时间单位是秒,从变量从上次SQL执行后算起;当前空闲若超过该时间,则也会被强制断开。


想把mysql 的连接断开时间改长一些,以前只改了connect_timeout变量的值,还不够。现在又改了这两个,不知够不够。不够再继续查吧。

注意:对两个值都做修改才升效:set interactive_timeout=120; set wait_timeout=120;



mysql > show variables like '%timeout';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| interactive_timeout | |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | |
+-------------------------+-------+


mysql > set interactive_timeout=120; set wait_timeout=120;
Query OK 0 rows affected (0.00 sec)

mysql > show variables like '%timeout';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| interactive_timeout | 120 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 120 |
+-------------------------+-------+

修改全局变量:
set global interactive_timeout=120;set global wait_timeout=120;


mysql > show global variables like '%timeout';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| interactive_timeout | 120 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 120 |
+-------------------------+-------+

特别注意全局和一般变量时不一样的两个变量,这也就是为何导致修改没有起作用的原因!!!!



配置修改:
直接的修改 /etc/my.cnf这个文件中

-------------------------------------------
[mysqld]

wait_timeout =
interactive_timeout =
--------------------------------------------
添加这两行,然后重新启动mysql 服务就OK了
文章来源:

近一段时间,部门同事反映在使用mysql 的过程出现数据库 连接问题

应用程序和数据库 建立连接,如果超过8小时应用程序不去访问数据库 数据库 就断掉连接 。这时再次访问就会抛出异常,如下所示:

java.io.EOFException
at com.mysql .jdbc.MysqlIO.readFully(MysqlIO.java:1913)
at com.mysql .jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2304)
at com.mysql .jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2803)
at com.mysql .jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
...

查了一下发现应用程序和mysql数据库 建立连接,如果超过8小时应用程序不去访问数据库 数据库 就断掉连接 。这时再次访问就会抛出异常。

关于mysql 自动断开的问题 研究结果如下,在mysql 中有相关参数设定,当数据库 连接空闲一定时间后,服务器就会断开等待超时的连接:
1、相关参数,红色部分
mysql > show variables like '%timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | |
+--------------------------+-------+
同一时间,这两个参数只有一个起作用。到底是哪个参数起作用,和用户连接时指定的连接参数相关,缺省情况下是使用wait_timeout。我建议是将这两个参数都修改,以免引起不必要的麻烦。


2、修改参数
这两个参数的默认值是8小时(60*60*8=)。我测试过将这两个参数改为0,结果出人意料,系统自动将这个值设置为1。换句话说,不能将该值设置为永久。
将这2个参数设置为24小时(60*60*24=)即可。
set interactive_timeout=;
set wait_timeout=;

也可以修改my.cof修改后重起mysql
打开/etc/my.cnf在属性组mysqld下面添加参数如下:
[mysqld]
interactive_timeout=
wait_timeout=

如果一段时间内没有数据库 访问则mysql 自身将切断连接,之后访问java访问连接池时对数据库 的数据通道早就关闭了,因为dbcp连接池无法时时维护与数据库 的连接关系,mysql 5以后即使在dbcp配置中加入autoReconnect=true也没有效果。


言归正传,接着来,shell脚本实现如下:

#!/bin/sh
注:这个脚本运行后会每五秒去检测一次 mysql 的sleep进程数


while :
do
n=`/usr/bin/mysqladmin processlist | grep -i sleep | wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $n

if [ "$n" -gt 10 ]
then
for i in `/usr/bin/mysqladmin processlist | grep -i sleep | awk '{print $2}'`
do
/usr/bin/mysqladmin kill $i
done
echo "sleep is too many i killed it" >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi
sleep 5
done


crontab实现:不会循环的脚本(配合crontab使用)


#!/bin/sh
n=`/usr/bin/mysqladmin processlist | grep -i sleep | wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $n
if [ "$n" -gt 10 ]
then
for i in `/usr/bin/mysqladmin processlist | grep -i sleep | awk '{print $2}'`
do
/usr/bin/mysqladmin kill $i
done
echo "sleep is too many i killed it" >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi


你可能还会用到查询mysql 当前连接数:
1.show status
Threads_connected 当前的连接数
Connections 试图连接到(不管是否成功)MySQL 服务器的连接数。
Max_used_connections 服务器启动后已经同时使用的连接的最大数量。
2.set GLOBAL max_connections=连接数;
flush privileges
3.修改/etc/my.cnf中的max_connections
4.show processlist 显示当前正在执行的mysql 连接
5.mysqladmin -u -p -h status
显示当前mysql 状态
Uptime: Threads: 1 Questions: 22 Slow queries: 0 Opens: 16 Flush tables: 1 Open tables: 1 Queries per second avg: 0.1
mysqladmin -u -p -h extended-status
显示mysql 的其他状态
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1152 |
| Bytes_sent | |
| Com ......


参看:





补充:


netstat -n | awk '/^tcp/ {++state[$NF]} END {for(key in state) print key"\t"state[key]}'


会得到类似下面的结果,具体数字会有所不同:

LAST_ACK 1
SYN_RECV 14
ESTABLISHED 79
FIN_WAIT1 28
FIN_WAIT2 3
CLOSING 5
TIME_WAIT 1669

状态:形容
CLOSED:无连接是活动的或正在进行
LISTEN:服务器在等待进入呼叫
SYN_RECV:一个连接请求已经到达,等待确认
SYN_SENT:应用已经开始,打开一个连接
ESTABLISHED:正常数据传输状态
FIN_WAIT1:应用说它已经完成
FIN_WAIT2:另一边已同意释放
ITMED_WAIT:等待所有分组死掉
CLOSING:两边同时尝试关闭
TIME_WAIT:另一边已初始化一个释放
LAST_ACK:等待所有分组死掉

也就是说,这条命令可以把当前系统的网络连接状态分类汇总。

下面解释一下为啥要这样写:

一个简单的管道符连接了netstat和awk命令。

------------------------------------------------------------------

先来看看netstat:

netstat -n

Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 123.123.123.123:80 234.234.234.234: TIME_WAIT

你实际执行这条命令的时候,可能会得到成千上万条类似上面的记录,不过我们就拿其中的一条就足够了。

------------------------------------------------------------------

再来看看awk:

/^tcp/
滤出tcp开头的记录,屏蔽udp socket等兽关记录。

state[]
相当于定义了一个名叫state的数组

NF
表示记录的字段数,如上所示的记录,NF等于6

$NF
表示某个字段的值,如上所示的记录,$NF也就是$6,表示第6个字段的值,也就是TIME_WAIT

state[$NF]
表示数组元素的值,如上所示的记录,就是state[TIME_WAIT]状态的连接数

++state[$NF]
表示把某个数加一,如上所示的记录,就是把state[TIME_WAIT]状态的连接数加一

END
表示在最后阶段要执行的命令

for(key in state)
遍历数组

print key"\t"state[key]
打印数组的键和值,中间用\t制表符分割,美化一下。

如发现系统存在大量TIME_WAIT状态的连接,通过调整内核参数解决,
vim /etc/sysctl.conf
编辑文件,加入以下内容:
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
然后执行 /sbin/sysctl -p 让参数升效。

net.ipv4.tcp_syncookies = 1 表示开启SYN Cookies。当出现SYN等待队列溢出时,启用cookies来处理,可防范少量SYN攻击,默认为0,表示关闭;
net.ipv4.tcp_tw_reuse = 1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接,默认为0,表示关闭;
net.ipv4.tcp_tw_recycle = 1 表示开启TCP连接中TIME-WAIT sockets的快速回收,默认为0,表示关闭。
net.ipv4.tcp_fin_timeout 修改系統默认的 TIMEOUT 时间

下面附上TIME_WAIT状态的意义:

客户端与服务器端建立TCP/IP连接后关闭SOCKET后,服务器端连接的端口
状态为TIME_WAIT

是不是所有执行主动关闭的socket都会进入TIME_WAIT状态呢?
有没有什么情况使主动关闭的socket直接进入CLOSED状态呢?

主动关闭的一方在发送最后一个 ack 后
就会进入 TIME_WAIT 状态 停留2MSL(max segment lifetime)时间
这个是TCP/IP必不可少的,也就是“解决”不了的。

也就是TCP/IP设计 者本来是这么设计的
主要有两个原因
1。防止上一次连接中的包,迷路后重新出现,影响新连接
(经过2MSL,上一次连接中所有的重复包都会消失)
2。可靠的关闭TCP连接
在主动关闭方发送的最后一个 ack(fin) ,有可能丢失,这时被动方会重新发
  评论这张
 
阅读(875)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017