mysql - 自动终止 mysql 休眠进程的 Shell 脚本

Question

我们如何杀死 mysql sleep 进程例如:

+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+| 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)

这种 sleep 过程是否会像其他查询缓慢一样影响网站性能？

Answer 1

我做到了。

创建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

并将 kill_sleep.sh 设置为 cron 作业。