Clearing a Backlog of MySQL Queries

Published: June 12, 2020

Tags:

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

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 '{ print $1; }' | xargs -n1 mysqladmin kill
  sleep 1
done

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but also dabbles in Ruby and Go. Technical topics that interest me are monitoring, security and performance.

During the day I solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I also blog about tech, work on open source and hunt for bugs.

If you'd like to get in touch with me the best way is on Twitter.