DEV Community

Cover image for How to monitor long running ALTERS in AWS RDS MySQL 5.7+ using the Performance Schema
Emmanuel K
Emmanuel K

Posted on

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) Image description
  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.
    Image description

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

Top comments (0)