How to Kill All MySQL Sleeping Processes
for i in `mysql -e "show processlist" | awk '/Sleep/ {print $1}'` ; do mysql -e "KILL $i;"; done
Having a lot of MySQL sleeping processes can cause a huge spike in your CPU load.
Here is a simple one-line command which would kill all of the current sleeping MySQL processes:
for i in `mysql -e "show processlist" | awk '/Sleep/ {print $1}'` ; do mysql -e "KILL $i;"; done
Note that this is a temporary solution, I would highly recommend investigating and fixing the root cause of the problem. For example, you could configure the wait_timeout
variable to a specific time that MySQL should wait before closing idle connections.
Hope that it helps.
Here is what the above code is doing:
1. List all mysql process IDs
2. Filter out the ID's that are sleeping
3. Kill the sleeping processes
You can use the following command to find out the process ID's of the mysql queries that are running:
mysql -