Rusty Razor Blade
Altering 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.
If you enjoyed this article, please consider sharing it!
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.
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
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.
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? 
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….
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)