MySQLのオンラインDDLとpt-online-schema-change
技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要
サービスの仕様追加や新規開発で、既存テーブルへのカラムの追加やインデックスの追加などのDDL（Data Definition Language)操作が必要になることがあります。
その時にサービス稼働中に普通にDDL操作を行ってしまうと、テーブルロックが発生し重大なサービス障害に繋がる可能性があります。
下記の方法を利用することでテーブルのロックを防いで、オンラインでDDL操作を行うことが可能です。
結論
AWS Auroraの場合
- MySQL公式機能のオンラインDDLを使う
- 後述の理由で使えないなら次点でpt-online-schema-change
RDS(MySQL) 、もしくは素のMySQL(5.5~5.7)の場合
- pt-online-schema-changeを使う
方法
MySQLの公式機能を使う(MySQL5.6~)
MySQL5.6から実装されたオンラインDDL機能により、操作の一部がロック不要で実行できるようになりました。
抜粋すると下記のような操作がオンラインで実行可能です。
- OPTIMIZE TABLE
- ADD INDEX
- CREATE INDEX
- DROP INDEX
- カラムの追加
- カラムの削除（コスト高注意)
- 外部キー制約の追加・削除
また、MySQL8.0.12からはINSTANT DDLという機能が加わり、メタデータのみを更新することで高速にDDL操作を行うことができるようになりました。
ただしALGORITHM=INSTANTで実行するためには下記のような制約があります。
- カラムの追加は、ALGORITHM=INSTANT をサポートしない他の ALTER TABLE アクションと同じステートメントで組み合せることはできません。
- カラムは、テーブルの最後のカラムとしてのみ追加できます。 他のカラム間の他の位置へのカラムの追加はサポートされていません。
- ROW_FORMAT=COMPRESSED を使用するテーブルにはカラムを追加できません。
- FULLTEXT インデックスを含むテーブルにはカラムを追加できません。
- カラムは一時テーブルに追加できません。 一時テーブルでは、ALGORITHM=COPY のみがサポートされます。
- データディクショナリテーブルスペースに存在するテーブルにはカラムを追加できません。
- 行サイズ制限は、カラムの追加時には評価されません。 ただし、行サイズ制限は、テーブルの行を挿入および更新する DML 操作中にチェックされます。
8.0.29以降ではALTER文を実行した際、INSTANTをサポートしている操作の場合はALGORITHM未指定でもデフォルトでINSTANTが使用されます。
また、下記のような機能の追加がありました
- 末尾にカラム追加だけではなく他の位置へのカラム追加も可能
- DROP COLOMNに対応
詳しくは公式ドキュメントのオンラインDDLの項目をご参照ください。
このオンラインDDLの機能を利用する際の注意点は、
ALGORITHM=INSTANTが実行できない状況でデータサイズの大きいテーブルで実行するとそのままレプリケーション遅延する
という点です。
マスターDBでDDL実行したものが20分掛かった場合、マスターで実行完了後にスレーブDBで実行されるので、
それがそのまま20分のレプリケーション遅延となります。
実行環境がAmazon RDS Auroraの場合はアーキテクチャが異なるためこの通りではありません
これを防ぐにはスレーブで先にDDL実行したあとに、マスターDBでbinlogを一時的に吐かない状態にしてDDLを実行します。
これを一般的にRolling Schema Upgradeと呼びます。ただこの方法にもデメリットはあります。
スレーブで先にカラムの型変更やカラム追加が行われている状態となるため、
その状態でもアプリケーションのつくりは問題ないかを事前に確認しておく必要があります。
これらの点が許容できない場合は後述のpercona製のツールを利用する方法があります。
pt-online-schema-changeを利用する
pt-online-schema-change(以下pt-osc)はPercona ToolKitに同梱されているツールで、通常テーブルロックがかかってしまうALTER文をロック無しで実行してくれるツールです。
このツールのアーキテクチャや詳細は下記のページで解説しているのでご参照ください。
percona toolkitのインストール方法などは公式ドキュメントをご参照ください。
pt-oscを利用することでオンラインDDLの機能がないMySQL5.5でのオンラインDDL実行や、
実行時間が長いDDLも比較的レプリケーション遅延がしづらいように実行することが可能です。
カラム追加の場合の実行サンプル
このツールも制約などがあるため、すべての状況で利用できるわけではありません。
事前に検証環境でテストした上でご確認ください。
終わりに
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。