Menu
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?
895
Run the following command: mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') FROM information_schema. processlist WHERE user <> 'system user'; This will kill all your MySQL queries.
MySQL does not have a unique command for killing all processes. To kill all processes for a specific user, use CONCAT to create a file with the list of threads and statements. In our case, we entered root as the user. To specify another user, replace root with the desired username.
Sleep meaning that thread is do nothing. Time is too large beacuse anthor thread query,but not disconnect server, default wait_timeout=28800;so you can set values smaller,eg 10. also you can kill the thread. Follow this answer to receive notifications.
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 .
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With