オンラインDDLの検証とミスをした話
本記事は株式会社ココナラ Advent Calendar 2024 3日目の記事です。
株式会社ココナラマーケットプレイス開発部でバックエンドチームのチームマネージャーをしているぴろと申します。
今回は、MySQL8.0系でオンラインDDLの検証を行った話を書きます。
何故その検証が必要だったか
ココナラでは日々、機能の開発や改修が動いていて、その中にはデータベースに大きな変更を入れたいケースがあります。
データベースに大きな変更を入れる際に大きな負荷を掛けすぎてしまうと、データベースにアクセスしにくくなったり、SQLの結果を返すのが遅くなったりしてしまいます。
そこで、MySQL8で強化されたオンラインDDLを活用することで、システムへの負荷を最小限に抑え、メンテナンス時間を短縮し、日中の作業も可能にすることを目指し、まずは調査を開始しました。
MySQLの公式ドキュメントや過去の事例などもあたってみましたが、数点、これが確認できないと実施は難しいかなと思う部分がありました。以下がその一例になります。
- ALGORITHM=INSTANTが早いのは分かった。ALGORITHM=INPLACEの時にどれくらい負荷がかかるのか？
- 一定以上のレコード数のテーブルに対して行い、パフォーマンス劣化は発生するのか
- 実際に本番運用しているインスタンスで試さないと判断が難しい
という事で実際に検証するに至りました。
検証項目の検討
要件によって検証すべき内容は変わってくると思いますが、今ココナラで必要だと思われるパターンを洗い出します。
| 検証内容 | 検証有無 |
|---|---|
| セカンダリインデックス追加 | インプレースでのみ行われるため検証を行う |
| インデックス削除 | インプレースでのみ行われるため検証を行う |
| インデックスの名前変更 | 用途が限定的なため検証は行わない |
| FULLTEXT インデックスの追加 | ケースにより変わるので随時ドキュメントを参照したほうがよいため検証は行わない |
| カラム追加 | インスタント、インプレースどちらでも検証を行う |
| カラム削除 | インプレースかつテーブルの再構築が走るが、ユースケースがありそうなため検証を行う |
| カラム名変更 | 同時DMLには条件があるためケースバイケースなので検証は行わない |
| カラムのデフォルト値の設定 | インスタント、インプレース、DMLともに可能なため検証を行う |
と、ざっくりこのような形になりました。(実際にはこれにプラスしていくつか検証は行いました)
検証に使うテーブルですが本番の1テーブルをコピーして実施します。
数億レコードあるテーブルに対してテーブルの変更を加えるケースが少なそうなため、ユースケースとして多そうな1千万単位のテーブルにしました。
結果
検証時のレコード数は1300万行程
インスタンスはメモリ128GBで本番相当のインスタンス
検証1:セカンダリインデックス追加
ALTER TABLE table_name ADD INDEX test_index_1 (user_id), ALGORITHM=INPLACE , LOCK=NONE
[2024-10-01 13:01:08] 19 s 568 ms で完了しました
- DDLのみ19秒(実行時間分)の遅延。それ以外は2ミリ程度の遅延(通常と同じ程度)
- 同時DML可能なためDDLのみに影響
検証2:インデックス削除
ALTER TABLE table_name DROP INDEX test_index_1, ALGORITHM=INPLACE , LOCK=NONE;
[2024-10-01 13:17:41] 60 ms で完了しました
- INPLACEだがメタデータの変更のみなので速い
- latency,throughputともにグラフに影響はなし。遅延もなし
検証3:末尾にカラム追加
ALTER TABLE table_name ADD COLUMN add_column_2 int AFTER last_column, ALGORITHM=INSTANT;
[2024-10-01 13:40:50] 142 ms で完了しました
- 142ms。INSTANTなので速い
- どのメトリクスもピクリともしていない
検証4:カラム削除
ALTER TABLE table_name DROP COLUMN add_column_1 , ALGORITHM=INPLACE , LOCK=NONE
[2024-10-01 13:52:48] 9 m 28 s 604 ms で完了しました
-
テーブルの再構築が走るため遅い
-
replicaの遅延はなし(25ms)
- latencyも数ms
- cpuは15%はねた
検証5-1:カラムのデフォルト値の変更(INPLACE)
ALTER TABLE table_name ALTER COLUMN status DROP DEFAULT , ALGORITHM=INPLACE;
[2024-10-01 14:03:48] 40 ms で完了しました
- 明示的にINPLACEにしているがこちらも問題なく実行可能
検証5-2:カラムのデフォルト値の変更(INSTANT)
ALTER TABLE table_name ALTER COLUMN status DROP DEFAULT
[2024-10-01 14:04:53] 42 ms で完了しました
- INSTANTで実行される想定
- 42msなので問題はなさそう
最後に
ここまで検証していますが、本番への実施時に1つミスをしてしまいました。
データベースのスキーマ変更はツールを使っていて、本番作業時には1つのテーブルに対して1つのSQLで複数の変更が行われていました。
なんとも初歩的ですが、この考慮が漏れていたため一部のテーブルには想定した以上の負荷がかかってしまいました。
本来はINSTANTで実行される想定が、インデックス追加も同時に行われたためINPLACEに実行になっていたというオチです。
幸い、早朝にリリース作業を行っていたためスロークエリが少し出る程度でしたが、検証したのに最後にミスしてしまい、肝を冷やしました。
本検証結果は、MySQLのオンラインDDLを検討されている方にとって、参考になれば幸いです。
明日は@yuta_k0911さんによる 2024年の技術広報取り組み成果発表！ です。
ココナラでは積極的にエンジニアを採用しています。
採用情報はこちら。
カジュアル面談希望の方はこちら。
Discussion