Mysql kill sleep进程

查询数据库当前设置的最大连接数

mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 58    |
| Threads_connected | 57    |   ###这个数值指的是打开的连接数
| Threads_created   | 3676  |
| Threads_running   | 4     |   ###这个数值指的是激活的连接数,这个数值一般远低于connected数值
+-------------------+-------+

Threads_connected 跟show processlist结果相同,表示当前连接数。准确的来说,Threads_running是代表当前并发数

这是是查询数据库当前设置的最大连接数

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

可以在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]
max_connections = 1000

查看mysql连接数:

show full processlist;

查看sleep退出时间限制:

show variables like “%timeout%”;

+-----------------------------+----------+
| Variable_name              | Value    |
+-----------------------------+----------+
| connect_timeout            | 10      | 
| delayed_insert_timeout      | 300      | 
| innodb_flush_log_at_timeout | 1        | 
| 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    | 
+-----------------------------+----------+
11 rows in set (0.00 sec)

临时生效命令(注意全局 变量和session变量的区别,下面是全局变量的修改):

set global interactive_timeout=60; flush PRIVILEGES;
set global wait_timeout=60; flush PRIVILEGES;
show global variables like '%timeout%';

永久生效命令:

编辑 /etc/my.cnf,在mysqld 下 新增 timeout参数,设置为120秒,如下:

【mysqld】
wait_timeout=120
interactive_timeout=120

注意:要同时设置interactive_timeout和wait_timeout才会生效。

编写脚本杀死sleep进程:

#!/bin/sh

user=root
passwd=mysql密码
host=localhost

while : 
do
  n=`mysqladmin processlist -u$user -p$passwd -h$host | grep -i sleep | grep '10.10.11' | wc -l`
  date=`date +%Y%m%d[%H:%M:%S]`
  echo $n >> /tmp/sleep.log

  if [ "$n" -gt 20 ]
  then
  for i in `mysqladmin processlist -uroot -p5432b400aa9c9fb0c92b543dce97c244 -hlocalhost | grep -i sleep | grep '10.10.11'  | 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
点赞