巨大なテーブルのテーブル定義を無停止で安全に 誰でも変更できるようにする 2024.04.08

　 2 ● freeeのエンジニア ● 2023/3までfreeeでDBREのチー ムリーダーをやっていました。 ● その後転職の後2024/1からfreee に出戻り ● 最近はプロダクト横断で利⽤す る基盤システムを作っています ● 最近はDB系の論⽂を読む会を主 催しています shallow1729

3 テーブル定義の変更の重要性と課題 ● WEB開発においてはサービスの開発後に新しい要件に合うようにカラムやインデッ クスの追加、変更が必要になるケースがある
 ● 頻繁にDDLを行いたいが以下のように無停止でのDDLには危険性がある
 ○ DDLの実行はテーブルのサイズや行いたい変更の種類によっては実施が困難 
 ■ 数十億レコードのテーブルのインデックスの追加 
 ■ カラムのデータ型の変更などのOnline DDLできないもの 
 ● ドキュメントのPermits Concurrent DMLを確認 
 ○ DDLの負荷が問題になるケースがあるがコントロールしづらい 
 ○ アクセスの多いサービスでのDDLではメタデータロックのケアが必要(後述) 
 DDLを無停止で頻繁に行いたいユースケースがあるがMySQLの標準の仕組みは 要件に合わない


4 メタデータロックの動作 ● MySQLはInnoDBなどのストレージエンジンで取る行ロックなどとは別にエンジンの レイヤーで取るメタデータロックというやつがいる
 ● クエリやDML時はshared、DDLの実行時はexclusiveにロックを取る
 ● メタデータロックはトランザクションの終了時に手放される
 ● exclusive lockはshared lockを待つ必要があり、exclusive lockが待っていると後続 の処理はshared lockを取れない
 DDLの対象テーブルに対してロングトランザクションがあるとDDLが実行できず、 後続のクエリも待たされる


5 メタデータロックの競合のイメージ
 ロングトランザクションのある状況でDDLを実行するのは危険


6 メタデータロックの問題の回避策
 ● lock_wait_timeoutというMySQLのパラメーターでメタデータロックの取得待ちのタイ ムアウトを決められるので、これが小さいとロック待ちで詰まることを回避できる
 ● freeeではDDLを実行するセッションのみlock_wait_timeoutを小さくすることで既存の ワークロードへの影響を最小限にOnline DDLの安全性を高めている
 ● しかし、メタデータロックは一般にDDLの開始時と終了時の二度取るタイミングがあ り、終了時にタイムアウトが発生すると最初からやり直しになる...


7 MySQLのOnline DDLの課題のまとめ
 ● MySQLの通常のOnline DDLでは以下のケースで課題がある
 ○ データ量の多いテーブルのインデックス追加やデータ型の変更などOnline DDLを利用できないケー スがある
 ○ DDLの負荷自体がワークロードに影響を与えるリスクがあるがコントロールが難しい 
 ○ メタデータロックの問題があり、ロングトランザクションがあるワークロードでのOnline DDLはリスク がある
 ■ lock_wait_timeoutを小さくすることで影響は減らせるがDDLが失敗するリスクが上がる 


8 一般的な代替案
 ● メンテナンスを実施してDDLを実行
 ● レプリカでDDLを実行した後にレプリカをプライマリーに昇格する
 ● pt-online-schema-change(ptosc)などのDDLを実行するためのツールの利用


9 レプリカでDDLを実行して切り替える
 ● インフラ作業やMySQLの標準の機能(レプリケーションやDDL)で済むので実装は不要 
 ● インフラの理解や権限が必要でプロダクトチームに任せづらく、スケールしない 
 ● プライマリーへの昇格時に接続先を変更するために許容できない停止時間が発生する

10 ptoscの動作
 ● ソフトウェアの処理で完結するのでインフラよりはプロダクトチームに権限を渡しやすい 
 ● パラメーターで負荷の調整が可能で切り替え時の停止時間も小さい 
 ● 利用経験のある人が少なく、学習コストがかかりそうなのでスケーラビリティの問題がある 
 ● 通常のDDLより時間がかかる上、メタデータロックを取るステップが最後にあり、メタデータロックの取得に 失敗する可能性のケアが必要 


11 freeeでの解決策 ● ptoscをRDS/Auroraに対して実行するECS taskを呼び出すAWS Step Functionsを 用意
 ● AWS Step Functionsの定義はTerraformで管理し、ptoscに関するTerraformの変更 やStep Functionsの実行権限、実行ログの閲覧権限などを各プロダクトチームに付 与
 ○ TerraformはGitHubで管理されており、prに実行したいDDLを書いてマージするとStep Functionsに その情報が反映される 
 ● ptoscをそのまま使うのではなく、設定や手順などについてDBREのベストプラクティ スが反映している


12 freeeでの解決策の図

13 ptoscの実行に関する工夫
 ● メタデータロックのケア
 ○ lock_wait_timeoutを短くする 
 ○ ptoscの最後のスワップは行わずに実行し、別タスクでswapを行う(--no-swap-tables) 
 ■ スワップの失敗時にリトライできるようにするため+後述の事前チェックのため 
 ● ptoscの実施によるワークロードへの影響のケア
 ○ chunk sizeなどのパラメーターの調整 
 ○ max loadなどのptoscに備え付けの高負荷を検知して処理を止める仕組みの利用 
 ■ 使っていないがRDSのcpu使用率を見て止めるパッチを作ったりもした 
 ● ptoscが正しく実施できたかの事前チェック
 ○ テーブルのレコードをちょっとずつ比較して差分が無いかを確認 
 ● その他失敗時の掃除なども含めてジョブを用意してプロダクトチームに提供


14 ptoscの導入の効果
 ● int32のauto incrementのカラムが一杯になりかけていたテーブルについてデータ型 をbigint化する事を無停止で実現
 ● 負荷の調整によりアクセスの多い時期でも安全にDDLを実現。DDLのためにメンテ を行わなくてもよくなる。
 ● 大半のオペレーションが自動化され、権限がプロダクトチームにあるので各チーム で実行が可能になり作業のスケーリングを実現
 ● ptosc実行中のレプリケーション遅延などの問題もAurora MySQLの場合独自のレ プリケーションの仕組みでほとんど起きなかった


15 まとめ
 ● freeeではサービスの継続的な成長を支えるための独自のDDLの仕組みを用意し、 プロダクトチームに提供している
 ● 無停止で: ptoscにより数十億レコードのテーブルのデータ型の変更のようなOnline DDLが使えないケースも対応する
 ● 安全に: DBREのベストプラクティスを自動化処理の実装に落とし込む
 ● 誰でも: プロダクトチームにタスクの実行権限を渡す事でツールの学習コストを抑 え、プロダクトチーム主体で難易度の高いDDLも必要なタイミングで各自で実現で きるようにする


16 技術的な詳細を知りたい方は
 ● pt-online-schema-changeの導入時に検討したこと、およびRailsアプリとの併用に ついて
 ● AWSマネージドサービス+Terraformを活用してDBオペレーションをより安全&簡単 に（pt-oscの例）


スモールビジネスを、世界の主役に。