r/mysql • • Oct 13 '19

How monitor MySQL ALTER TABLE command progress?

It is a common question to MySQL experts when there are running ALTER TABLE on production environments especially for large tables with millions of records. How to monitor ALTER TABLE progress? How much time does it take?

You can get details using the following SQL query

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

It should be needed to enable Performance_schema in MySQL . More details

This Query also loaded in SmartWorkbench tool and if you type "AM(AlterMonitor)" and enter then this query will be replaced.

SmartMySQL's SmartWorkbench is a GUI tool for MySQL to develop MySQL 10X faster demo. It is Free , Downlaod

Speed up your SQL queries 100X Demo

Fix production issues with your fingertips

0 Upvotes

6 comments sorted by

4

u/reddit_be_nice Oct 13 '19

Show processlist;

1

u/SmartMySQL Oct 13 '19

show processlist just give how long the ALTER command has been executing. If you would like to know when it will be complete then ?

1

u/Risyasin Oct 13 '19

Show processlist never really gives an accurate percentage of work done neither est. time to finish. Observing tmp file size is also misleading. Sometimes it just needs to reads several times. It's all up to your data size your indexes etc. Too many variables. You can only have iostat like command to see what's really going on. But if you really need that time to be short. You can create a replica/slave maybe delayed one. Alter the tables/dB in somewhere else. Then replace it with the updated one. Kind of blue/green deployments.

5

u/xenilko Oct 13 '19

If it creates a temp file table just monitor its size as this will give you some insight

2

u/kristofer_grahn Oct 13 '19

IMHO, This is the best way to monitor progress.
A while true; loop running du -h on the temporary files.

3

u/MisterPinkySwear Oct 13 '19

There’s a command named watch to replace that while loop.

https://linux.die.net/man/1/watch