很多人有疑问为什么会有这么多sleep进程?
答:mysql默认wait_timeout=28800 也就是8小时,wait_timeout过大有弊端,其体现就是MySQL里大量的SLEEP进程无法及时释放,拖累系统性能。不过也不能把这个指设置的过小,否则你可能会遭遇到“MySQL server has gone away”之类的问题。
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 20 ]
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 I killed it " >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi
sleep 1
done