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?
- Set
performance_schema
parameter to 1 in your database's parameter group (⚠Requires instance reboot so that parameter is applied) -
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';
-
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;
You should see something like this (where
WORK_COMPLETED
is how many rows have been altered andWORK_ESTIMATED
is how many rows are left to alter andCompletedPct
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
Top comments (0)