How to monitor long running ALTERS in AWS RDS MySQL 5.7+ using the Performance Schema

You've probably landed on this page because you have a MySQL RDS database that has one/many massive tables and anytime you run an ALTER it takes hours, maybe even days. Unfortunately, MySQL does not provide an easy way to monitor this and neither does RDS monitoring or RDS Performance Insights on AWS.

Luckily, since MySQL 5.7 there has been support for Progress information and that is implemented with the Performance Schema using the stage events. I recommend you read this excellent article for a detailed explanation (and also a better way to do the below in MySQL 8). In this article I will focus on MySQL 5.7

So how do you do it on RDS?

1. Set performance_schema parameter to 1 in your database's parameter group (⚠Requires instance reboot so that parameter is applied)

2. Once the parameter has been applied. Run the following queries
   

   -- Enable events_stages_current to monitor threads
   UPDATE performance_schema.setup_consumers 
   SET ENABLED = 'YES' 
   WHERE NAME = 'events_stages_current';
   
   -- Check results of below to see that ENABLED 
   -- and EnabledWithHierarchy are both "YES"
   SELECT 
       NAME, 
       ENABLED, 
       sys.ps_is_consumer_enabled(NAME) AS EnabledWithHierarchy 
   FROM performance_schema.setup_consumers 
   WHERE NAME = 'events_stages_current';
   

3. If the above works then you are good to go. You can now start an alter and execute this query to see its progress
   

   SELECT 
       stmt.THREAD_ID, 
       stmt.SQL_TEXT, 
       stage.EVENT_NAME AS State,
           stage.WORK_COMPLETED, 
       stage.WORK_ESTIMATED,
     ROUND(100*stage.WORK_COMPLETED/stage.WORK_ESTIMATED, 2) AS CompletedPct
   FROM performance_schema.events_statements_current stmt
   INNER JOIN performance_schema.events_stages_current stage
   ON stage.THREAD_ID = stmt.THREAD_ID
   AND stage.NESTING_EVENT_ID = stmt.EVENT_ID
   
   -- ALTERNATIVELY
   ----------------
   -- SELECT 
   --  thd_id, 
   --     conn_id, 
   --     db, 
   --     command, 
   --     state, 
   --     current_statement,
   --     statement_latency, 
   --     progress, 
   --     current_memory, 
   --     program_name
   -- FROM sys.session
   -- WHERE progress IS NOT NULL;
   

4. You should see something like this (where WORK_COMPLETED is how many rows have been altered and WORK_ESTIMATED is how many rows are left to alter and CompletedPct is the completion percent). Run this query everytine you need to see the updated values.
   

And that's it -- hopefully now you can monitor long running queries on an RDS database!

---

Hi I'm Emmanuel! I write about software, AWS and DevOps.

If you liked this article and want to see more, add me on LinkedIn or follow me on Twitter