MySQL queries can sometimes be long-running and may prevent transactions from accessing necessary tables to complete a request. This will put your users on hold. In order to kill these long-running queries, you must access the environment’s MySQL database.
Correcting Long-Running Queries
When you are able to successfully make a local MySQL connection to the site’s database, run the ensuing command to present a list of accessible threads:
Show processlist;
- Then review the “Time” field to find the longest running query
- Next, run the following command to kill it:
killthread_ID;
As a side note, be sure to replace <thread_id> with the ID of the query you want to deactivate or get rid of.
Kill All Queries
If multiple bad requests are preventing valid queries from working, you can fix this by clearing them out without having to run kill on individual threads. Perform the following to produce kill commands from the PROCESSLIST table:
mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') FROM information_schema.processlist WHERE user <> 'system user'; |
The final step is to copy the provided query in the output and run as instructed. This will fix your queries problem!
SHOW PROCESSLIST Command
Another helpful MySQL command, is SHOW PROCESSLIST. This assists in keeping account of client-user transactions. SHOW PROCESSLIST lists all the open connections to the server, including: thread IDs, material on the requesting host, user and database, the query string, and the current state of the connection. The command itself is very easy to run from the MySQL client:
mysql> SHOW PROCESSLIST; |
Remember, since this command shows currently running queries, the command is only obtainable to users with the SUPER privilege (such as a MySQL root user). Several different states are likely for the connection—the MySQL manual lists them, along with explanations of each.
Also, along with viewing processes, users with the SUPER privilege can kill running threads. For example, threads which have “gone zombie” and are not replying or tying up server resources, can be resolved swiftly with the KILL command. In order to kill a thread completely, use the KILL command followed by the thread ID returned by SHOW PROCESSLIST:
mysql> KILL 27;Query OK, 0 rows affected (0.05 sec) |
Lastly, to kill the query being implemented by a thread and leave the connection active (yes, MySQL will allow for such detailed control), use the KILL QUERY command instead. Then follow by the suitable thread ID to complete the command.
