そういえば先日はハロウィンだったね。んで、今日スタバに行ってみたら早くもクリスマス仕様。残念ながら年末の予定は無え。
そんなこんなでオンラインスキーマチェンジを本番のオペレーションで使い始めたのでそのメモ。



0. online-schema-change

オンラインスキーマチェンジは、percona社が出しているpercona-toolkitに梱包されている。その他有用なツールも入っているのでお世話になっている人も多いだろう。で、オンラインスキーマチェンジはその名のとおり、スキーマの変更、alter文をブロックなしで実行してくれるという代物。
私事なんだけど、今までは「ちょっと更新をブロックしちゃうけどアクセスの少ない時間帯にオンラインでalterを流す」みたいな運用をしてた。実行内容にもよるけどalter tableは意外と早いので、「更新をブロックされる時間がSLA的に許可できるならOK」という判断。んでまあ、今回、alterにかなり時間のかかる巨大なテーブルをもつDBを運用することになったので、さすがにオンラインで生のalterはいけねーだろ…って思って手を出した。なんで”今更ながら”なのかというと、直近の最新バージョンであるMySQL5.6のalter文は同等の機能を有しているから。そっちの方はおれは検証してない。ていうかMySQL5.6を本格的に運用したことない。

1. 環境

  • MySQL: 5.5.24
  • ストレージエンジン: InnoDB
  • percona-toolkit: 2.2.5-1
2. インストール

perconaさんのリポジトリからインスコできる。次の例はRPMの例だが、debやソースも提供されている。

1
2
3
wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.5-1.noarch.rpm
yum install perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-MySQL
rpm -ivh percona-toolkit-2.2.5-1.noarch.rpm

3. 使い方

–alterに続けてalter文の中身を書く。「u」でユーザ指定、「h」でターゲットとなるDBのIP/host、「D」でデータベース名、「t」でテーブル名を指定する。set sql_log_bin=0など、set xxxxしたいときは–set-varsオプションを使う。

例えば↓のオペレーションは…

1
2
3
4
mysql -uroot -h localhost
use db;
set sql_log_bin=0;
alter table t engine=InnoDB;

こうなる↓

1
/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter="engine=InnoDB" h=localhost,D=db,t=t,u=root

その他ありそうな使い方を挙げておく

カラム追加: nameカラムの後ろにdatetime型のカラムdtを追加

1
/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter "add column dt datetime DEFAULT NULL after name" h=localhost,D=db,t=t,u=root

インデックス作成: nameカラムにインデックスを作成

1
/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter "add index idx_name(name)" h=localhost,D=db,t=t,u=root

インデックス張り替え: nameカラムに貼ったインデックスを削除してnameとdtに複合インデックスを作成

1
/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter "drop index idx_name,add index idx_name_dt(name, dt)" h=localhost,D=db,t=t,u=root

ストレージエンジン変更/InnoDB最適化

1
/usr/bin/pt-online-schema-change --execute --set-vars="sql_log_bin=0" --alter "engine=InnoDB" h=localhost,D=db,t=t,u=root

4. メモ

挙動

コマンドを実行するとなにをやっているかが標準出力されるので、ツールがなにをやっているかはわかりやすい。おおむね次のような感じ。

 
  1. ターゲットのテーブルから作業用テーブルを作成(※1)
  2. 作業用テーブルにalterを適用
  3. トリガーを作成(※2)して、ターゲットとなるテーブルへの更新を作業用テーブルに反映されるようにする
  4. ターゲットのテーブルから作業用テーブルへデータをコピー
  5. RENAMEで作業用テーブルとターゲットとなるテーブルをすげかえる。作業用テーブルに正規のテーブルとなる。
  6. 作業用テーブルとトリガーを削除
※1: ターゲットのテーブルが「user_account」の場合、「_user_account_new」って作業用テーブルが作られる。つまり「_TABLENAME_new」。
※2: ターゲットのデータベース名が「db」でテーブルが「user_account」の場合、トリガはpt_osc_db_user_account_del(削除用), pt_osc_db_user_account_upd(更新用), pt_osc_db_user_account_ins(挿入用)の3つが作られる。つまり、「pt_osc_DATABASE_TABLENAME_del」「pt_osc_DATABASE_TABLENAME_upd」「pt_osc_DATABASE_TABLENAME_ins」。

普通にalterするよりは遅い

まあそうなるよね

作業用テーブルをリネームするときが一番重くなる

実行時にOSのリソースやらMySQLやらinnodb statusを監視していたんだけど、作業用テーブルをリネームしてdropするフェーズのときに、innodb statusのACTIVEトランザクションがかなり溜まる。それも10秒とか20秒とか。それに伴ってスローログがどわっとでる。作業用テーブルをすげかえるときだけはさすがにロックするからね…。
このフェーズ以外のときはおおむね安定しているが、CPUやIOを普段よりも食うので、やはり安全な時間帯に実施すること。

アプリケーションへの影響

(おれがやったときは)実行中にwebサーバのHTTP500番台レスポンスやエラーログが目に見えて増えるようなことはなかったけど、上述したように、終了間際にオンライン処理のスローログが検出されるので、致命的ではないにしろ少なからず影響は出る。

2013/11/07追記:ちなみにおれはselect 8k/sec, update 800/sec, insert 200/sec, delete 6/sec程度のときに実施して問題なかった

途中でキャンセルしたときの挙動

Ctrl + Cとかkillで止めることができる。

ただしCtrl+Cやkillで止めた場合は、

1
2
3
4
5
set sql_log_bin=0
drop trigger pt_osc_xxxx_xxxx_upd
drop trigger pt_osc_xxxx_xxxx_ins
drop trigger pt_osc_xxxx_xxxx_del
drop table _xxxx_new

などとして作業用テーブルやトリガを削除する必要がある。先にトリガを削除すること。そうしないと、もとのテーブルへの更新をnewテーブルに当てようとして”テーブルがないよエラー”になる。

スレッド数が50を超えるとコケる

一回だけコケた。どうやらスレッド数50が閾値になっていて、これを超えると安全のために止める仕様とのこと。情報のソースは忘れた(Stack Overflowだったかな…)。maxなんちゃらってオプションで閾値上げることができるけど、まあデフォでいいかな、と。この挙動についてはそんなに深追いはしなかった。

1
2
mysql -uroot -e "show global status like '%thread%'"
コレね... -> Threads_running 10

なかなかの神ツールなのでバンバン使ったらいいんじゃないかな。

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Set your Twitter account name in your settings to use the TwitterBar Section.