株式会社High Linkの大崎（https://x.com/kantarow2813）です。
プロダクトの開発と運用を同時に継続していく環境では、既存のテーブルに対してインデックスやカラムを追加したいというケースに遭遇することがあります。そのようなケースではデータベースの負荷上昇や同時実行性の低下など考慮するべきことが多く、経験が浅い自分は腰が重くなりがちです。
幸いなことに、弊社が利用しているMySQLではオンラインDDL機能が提供されています。オンラインDDLは稼働中のデータベースのテーブルに対する変更をサポートするもので、同時実行性とスループットを維持しながらDDLを実行できます。
本記事では、MySQLにおけるオンラインDDLを活用して稼働中のデータベースを変更する際の注意点を紹介します。
環境
- MySQL8.0
- InnoDB
オンラインDDLとは何か
オンラインDDLは稼働中のデータベースのテーブルを変更するための機能であり、以下の二つの要件から構成されています。
インスタントとインプレース操作のサポート
インスタントとインプレースは従来の方式であるコピーと対比され、負荷や同時実行性の面でより優れたアルゴリズムです。
これらのアルゴリズムはコピー操作を伴わないためディスクI/OやCPUサイクルを節約でき、DDL実行中も高いスループットを維持できます。
またバッファプールという領域の消費もコピー方式より少なく済みます。バッファプールはテーブル及びインデックスのキャッシュに用いられている領域で、LRUのバリエーションによってキャッシュ管理を行なっています。そのためDDLによって領域を取られてしまうと他の操作のキャッシュヒット率が下がり、パフォーマンスが低下する恐れがあります。
同時DMLの許可
従来のコピー方式ではテーブルロックがかかってしまいますが、インスタントなら常時、インプレースなら条件付きで同時DMLを許可します。
インプレースとコピーの場合はLOCK句でロックのレベルを指定し、同時実行性とパフォーマンスのバランスを調整することができます。以下はLOCK句に指定できる値です。
| 値 | 説明 |
|---|---|
| NONE | クエリもDMLも許可する |
| SHARED | クエリだけ許可する |
| DEFAULT | クエリとDMLを可能な限り許可する |
| EXCLUSIVE | クエリもDMLも許可しない |
何も指定しなかった場合はDEFAULTが選択されます。DDLによってはロックの種類が制限されますが、許容されないロックを指定した場合、DDLはエラーになり実行されません。逆に言えばLOCK句を指定することで、意図せず同時実行性が低いDDLを実行することを防ぐことができます
オンラインDDL使用時の注意点
DDL実行時は選択されるアルゴリズムとロックについて注意する必要があります。
原則としてコピーアルゴリズムは避けるべきです。前述の通りデータベースの負荷が増加する傾向があり、テーブルロックがかかるので同時実行性の面でも制約があります。
インプレース操作が選択される場合は要件に応じて同時実行性を調整することができます。同時DMLはDDL実行中にDMLを一時領域に溜めておき、DDLが完了した後に適用します。DMLが適用されるまでテーブル変更のプロセスは完結しないため、DDLのパフォーマンスを優先してロックのレベルを上げることも考えられます。
また、適切なアルゴリズムとロックが選択されている場合でも、オンラインDDLにはいくつかの制約があります。例えばオンラインDDL実行中の同時DMLは一時ログ用の領域に保存されますが、そのサイズはinnodb_online_alter_log_max_size によって規定されます。これを超過するとエラーが発生してDDLが失敗するので、DDLの実行にかかる時間と時間あたりのDMLの発行量が多い場合はケアする必要があるでしょう。
このような制約はMySQLのリファレンスマニュアルに記載されているので、一読することをお勧めします。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.12 InnoDB とオンライン DDL
DDLがどのように実行されるか確認する
DDLが具体的にどのように実行されるかは、MySQLの公式ドキュメントに当たり、必要に応じて検証することで確認できます。
オンラインDDLのサポート状況を確認する
MySQLのリファレンスマニュアルには、オンラインDDLのサポート状況が以下のような表で記載されています。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.12.1 オンライン DDL 操作
基本的にはこの表を見ればいいですが、画像内のAdding a FULLTEXT indexのようにアスタリスクが表記されているものがあります。これらは表の後に続く使用上の注意で制約が説明されているので、そちらも確認しましょう。
またオンラインDDLに共通の制約が書かれているページがあるので、そちらも確認しましょう。
rows affectedを確認する
より確実な方法で確認したい場合、安全な形でDDLを実行して評価する方法もあります。
テーブルコピーが伴わないことを確認するためには、対象のテーブルのコピーを作成し、DDLを実行した際のrows affectedを見る方法があります。コピーを伴わない場合は影響を受ける行は0になりますが、コピーが発生する場合は1以上の値になります。この際にダミーデータを投入することを忘れないようにしましょう。
DDLの進捗を監視する
MySQLではパフォーマンススキーマからDDLの進捗状況を確認することができます。
事前にALTER TABLEに関するinstrumentを有効化するなどの設定が必要ですので、ドキュメントを確認してください。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.16.1 パフォーマンススキーマを使用した InnoDB テーブルの ALTER TABLE の進行状況のモニタリング
performance_schema.events_stages_current というテーブルで現在進行中のDDLの進捗を確認することができます。
WORK_ESTIMATEDカラムが予測される作業数で、WORK_COMPLETEDが実際に完了した作業数です。WORK_ESTIMATEDはあくまで予測なので、最終的に完了した作業数とズレる場合があります。
クエリを更新し続けているとWORK_COMPLETEDが増えていきます。パフォーマンス監視などは別の手立てを取る必要がありますが、DDLの進捗状況が分かるだけで安心材料になります。
おわりに
今回はオンラインDDLによるテーブル変更についてのノウハウを紹介しました。
基本的にはドキュメントを参照して制約を確認し、要件に合わせて実行するという流れになりましたが、今後はこれを社内のデータベース運用ポリシーに組み込み、組織の資産にしていきたいと思います。組織的な運用についての知見が溜まった際は、またこのテックブログで紹介させていただきます。
パフォーマンスの監視についてはあまり触れることができませんでしたが、どのメトリクスを重点的に見ればいいかなどの知見が未だ蓄積できていないため、こちらもこれから実践と学習を積み重ねていきたいと思います。
最後までお読みいただきありがとうございます！
High Linkはバックエンドエンジニアを積極的に募集しています！
本記事で紹介したように、私たちのデータベース運用にはまだまだ課題があります。知識と経験を持っている方も、これから学んでいきたい方も、High Linkに興味を持っていただけたら是非一度お話ししましょう！
