Tag Archives: MySQL

MySQL, Alter Table, and How to Observe Progress

Network Gear

This only works on MySQL 5.0.  If you’re using 5.1+, this approach isn’t for you.

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 viagrasstore.net.  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:

(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, acheter viagra!

Thoughts from MySQL Conf, Oracle’s Acquisition of Sun, and Meeting New People

I attended MySQL Conf, and it’s been an neat conference.  The most surprising thing was the news was the purchase of Sun by Oracle.  This sounds to me like a play for a solid vertically integrated market for Oracle.  I can appreciate that — they get Sun’s hardware and OS skills, they bring their own DB services to the table, and they get a lot of community with MySQL.

Lots of people have spoken about their fears that Oracle will suffocate MySQL, but I don’t think they’ll do that.  I think that Oracle realizes how centrally important to the web community that MySQL is.  I do think they’ll try to sell Oracle to high-volume MySQL sites, and that makes sense.  And I think they’ll continue MySQL to bring DB share under the Oracle roof.

MySQL is hugely popular, it wouldn’t make any sense to throw away that good will.

Other things from the conference have been that I’ve run into people I haven’t seen in a while, and I’m learning things.

One of the most interesting talks I went to was Rockyou’s talk about how they built their federated platform for supporting their Facebook applications.    Very neat to see their approach, especially since we’re buidling our own sort of Facebook applications, specifically Bejeweled Blitz.   Good times.

I met the photographer called Julian Cash, a very nice guy located in San Francisco.  He did a light painting portrait of me, which was fun.   We talked about various things for a while, and he talked to me about his project that he’s working on.  More to come later.

Today is the last day.  Things are winding down, and I’m preparing to go home.