Hatena::ブログ(Diary)

SH2の日記 RSSフィード

2011-06-15 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億レコード処理できるとは限らないという点には注意が必要です。

kennejimakennejima 2011/06/16 05:46 innotopをつかうと、row operationsのビューにズバリIns/Secがあるので、同様にしてALTER TABLEの完了予想が立てられますね。差を自分で計算しなくてもいいし、標準的なツールなので便利です。

sh2sh2 2011/06/18 14:48 innotopのソースを確認したところ、Rコマンドで出てくるInnoDB Row OperationsはSHOW ENGINE INNODB STATUSのROW OPERATIONSセクションを読んでいました。ここには累積レコード件数と秒速レコード件数の両方が記録されているので、InnoDBならこちらの方が便利ですね。情報ありがとうございました。