How to check progress during a heavy ALTER TABLE
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article summarizes how to check the progress of a heavy ALTER operation in MySQL.
I hope this helps in some way.
I want to estimate how long it will take to execute a heavy ALTER command in MySQL.This can now be checked from the Performance Schema information.setup_instruments Tablesetup_consumers Tableevents_stages_currentevents_stages_historyevents_stages_history_longCheck the progress of an ALTER executionConclusion
I want to estimate how long it will take to execute a heavy ALTER command in MySQL.
When performing heavy ALTER such as changing columns on a MySQL table with a large data size,
There are times when you want to know how much time is left until completion, what process is currently being performed, etc.
This can now be checked from the Performance Schema information.
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
- setup_instruments Table
- setup_consumers Table
We will check the progress from here.
setup_instruments Table
This is the table that controls what information is retrieved.
As shown below, you can get information about wait, stage, transaction, and memory.
The stage is information about which stage is being processed during query execution.
This time, we want to obtain information while ALTER is running, so we will check whether the following items are ENABLED.
I think it's enabled by default in MySQL now.
setup_consumers Table
This table controls the collection and storage of performance information.
Now, check to see if these items are enabled.
events_stages_current
Collect information on currently running stage events.
events_stages_history
Keeps a history of recently completed stage events
events_stages_history_long
Keeping a longer-term history of stage events
It's disabled by default, so enable it.
For more details, please refer to the Performance Schema page in the official MySQL documentation.
Check the progress of an ALTER execution
You can check this by running the following query while a heavy ALTER query is running.
- EVENT_NAME (the stage at which the running query is)
- WORK_COMPLETED (number of work units completed in a stage)
- WORK_ESTIMATED (number of units of work estimated for the stage)
The closer the WORK_COMPLETED value is to WORK_ESTIMATED, the closer it is to completion.
By checking the numbers periodically, you can estimate how much time is left until completion.
Conclusion
When I was running a heavy ALTER command during system maintenance, I sometimes got nervous because I didn't know how long it would take to complete.
I think using this method will give me a little more peace of mind, so I would like to make use of it.
SRG is looking for people to work with us.
If you're interested, please contact us here.