MySQL, Alter Table, and How to Observe Progress
Today we had to do a very large alter table on some production data. The table in question is big. 47 million rows big. We have to make some changes to drop a column and change some indexing to support our application. Fine, ALTER TABLE blah blah blah. But how do we keep an eye on the progress of the alter?
There’s nothing in the documentation for MySQL that speaks to a method, nor does any google searching pull up anything (except for the one InnoDB file per table patch.)
So what’s a clever systems administrator to do?
I looked at the output of lots of status commands from MySQL, and finally I came across something useful in the InnoDB engine status output. I noticed that there was an entry in the TRANSACTION section that speaks to the undo log entries. I noticed that after several hours of our ALTER TABLE running that the undo log entries value was up around 33 million. And then it occurred to me that since InnoDB does row-level operations, that this value might correspond to the number of rows that have been operated on throughout the process of the ALTER:
---TRANSACTION 0 2769988559, ACTIVE 53210 sec, process no 4115, OS thread id 1157654880 inserting, thread declared inside InnoDB 91
mysql tables in use 2, locked 2
111667 lock struct(s), heap size 13580272, undo log entries 33707735
MySQL thread id 948913367, query id 1662722204 localhost root copy to tmp table
So I wrote some bash. And some awk. And some perl, and I made a hacky little one-liner script to dump out the rate of change, the average rate of change, the % complete, and an estimate (based on average rate of change) as to when the query might be complete.
That script is below, and you are welcome to it. I have yet to wrap it in more clever perl to dump out an actual status bar, but I’m happy with it, and hopeful that this is a meaningful way of keeping an eye on the rate of change, and the progress of ALTER statements.
The script:
#!/bin/bash
(while(true); do \
(mysql -e 'show innodb status \G' | grep undo\ log\ entries ; sleep 1 ; \
mysql -e 'show innodb status \G' | grep undo\ log\ entries ) | \
egrep '[0-9][0-9][0-9][0-9]' |awk '{print $10;}' ; done ) | \
perl -e '$t = ROWS_IN_TABLE; while(1) { \
$n ++; $nn; $a = <>; $b = <>; $nn += ($b-$a); \
printf "Rate: %d, avg: %d, %0.3f%% complete, done in %d sec\n", \
$b-$a, $nn/$n, ($b/$t)*100, ($t-$b)/($nn/$n); }';
I hope you find this tool useful.
It took a LONG time for the query we performed to run, and it was very helpful to have this to gauge the time it’d take. The moving average is not the best for long-term accuracy; restart the script periodically for better views (and wait for ~100+ trips through the loop.)
It was within three minutes of accurate. It look a little longer than the script indicated (log undo entries was greater than the # of rows, but not by more than a small percentage (less than 0.01%.) The total query time was 22 hours, 9 minutes, and 33 seconds. So it was off by only 1 hundredth of a percent.
This is only valid for ALTER TABLE, but appears VERY useful. 
WOOHOO!
So in June, I’m having my first art opening. That’s exciting! Over the last half-year I’ve been talking to the folks at Utilikilts about whether they’d be interested in seeing my works in their store for Art Walk. They expressed interest that yes, indeed, they would like that. So we talked, and figured things out, and now I’ve got an opening coming up.
I’ve made a few changes to the blog: I’ve switched themes to something that I think fits better with what I want htis to be, and I’ve updated 
This weekend I went to the Hive-Mind Halloween party. It’s a pretty cool scene, and the money raised from it goes to support local arts organizations and other charities. It’s money for a good cause, and it’s a great time. I had a good time catching up with people I hadn’t seen in a while, and taking lots of pictures of people.