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

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.

Max Chadwick Hi, I'm Max!

I'm a software developer who mainly works in PHP, but loves dabbling in other languages like Go and Ruby. Technical topics that interest me are monitoring, security and performance. I'm also a stickler for good documentation and clear technical writing.

During the day I lead a team of developers and solve challenging technical problems at Something Digital where I mainly work with the Magento platform. I've also spoken at a number of events.

In my spare time I blog about tech, work on open source and participate in bug bounty programs.

If you'd like to get in contact, you can find me on Twitter and LinkedIn.