MySQL ALTER table Progress Bar?
May 15, 2008 – 9:48 pmAltering a big table sucks, and to make it worse you have no idea what’s happening or how long it will take. I’d like a progress bar, or some status output, or something that gives me the feeling like my server didn’t die.
6 Responses to “MySQL ALTER table Progress Bar?”
If you are using InnoDB, and innodb_file_per_table, the alter creates a temp table, and then drops the original table and renames the temp table. While the alter is running, you can compare the size of the temp table’s file (in your data directory) with the size of the original table’s file, to get a rough idea of the progress.
It’s only a rough gauge of progress because fragmentation, etc. will make the original table’s file bigger than the temp table will be. The temp table file will probably not grow to the same size as the original table’s file, before it finishes.
By nathan on May 15, 2008
Welcome to the club
For now, try something like (if innodb_file_per_table is enabled):
while true; do ls -lh /path/to/table.ibd; sleep 60; done
By Frank on May 15, 2008
Re: Frank. Or you can try a less hacky and more proper way
‘watch -d -n 60 ls -lh /path/to/table.ibd’
Reference: http://beerpla.net/2007/08/04/watch-a-useful-linux-command-you-may-have-never-heard-of/
P.S. Online alter table is planned for 2009, as far as I remember from the conference. That will be a godsend.
By Artem Russakovskii on May 15, 2008
Instead of ‘while true … etc’ you can also do:
watch ls -lh /path/to/table*
For more accurate info check out:
http://www.stillhq.com/mysql/000011.html
and
http://jcole.us/blog/archives/2007/02/08/progress-in-mysql-process-list/
Jeremy’s solution looks really nice
Would rock if that could be incorporated into MySQL. Would be even nicer if this could be done for other (non-ALTER) queries, one can dream, right? 
By Bart van Bragt on May 15, 2008
yup…. Jeremy’s patch is more than a year old. MySQL has been sitting on their collective asses and not accepting external patches.
So much for the open source process….
By Kevin Burton on May 16, 2008
Instead of making ‘alter table’ query you can create new one with all changes you want to do and type:
mysqldump all-usual-arguments | pv | mysql
pv is very useful tool to monitor pipes (http://www.ivarch.com/programs/pv.shtml)
By Alexey on May 16, 2008