Shell Script to auto kill mysql sleep processes
How We Kill mysql sleep processes Like:
+------+-----------+-----------+------------------------+---------+------+----------------+-------------------------------------------------------------------------------------------+ | 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?
Answer
I made it.
Create kill_sleep.sh file
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 .