Finding and killing long running MySQL queries

Sometimes (Woocommerce?) related queries on a MySQL server can seemingly run forever and eventually lead to the server being clogged up (and few free connections).

Something like this can help …. (i.e. kill all MySQL queries which have been running longer than 900 seconds, and aren’t from the ‘root’ user).

 

#!/bin/bash

# assume e.g. ~/.my.cnf exists with user/password etc.

QUERY="SELECT id FROM information_schema.processlist
     WHERE command <> 'Sleep' 
     AND info NOT LIKE '%PROCESSLIST%'
     AND command <> 'Killed' 
     AND user != 'root' AND time > 900"

for PID in $(mysql -u root -BNe $QUERY )
do
    # Log it ... 
    LOG_QUERY="SELECT * FROM information_schema.processlist WHERE id = $PID \G"

    OUTPUT=$(mysql -u root -NBe $LOG_QUERY)
    echo "Long running query : $OUTPUT "
    
    # If on AWS RDS then : mysql -u root -e "call mysql.rds_kill($PID)"
    # otherwise, in a normal environment :
    mysql -u root -e "KILL $PID"
done

 


Posted

in

by

Comments

2 responses to “Finding and killing long running MySQL queries”

  1. Matt Parker Avatar
    Matt Parker

    Hi,

    Thanks for this. A couple of things:

    I needed to enclose $QUERY in quotes for both queries:

    for PID in $(mysql -u root -BNe “$QUERY” )

    Running this as not root user needs these permissions:

    grant execute on procedure mysql.rds_kill_query to @;
    grant process on *.* to @;

    (Note I’m using mysql.rds_kill_query, rather than mysql.rds_kill)

  2. David Goodwin Avatar

    yes, indeed [variable quoting] – that’ll teach me for taking something that works in production and trying to jam it into a blog post 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *