SQL queries for mysql RDS - CPU Usage, Locks, Waits and Kill | AWS

In this article let me share some SQL queries for RDS MYSQL troubleshooting

It is true that when you are choosing RDS or any managed database, you cannot fully control or customize your database. There are few granular restrictions and constraints

But AWS RDS is not a complete black box, there are certain ways and tools to help you administrate and manage RDS efficiently.

Like having a slow query and Error log enabled and ingested to cloud watch and having performance insights enabled etc.

Besides all these recommended production RDS settings or configurations there are a few typical ways to troubleshoot your RDS mysql instance

There are a few handy SQL queries to help you troubleshoot your CPU, Memory or Disk Usage related issues (or) find and kill long-running queries etc.

SQL Queries for RDS MYSQL Troubleshooting

I have collected and documented selective queries based on my experience with RDS MySQL.

You can use these commands to troubleshoot CPU, Memory and Disk Usage spikes and Long Running queries etc.

SQL Queries to Find the running processes or SQL queries

List all the running Processes - SQL Queries

SHOW PROCESS LIST

SHOW FULL PROCESS LIST

SELECT * FROM information_schema.processlist;

 – display number of connections for each user
SELECT `USER`, COUNT(*) FROM information_schema.processlist
GROUP BY `USER`;
 
 – display number of connections for each host
SELECT `HOST`, COUNT(*) FROM information_schema.processlist
GROUP BY `HOST`;
 
 – display root user activity
SELECT * FROM information_schema.processlist
WHERE `USER` = 'root';
 
 – display processes associated with SELECT queries
SELECT * FROM information_schema.processlist
WHERE `INFO` LIKE 'SELECT %';
 
 – display average query time for each database
SELECT `DB`, AVG(`TIME`) FROM information_schema.processlist
GROUP BY `DB`;

SQL queries to troubleshoot Locks

Find the current transactions  and locks and transactions waiting for locks using the following query

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

the following query helps you find the locks

 – mysql 5.7

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;


 – mysql 8.0

SELECT * FROM performance_schema.data_locks;

Transactions waiting for the locks to be released aka lock waits

 – Troubleshooting Lock Waits

 – mysql 5.7

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 – mysql 8

SELECT * FROM performance_schema.data_lock_waits;

The following query helps you to get more information on the mysql LOCK and the transactions holding the lock and the transactions waiting for the lock to be released

 – For mysql 5.7 - Find Waiting Transactions and the Locks blocking the resource required

SELECT 
r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query 
FROM information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx b 
ON b.trx_id = w.blocking_trx_id 
INNER JOIN information_schema.innodb_trx r 
ON r.trx_id = w.requesting_trx_id;

 – For mysql 8 - Find Waiting Transactions and the Locks blocking the resource required

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

How to kill a running Query in RDS mysql

I hope, Now with all these aforementioned SQL queries, you have got the transaction holding the lock or causing the CPU or memory surge.

Now you want to deal with it and kill the specific transaction or the long-running query.

The following SQL query helps you to kill the SQL transaction or process on RDS mysql

CALL mysql.rds_kill_query(Transaction ID);

The transaction ID can be taken from the process list or lock listing commands given above

Basically, this is a stored procedure available on the mysql schema on every RDS mysql instance

Here is a quick insight into the rds_kill_query stored procedure's SQL source code.

DELIMITER ;;
CREATE DEFINER=`rdsadmin`@`localhost` PROCEDURE `rds_kill_query`(IN thread BIGINT)
    READS SQL DATA
    DETERMINISTIC
BEGIN
   DECLARE l_user varchar(16);
   DECLARE l_host varchar(64);

   SELECT user, host INTO l_user, l_host
   FROM information_schema.processlist
   WHERE id = thread;

   IF l_user = "rdsadmin" AND l_host LIKE "localhost%" THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CANNOT KILL RDSADMIN QUERY';
   ELSEIF l_user = "rdsrepladmin" THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'CANNOT KILL RDSREPLADMIN QUERY';
   ELSE
      KILL QUERY thread;
   END IF;
END;;
DELIMITER ;

Hope this helps you in troubleshooting AWS RDS.

Let me know if you have any questions in the comments section and feel free to add more SQL queries that you may find useful for mysql RDS

Cheers
Sarav AK

Follow me on Linkedin My Profile
Follow DevopsJunction onFacebook orTwitter
For more practical videos and tutorials. Subscribe to our channel

Signup for Exclusive "Subscriber-only" Content

Name*

Email*