MySQLでALTER TABLE文の進捗状況を確認する

MySQLでテーブルへのカラム追加やテーブルの再編成を行うには、ALTER TABLE文を使用します。MySQLのALTER TABLE文は、変更後の定義にもとづく作業用テーブルを作成し、変更前のテーブルから作業用テーブルへデータをコピーして、最後に二つのテーブルを入れ替えるという仕組みになっています。テーブルへのインデックス追加についても、現在のところ大半のケースで内部的にALTER TABLE文が実行されています。
ALTER TABLE文の怖いところは、処理がもうすぐ終わるのかどうかが分からないところです。テーブルサイズが1GBを超えるあたりから分単位の時間がかかるようになり、100GBともなると本当に終わるのか？と見ていて不安になります。メンテナンス時間が限られている場合は、作業を中断すべきかどうか難しい判断を迫られることもあります。
実は、というほどではありませんが、ALTER TABLE文の進み具合を確認する方法があります。

mysql> SHOW GLOBAL STATUS LIKE 'Handler_write';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Handler_write | 22472949 |
+---------------+----------+
1 row in set (0.00 sec)

ALTER TABLE文は内部的に作業用テーブルへのINSERT処理を実行するので、ステータス変数Handler_writeにINSERTされたレコード件数が記録されていきます。

mysql> ALTER TABLE order_line ENGINE = InnoDB;
Query OK, 4796697 rows affected (1 min 4.78 sec)
Records: 4796697  Duplicates: 0  Warnings: 0

mysql> SHOW GLOBAL STATUS LIKE 'Handler_write';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Handler_write | 27269647 |
+---------------+----------+
1 row in set (0.00 sec)

ですから、作業開始前にHandler_writeの値と対象テーブルのレコード件数を控えておけば、どこまで処理が進んだのかを確認することができるのです。InnoDBの場合はInnodb_rows_insertedでも同じ値が取れます。

#!/bin/bash

while true
do
  cat <<_EOF_
SHOW GLOBAL STATUS LIKE 'Handler_write';
_EOF_
  sleep 10
done | mysql -u root -p -N

$ ./watch.sh
Enter password:
Handler_write   27270904
Handler_write   27270916
Handler_write   27270917
Handler_write   27270918
Handler_write   27981431
Handler_write   28791103
Handler_write   29546564
Handler_write   30319521
Handler_write   30980129
Handler_write   31629177
Handler_write   32067622
Handler_write   32067623
Handler_write   32067624
Handler_write   32067625
Handler_write   32067626

ただしテーブルサイズが大きくなるとINSERT処理がだんだん遅くなるため、最初の10分間で1億レコード処理できたとしても、次の10分間でまた1億レコード処理できるとは限らないという点には注意が必要です。