Clearing a Backlog of MySQL Queries

Published: June 12, 2020

Tags:

MySQL

WARNING Proceed with caution

Sometimes, for one reason or another, MySQL may get in a state where it has a massive backlog of queries to process. In these types of situations, your application will likely be experiencing major performance issues. Additionally MySQL (and your application) will likely struggle to regain stability without human intervention. While restarting the MySQL process could be an option, that comes with a lot of risk. Another option, is to selectively kill certain queries.

In these cases there may be hundreds of active queries making it infeasible to kill each query one by one.

Here’s an example bash command that could be used to clear a large backlog of queries:

In this example there's a large backup of SELECT queries against a table named catalog_product_flat_1

mysql -e "SHOW FULL PROCESSLIST" | grep 'SELECT' | grep 'catalog_product_flat_1' | awk '{ print $1; }' | xargs -n1 mysqladmin kill

Additionally, we can use an if in the awk command to only kill queries that have been running more than a specific amount of time. In the updated example we only kill queries that have been running at least 30 seconds.

mysql -e "SHOW FULL PROCESSLIST" | grep 'SELECT' | grep 'catalog_product_flat_1' | awk '{ if ($6 >= 30) print $1; }' | xargs -n1 mysqladmin kill

If the backlog is big enough that it’s preventing you from establishing a connection you can retry the command in a loop.

while true; do
  mysql -e "SHOW FULL PROCESSLIST" | grep 'SELECT' | grep 'catalog_product_flat_1' | awk '{ if ($6 >= 30) print $1; }' | xargs -n1 mysqladmin kill
  sleep 1
done

In certain cases you may need to run this loop continually to enable site stability while troubleshooting an issue.