用于自动终止 mysql 睡眠进程的 Shell 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20048017/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Shell Script to auto kill mysql sleep processes
提问by Vishal Kamal
How We Kill mysql sleep processes Like:
我们如何杀死 mysql 睡眠进程，例如：
+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+ | 2477 | stageuser | localhost | jj_production_11102013 | Query | 0 | end | SELECT * FROM wp_comments WHERE blog_id = 1071 ORDER BY comment_date_gmt DESC LIMIT 0, 50 | | 3050 | stageuser | localhost | jj_production_11102013 | Query | 0 | Sorting result | SELECT * FROM wp_comments WHERE blog_id = 1071 ORDER BY comment_date_gmt DESC LIMIT 0, 50 | | 3052 | stageuser | localhost | jj_production_11102013 | Sleep | 336 | | NULL | | 3056 | stageuser | localhost | NULL | Query | 0 | NULL | show processlist | | 3057 | stageuser | localhost | jj_production_11102013 | Sleep | 301 | | NULL | | 3058 | stageuser | localhost | jj_production_11102013 | Sleep | 299 | | NULL | | 3059 | stageuser | localhost | jj_production_11102013 | Sleep | 298 | | NULL | | 3061 | stageuser | localhost | jj_production_11102013 | Sleep | 273 | | NULL | | 3068 | stageuser | localhost | jj_production_11102013 | Sleep | 251 | | NULL | | 3072 | stageuser | localhost | jj_production_11102013 | Sleep | 233 | | NULL | | 3111 | stageuser | localhost | jj_production_11102013 | Sleep | 1 | | NULL | +------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec)
Is this sleep processes affect site performance like slow other queries?
此睡眠过程是否会像其他查询缓慢一样影响站点性能？
采纳答案by Vishal Kamal
I made it.
我做到了。
Create kill_sleep.sh file
创建 kill_sleep.sh 文件
mysql -u<user> -p<password> -h<host> -e "select concat('KILL ',id,';') into outfile '/tmp/sleep_processes.txt' from information_schema.processlist where Command = 'Sleep'"
mysql -u<user> -p<password> -h<host> -e "source /tmp/sleep_processes.txt;"
rm -rf /tmp/sleep_processes.txt
And set kill_sleep.sh to cron job .
并将 kill_sleep.sh 设置为 cron job 。
回答by Ben Dowling
Vishal's answer works well if you're running the command on the MySQL server, but it won't work if you're connecting to the server remotely or if you don't have permission to run SOURCE or SELECT ... INTO OUTFILE(eg. Amazon's RDS). It's possible to rewrite it not to rely on those features though, and then it'll work anywhere:
如果您在 MySQL 服务器上运行命令，Vishal 的答案很有效，但如果您远程连接到服务器或者您无权运行 SOURCE 或SELECT ... INTO OUTFILE（例如 Amazon 的 RDS），则它不起作用。可以重写它而不依赖这些功能，然后它可以在任何地方工作：
mysql -h<host> -u<user> -p -e "SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE Command = 'Sleep'" > sleep.txt
cat sleep.txt | xargs -I% mysql -h<host> -u<user> -p -e "%"
回答by Jhanvi
The syntax is:
语法是：
KILL thread_id
In your case:
在你的情况下：
mysql > KILL 3057
But in order to delete all the sleep processes,one command cant be used, you need to loop through whole processlist,after taking all the processes in tmp table and looping through it:
但是为了删除所有的sleep进程，一个命令不能用，你需要遍历整个processlist，在取出tmp表中的所有进程并遍历它之后：
select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';
select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep' into outfile '/tmp/a.txt';
Referred from here
从这里引用
回答by John Peterson
Percona Tools:
Percona 工具：
pt-kill --match-command Sleep --idle-time 100 --victims all --interval 30 --kill
This will find all connections that are "Sleep" state and idle for 100 seconds or more and kill them. --interval 30 will make it keep do this every 30 seconds. So you can open a screen -S ptkill then in that screen run the above command, then ctrl-A, D to detach and exit the terminal and it will just keep running cleaning up your connections.
这将找到所有处于“睡眠”状态且空闲 100 秒或更长时间的连接并杀死它们。--interval 30 将使其每 30 秒保持一次。所以你可以打开一个屏幕 -S ptkill 然后在那个屏幕上运行上面的命令，然后 ctrl-A, D 分离并退出终端，它会继续运行清理你的连接。
https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html
https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html
