MySQLでALTER TABLEでINDEXを作成するときの注意事項

こんにちは。Ops側の小宮です。
ある日朝来たら突然開発の方から相談いただいたので、後のために記録しておこうと思います。

相談内容:
jenkinsで本番デプロイを行ったが、処理を途中停止した。
(CakeのDBマイグレーションスクリプトでデプロイした)
KEYカラムにINDEXをはろうとしたがDBの応答がなくなり接続できなくなった。
結果としてテーブルが破損したためRDSの時刻指定してロールバックする機能を用いた。
(ALTERが終わってたかどうかとかはロールバックしたので不明)
同じレコード数の試験環境で同じ操作をしたら特に異常なくすんなり終わった。
もう一回同じことを本番でやりたいけどどうしましょう。
MySQLのバージョンは5.5.27。

私の個人的認識:
普通、ALTERする時はロックがかかるから、
事前に同じ構成と件数の試験環境でかかる時間を見積もってから
その時間サービス止めてメンテ入れるべきです。
(※5.5まで。5.6から一部のALTERはオンラインで大丈夫になったようです。)
sorry表示に切り替えて更新のない環境でやってみましょう。
(既存のELBの下のwebを全部はずして、別のサブドメインのwebにvhost切ってsorryコンテンツおいてELBに入れるとか)
途中で強制終了とかするとMyISAMだとテーブル壊れやすそう。
show full processlist;で現在のクエリとかかってる時間は見ることができる
けどkillするのは最終手段かと。

alter table mysqlとか、それプラス5.6とかでググると色々わかります。

sh2さんのブログを見ると、以下のように書かれています。
MySQLでALTER TABLE文の進捗状況を確認する – SH2の日記
—————-
MySQLでは
変更後の定義にもとづく作業用テーブルを作成し、
変更前のテーブルから作業用テーブルへデータをコピーして、
最後に二つのテーブルを入れ替えるという仕組みになっています。
テーブルへのインデックス追加についても、現在のところ大半の
ケースで内部的にALTER TABLE文が実行されています。

どこまですすんでるのか確認する方法:
SHOW GLOBAL STATUS LIKE ‘Handler_write’;
作業開始前にHandler_writeの値と対象テーブルのレコード件数を控えておけば、
どこまで処理が進んだのかを確認することができるのです。

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

アライドさんのブログにおいて以下のとおり紹介されてました。
MySQL5.6の気になる新機能をチェック! | アライドアーキテクツ エンジニアブログ
—————-
InnoDBでいくつかのALTER TABLE操作でロックが掛からないように改善

今まではインデックスを張ろうとするとその最中のINSERTやUPDATEはロックがかかってしまうので
サービス提供中はパフォーマンスチューニングもできず、そのためにメンテナンスする必要がありました。
5.6からはDMLをブロックせずALTER TABLEができちゃいます。
これはかなり助かりますね。

ただし、全てのALTER TABLE操作ではなく以下に限定されるようです。
・カラムの追加、削除、リネーム
・セカンダリインデックスのCREATEとDROP
・auto incrementの値を変更
・外部キー制約の追加と削除
…etc

とはいえ、十分ですね。
運用がぐんと楽になります。
—————-

・ALTERを途中でKillするとどうなるか
MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.5.3 KILL 構文
ALTER TABLE の最中に、行の各ブロックが元テーブルから読み込まれる前にキル フラッグが確認されます。
もしキル フラッグが設定されると、ステートメントは異常終了し、テンポラリ テーブルは削除されます。
警告: MyISAM テーブル上で REPAIR TABLE や OPTIMIZE TABLE 操作を終了させると、テーブルが破損され、利用不可能になります。
それを最適化、または修復するまで(割り込み無しで)、そのようなテーブルへの書き込みや読み込みは失敗します。

・OPTIMIZE TABLEとかの効果↓
ANALYZE TABLE  インデックス統計情報の更新に使います
OPTIMYZE TABLE  MyISAMの可変長フォーマット(テーブル定義に可変長のカラムを持つ)と、InnoDBの
        データ部分にフラグメンテーションが起こったのを解消するために実行します
        MyISAMの場合は、インデックス統計情報の更新、デフラグの解消、インデックスページの並べ替えが行われます。
        InnoDBの場合は、内部的にALTER TABLEが行われてテーブルが再作成されることにより、
        インデックス情報もデータも再作成され、デフラグメントが解消されます。
CHECK TABLE   破損の可能性があるテーブルをチェックする際に使います
REPAIR TABLE   破損が確認できた場合に、復旧を試みます
ALTER TABLE   テーブルが再作成されることによりインデックス情報もデータも再作成され、デフラグメントが解消されます

・pt-online-schema-changeについて(参考)
サービスを停止せずにAlter Table | C.A.Mobile Engineers’Blog

PerconaToolkitがどこかに入ってるなら使ってみるといいかと思います。非常に便利なツールです。KEYの重複とかもわかります。

12/13追記:
以下もぜひご覧ください
漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。
MySQLでカラムやインデックスの追加、削除をするときのロックの話など – (゚∀゚)o彡 sasata299’s blog

とりあえず以上です。
見ていただいてありがとうございました。

おすすめ記事