MySQL 5.6から InnoDBのオンラインDDL が導入されて久しいですが、一方で pt-online-schema-change (以下pt-osc）もまだまだ元気です。MySQL 5.5とそれより前ではpt-osc一択になりますが、MySQL 5.6とそれ以上の場合はInnoDBさんに任せるかpt-oscを使うかを選択することができます。
MySQL 5.6でもpt-osc一択にしても構わないといえば構わないんですが、いくつかのケースではInnoDBさんに任せた方が速くなったり安定したりするので、そのあたり解説していきます。
TL;DR
ウチの使い分け。- 原則 pt-osc
- スレーブの台数が多すぎない かつ
- データ容量が馬鹿でかくてストレージ食いつぶしそう または
- INSERT大杉で2度のメタデータロックが馬鹿にならない または
- デッドロック大杉 なら
- InnoDB Online DDLでRSU(Rolling Schema Upgrade)
When pt-osc？
- pt-oscの仕組みをざっくり
- 元テーブルから新しいテーブル（空っぽ）を作って
- 空っぽのテーブルにALTER TABLEをかけて
- 元テーブルへの更新をトリガーでフックしながら古いデータをコピーする
- コピーが終わったらRENAME TABLEで新旧テーブルを入れ替える
- 古いデータをコピーする処理を細切れにしてくれるので、レプリケーションスレーブを詰まらせることが少ない
- あと、スレーブの遅延監視のための --recursion-methodが結構柔軟に設定できる ので、「MySQL 5.6だからオンラインALTER TABLEいけるじゃん？」「ざんねん！ SQLスレッドは同時に1つのクエリーしかさばけない！」ということはない
- 古いデータをコピーする処理が細切れなので、
- デッドロックがボコボコ出たりする
- binlogを抱いて溺死
- まるまるテーブルをコピーするので
- 容量に十分な余裕が必要
- バッファプールもそれなりに圧迫する
- binlogを抱いて（ｒｙ
- トリガーを張る時、RENAME TABLEのタイミングでメタデータロックを取る
- 同時アクセス（メタデータロックなのでSELECTも含む）が多い環境だと割と簡単に詰まる
- 先行トランザクションが終了するまでメタデータロック待ちするので、その更に後から来たクエリーはメタデータロックが取れるまで待たされる
- trx1> SELECT .. -- コイツが終わらないとき
- trx2> RENAME TABLE .. -- コイツが"Waiting for table metadata lock"になり
- trx3> SELECT .. -- コイツも"Waiting for table metadata lock"になる
- メタデータロックとHandlerSocket Pluginの相性が最悪
- 日々の覚書: HandlerSocketの不思議な動作（まだ試す予定）
- ↑まだ試す予定とか言いつつ試さないまま3年半…:（;ﾞﾟ'ωﾟ'）:
- でもInnoDBのオンラインDDLとHandlerSocketの相性ってどうなんだろう。同じく死にそうな気もする。
- pt-osc開始時のメタデータロックはまだ「様子を見ながら開始する」「引っかかったら即中断」することができるけど、終了直前のは祈るしかない（;-人-）
- 中断した場合、一時テーブルとトリガーのお片付けは自分でやる必要がある
- 先にDROP TABLEするとトリガーが転け続けるので必ずDROP TRIGGERから先にやること
- 入れ替えたあとに要らなくなった方のDROP TABLEが走るので、 lazy drop table を食らったことがある
- lazy drop tableは直ったことになってるんだっけ？
- テーブルがまるまる再作成されるので、ついでにOPTIMIZEがかかったようなもの
When InnoDB Online DDL？
- InnoDBオンラインDDLの仕組みをざっくり
- ALTER TABLEで追加するインデックス, カラムなどを先に.ibdの外側に作っておくイメージ
- ソートやもろもろ終わってから、.ibdファイルにマージする感じ
- 追加するインデックスやカラムに対するALTER TABLE中の更新はtmpdirに書き出しておいて後からマージ
- マージまでの間に innodb_online_alter_log_max_sizeがあふれる とALTER TABLE中断
- マスターで1時間かかったALTER TABLEがスレーブでも1時間かかるのはブロッキングなALTER TABLEと同じ
- スレーブの更新クエリーはSQL_threadからしか入ってこないので、SQL_threadがALTER TABLEを掴みっぱなしになって結局レプリケーションが遅れる
- マスターとスレーブで *レプリケーションを通さずに* それぞれオンラインALTER TABLEをかけることで回避する
- この手間が惜しい場合はこっちは使えない
- Rolling Schema Upgrade（RSU）って言うらしい
- テーブル全体のコピーが発生しないのでI/O量がpt-oscに比べて少ない
- とはいえそれなり（もとのFast Index Creation相当）のI/Oは発生する
- binlogにやさしい
- カラムのデータを読み取る処理はロックを取らないのでデッドロックは起こらない
- メタデータロックに関しては開始時と終了時らしい
- 開始時のメタデータロックに関する注意事項はpt-oscと同じ。長時間トランザクションが来てないタイミングを見計らって開始
- 終了時に.ibdファイルにもろもろマージするタイミングでもメタデータロックを取るらしいけれど、pt-oscと違って目に見えた範囲で問題になったことはない
- pt-oscはステートメントでロックを取るステートメントを実行するのに対し、内部のAPIでロックを取るからなのかしらん？
- オペレーションを選ぶ。たとえばALTER TABLE .. MODIFYでデータ型が変わるものはブロッキングなALTER TABLEになる
- ポイントインタイムリカバリー（PITR）ととても相性が悪い
- RSUでない場合、単純にmysqlbinlogの結果を食わせる場合に結局そのスレッドを占有してしまう。
- *RSUでbinlogに書き出させない場合、このALTER TABLEだけPITRできなくなってしまう。*
という訳で、InnoDBのオンラインALTER TABLEでRSUするやり方。
master> SET SESSION sql_log_bin= 0; master> ALTER TABLE ..; slave1> SET SESSION sql_log_bin= 0; -- binlog吐いてるなら。中間マスターでない限り必須ではない。好み。 slave1> ALTER TABLE ..; slave2> SET SESSION sql_log_bin= 0; -- 同上 slave2> ALTER TABLE ..;
「やり方」も何もとても簡単。そのALTER TABLEだけbinlogに出力させないようにして、 *マスターとスレーブ全てのサーバーで* ALTER TABLEを実行する。
この方法だとbinlogに一切合財ALTER TABLEの情報が載らなくなるので、このALTER TABLEをまたぐ期間のPITRができなくなる。RSU後にフルバックアップ推奨。
（インデックス追加くらいなら後から来たクエリーも特に問題ないけど、カラム追加だとアプリのリリースかけた後のクエリーが全部詰まって死ぬ）
なので運用上は *原則pt-osc* としています。何回かやってもpt-oscに失敗する（あるいは容量不足でpt-oscができないことが明白）な場合だけRSU。
用法、用量を守って使い分けると便利です。
0 件のコメント :
コメントを投稿