How to check the progress while executing a heavy ALTER TABLE statement
This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Management Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article summarizes how to check the progress of a heavy ALTER operation in MySQL.
I hope this is of some help.
I want to estimate how long a heavy ALTER operation in MySQL will take.This information can now be viewed from the Performance Schema.Setup_instant_tableSetup_consumers_tableevents_stages_currentevents_stages_historyevents_stages_history_longCheck the progress of the ALTER command.In conclusion
I want to estimate how long a heavy ALTER operation in MySQL will take.
When performing heavy ALTER operations such as changing columns on a large MySQL table,
You might want to know how much longer it will take, or what process is currently running.
This information can now be viewed from the Performance Schema.
Performance Schema has a long history, having been released since the MySQL 5.5 era, and its functionality has been expanded with each version upgrade.
This time, the two tables in performance_schema are
- Setup_instant_table
- Setup_consumers_table
We will check the progress from here.
Setup_instant_table
This table controls which information is retrieved.
As you can see below, we can obtain information such as wait, stage, transaction, and memory.
The `stage` parameter indicates which stage of the query execution is currently being processed.
This time, we want to retrieve information about the ALTER operation in progress, so we will check if the following items are set to ENABLED.
I believe this is enabled by default in current versions of MySQL.
Setup_consumers_table
This table controls the collection and storage of performance information.
This time, we will check if these items are enabled.
events_stages_current
Gathering information on currently ongoing stage events.
events_stages_history
Keeps a history of recently concluded stage events.
events_stages_history_long
Maintain a longer-term history of stage events
It's disabled by default, so let's enable it.
For more details, please refer to the performance schema page in the official MySQL documentation.
Check the progress of the ALTER command.
You can verify this while a heavy ALTER query is running using the following query.
- EVENT_NAME (indicates which stage the currently executing query is in)
- WORK_COMPLETED (Number of work units completed in the stage)
- WORK_ESTIMATED (Number of work units expected in the stage)
The closer the value of WORK_COMPLETED is to WORK_ESTIMATED, the closer the task is to completion.
By regularly checking the figures, it's possible to estimate how much longer it will take to complete the project.
In conclusion
I've sometimes felt anxious when running heavy ALTER commands during system maintenance, not knowing how long it would take.
I think this method might provide some reassurance, so I'd like to make use of it.
SRG is looking for new team members.
If you are interested, please contact us here.