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
The output from the previous command is not sortable or searchable. If you want to use the where clause or sort along with it use the following alternate
SELECT * FROM information_schema.processlist;
This is the alternative if you want to apply group by on the PROCESS LIST
You can do a lot of filtering and grouping with this command, Here are some examples
- Display the number of connections for each user
- Display the number of connections for each host
- check the activity of the specific user with where clause
- Find the transactions that run a particular query with LIKE
- Display the average query time for each database
– 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
Signup for Exclusive "Subscriber-only" Content