How to kill RDS Mysql Processes or queries

Ajith K Ranatungaon

Some times you may come up with situations where you need to kill certain AWS RDS Mysql processes. For example, if certain process hangs up; may be due to dead-lock, then other processes keep waiting till this process to complete. This may lead to create large number of mysql connections and ultimately lead to memory issues. In such situations, even this is not a best practice, you may need to kill RDS Mysql processes by fource. This article explains how to kill RDS Mysql process connecting to the RDS db.

Let’s look at how you can get the list of current processes and kill any process manually.

  1. See the list of current processes.
    You should have access to RDS Mysql. Login to Mysql and run below command
    show processlist;
    You will see the list of processes with time, state, info and some other fields. Which process to be killed can be decided by looking at those information.
  2. Note down the process ID which should be killed.
    Once you point out the process which should be killed, take a note the process ID (the first column in the output of above command)
  3. Kill the process
    Use below command to kill the process using the ID
    CALL mysql.rds_kill(process_id);
    ex: CALL mysql.rds_kill( 171537943);

This is it about killing a certain process. But you may come up with situations where you need to kill all the processes belongs to certain criteria. Using below query, you can get a list of execute ready queries specifying the criteria.
select concat('CALL mysql.rds_kill( ',id,');') from information_schema.processlist where [your criteria];
ex:
select concat('CALL mysql.rds_kill( ',id,');') from information_schema.processlist where User="db_user" and Time > 10000;
you may copy the output (list of queries) and run them right away.

In some other cases, you may need to automate all above processes. You may do it using a shell script running on your web server. Below script will get the list of processes matching the criteria you set; then kill those processes

mysql -uUSER -pPASS -h HOST -e 'MYSQL_QUERY' | grep Query | awk '{print $1}' | while read LINE;
do mysql -uUSER -pPASS -h HOST -e "kill $LINE";
done
Ex:
mysql -uUSER -pPASS -h HOST -e 'SELECT * FROM information_schema.processlist where USER="USER" and Info LIKE "%SELECT r.*, -(sum(r.quantity))%";' | grep Query | awk '{print $1}' | while read LINE;
do mysql -uUSER -pPASS -h HOST -e "kill $LINE";
done
where,
USER – Mysql Username
PASS – Mysql Password
HOST – Mysql Host