About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/How to check progress while a heavy ALTER TABLE is running

How to check progress while a heavy ALTER TABLE is running

This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article summarizes how to check the progress of a heavy ALTER 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.

When performing heavy ALTER such as changing columns on a MySQL table with huge data size,
There are times when you want to know how much longer it will take to finish, what process it is currently performing, etc.

This can now be confirmed from the Performance Schema information.

Performance Schema has a long history, having been released since the days of MySQL 5.5, and its functions have been expanded with each version upgrade.
This time, there are 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 you can see below, you can get information on wait, stage, transaction, and memory.
stage is information about which stage is being processed during query execution.
 
This time, we want to obtain information about the execution of ALTER, so we will check that the following items are ENABLED.
I think it is currently set to ENABLED by default in MySQL.

setup_consumers Table

This table controls the collection and storage of performance information.
 
Now let's check 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

Keep a longer-term history of stage events
 
It's disabled by default, so enable it.
 
For more information, please refer to the Performance Schema page in the official MySQL documentation.
 

Check the progress of an ALTER command

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 work units estimated for a stage)
 
The closer the value of WORK_COMPLETED is to WORK_ESTIMATED, the closer it is to completion.
By checking the numbers periodically, you can estimate how much time is left to complete.
 

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 finish.
I think using this method will give me a little more peace of mind, so I would like to put it to good use.
 
SRG is looking for people to work with us. If you are interested, please contact us here.