おはこんばんちは、Database Reliability Engineer (DBRE) の橋本です。今回は、pt-online-schema-changeというデータベースのスキーマ変更ツールを社内の運用に持っていくための過程や、freeeにおける運用上の工夫を紹介します。pt-online-schema-changeはある程度枯れた技術なので、いくつか事例が紹介されており、すでに運用に乗せている組織もあるかと思われますが、これから新規に導入する方などの参考になると幸いです。
背景
freeeではほぼ毎月ペースで深夜に定期メンテナンスを行なっており、おもにインフラの更新作業やアプリケーションの機能追加に伴うデータベースのスキーマ変更が行なわれます。
ここで少し寄り道をして、スキーマ変更を行なうためのMySQL（ストレージエンジンにInnoDBを使っている前提で進めます）のDDLのパターンについて説明します。MySQL 8.0のドキュメント "Online DDL Operations" 内のそれぞれの表をみると、MySQL 8.0ではDDLを以下のように大別できそうです:
ALGORITHM=INPLACE系- 表のIn PlaceがYesでPermits Concurrent DMLがYes: Online DDLで、DDL実行中も当該テーブルに対してDMLによる読み書きが可能
- Rebuilds TableがYes: 内部で新しくテーブルをつくり、そちらに向けるような操作
- Rebuilds TableがNo: テーブルを作らない分Yesの場合よりも軽い操作
- 表のIn PlaceがYesでPermits Concurrent DMLがYes: Online DDLで、DDL実行中も当該テーブルに対してDMLによる読み書きが可能
ALGORITHM=COPY系- 表のIn PlaceがNoでPermits Concurrent DMLがNo: DDL実行中は当該テーブルへのDMLは待機させられる
ALGORITHM=INSTANT系（MySQL 8.0から導入）- 表のInstantがYes: メタデータを書き換えるだけですぐ終わるDDL*1
ALGORITHM=COPY系の操作はDDL実行後、完了までサービスが停止することになるので、基本的にメンテナンス時間内に実行していました。また、Online DDLでも、DDL実行中から完了までに行なったDML書き込み操作は一時的なログファイルに書き込まれます*2。このログファイルの領域からあふれるとDDLは失敗し、未コミットのDMLはロールバックされるため、DDLの実行時間が長いとDDLが失敗する危険性が増えることになります。また、DDLが完了したとしてもその後にDMLの書き込み操作が一気に反映されるため、場合によってはディスクIOの面で不安があるかもしれません。
そのため、社内でOnline DDLの実行時間の基準を設けて、その時間が基準内であればアプリケーション開発者が自らスキーマ変更を行ない（Ruby on Railsのdb:migrateを行なうのみ）、基準を超えるようであれば定期メンテナンスで実行する運用をとっていました。
定期メンテナンスの話に戻りますが、この時間帯はお客様がfreeeを利用できない時間となります。DBREチームでは、なるべくダウンタイムなしにサービスをいつでも使っていただけることがマジ価値*3だと考え、定期メンテナンスによるスキーマ変更をメンテナンス外にて無停止で行なう方法を検討しはじめました。
pt-online-schema-changeの概要
pt-online-schema-change（以降pt-oscとよぶ）は、Percona社が公開しているRDBMSの運用のためのツールキットであるPercona Toolkitに含まれるツールのひとつです。GNU GPL v2.0ライセンスで公開されており、pt-oscの実体はPerlスクリプトとなっています。
pt-oscは名前のとおり、オンラインでMySQLのスキーマを変更するためのツールです。pt-oscのスキーマ変更の方法では、前述のオンラインで実行できないようなDDLでもオンラインでスキーマ変更の実行を可能にします。
pt-oscがどのようにスキーマを変更しているか、ソースコードとともに流れを追ってみます。バージョンはv3.xを想定しています。
pt-oscの一連の手順
1. スキーマ変更対象のテーブルと同じスキーマのテーブルを作成
元のテーブルと同じスキーマの空テーブルを作成します。新テーブル名はデフォルトで先頭にアンダースコア_がついた名前となります。CREATE TABLE ... LIKE文でも元のスキーマを保持したテーブルを作成できますが、この場合は元のテーブルに存在していた外部キー制約は保持されないので、pt-oscが独自にSHOW CREATE TABLEの結果を解釈してCREATE TABLE文を作成しています。
2. 新しいテーブルに対してALTER TABLEを実施
1.で作成された新テーブルに対してALTER TABLEを実行し、スキーマを反映させます。新規テーブルにはまだレコードがなく読み書きもないので、この処理は一瞬で終わります。
3. 既存テーブルと新しいテーブル間でレコードを同期するためのトリガーを作成
元のテーブルに対して書き込みが行われた場合に、その変更を新テーブルに反映させるためのトリガーを作成します。INSERT, DELETE, UPDATEそれぞれの更新操作について作成し、トリガーは元のテーブルに対して設定されます。
4. 元のテーブルのレコードを新しいテーブルにコピーする
トリガーは新規の書き込みについて新しいテーブルに反映しますが、元のテーブルにある既存のレコードはpt-oscによってコピーされます。レコードではINSERT文が発行されますが、具体的には次のような特徴があります:
INSERT LOW_PRIORITYが指定されます。LOW_PRIORITYは、対象のテーブルが誰からも読み込まれていないことを確認してからINSERTされます。ただし、この挙動はMyISAMのようなテーブルロックのみを行なうストレージエンジンで有効です*4IGNORE INTOが指定されるので、INSERTでエラーがあった場合はそのレコードをINSERTしないまま後続の処理が行われます。
たとえばpt-oscでなんらかの制約を追加するような変更を加えたい場合、元のテーブルに制約違反となるようなレコードがある場合はコピーされませんが、後続のコピーは実行されるので、テーブル間で整合しているかの確認は必要になるおそれがありますLOCK IN SHARE MODEが指定されているので、元のテーブルに対して共有ロックをかけます- チャンクという複数のレコードをまとめた単位でBulk Insertするイメージです
- 元のテーブルについて、原則
PRIMARY KEYまたはUNIQUE KEYが設定されている必要があります。Ruby on Rails（以降Railsとよぶ）の場合はデフォルトの挙動でPRIMARY KEYとしてidカラムが割り当てられます
5. 子テーブルがある場合に外部キー制約を更新する
レコードのコピーが最後まで終わり次第、このステップに入ります。
元のテーブルについて、そのテーブルに対する外部キー制約の有無から子テーブルがあるかどうかをINFORMATION_SCHEMA.KEY_COLUMN_USAGEから探しに行きます。
子テーブルがある場合は、外部キー制約に対する親テーブルを新しいテーブルに向けなおします。
6. 元のテーブルと新しいテーブルをスワップする
RENAME TABLEによって元のテーブルと新しいテーブルをアトミックに入れ替えます（テーブル名を入れ替えるだけ）。デフォルトの挙動では、入れ替える前に新しいテーブルに対してANALYZE TABLEを実行し、InnoDBの統計情報を更新しておきます。
7. ALTER TABLE実施前のテーブルを削除する
不要になったほうのテーブルを削除し、トリガーもあわせて削除します。
以上がpt-oscの一連の流れです。pt-osc実行時のオプション指定によって挙動を変えられる部分もあるので、詳細は公式ドキュメントを参照ください。
freeeにおける導入時の検討事項
pt-oscを本番環境に導入する前に、運用や負荷などの面で検討事項が挙がりました。そのうちのいくつかを紹介します。
pt-oscとgh-ost
pt-oscの類似ツールとして、GitHubがgh-ostというツールを公開しています。
別のテーブルを用意して入れ替える思想は同じですが、レコードコピー中の書き込みにおける差分について、pt-oscがトリガーで同期していたところをgh-ostはバイナリログで同期します。設計の詳細は以下のドキュメントで確認できます。
gh-ostを使ううえでバイナリログの形式は原則ROWでなければなりませんが、freeeでは運用の都合上MIXEDを採用しています。ROWで出力するようなレプリカを作成し、そちらからバイナリログを取得する方法も紹介されていますが、レプリカを用意するぶんの金額的なコストが懸念となります。そのため、まずは導入コストが比較的低いpt-oscを採用し、トリガー同期による負荷が顕著に現れたらgh-ostの導入を再度検討することにしました。
DDLとメタデータロック
MySQLではデータの一貫性のため、DDL実行開始時にメタデータを獲得します。ここで以下の例のように、メタデータロックの獲得待ちの間にDMLが待たされるおそれがあります。
- DMLがテーブルAの読み書き（または読み込みのみ）を行なうトランザクションを開始する
- テーブルAに対して
ALTER TABLEを行なうDDLが発行されるが、Waiting for metadata lockとなり、1.のトランザクションがCOMMITまたはROLLBACKされるまで待つ - DDLが発行されたあとにテーブルAの読み書き（または読み込みのみ）を行なうDMLが発行されるが、こちらも2.のDDLが開始されるまで
Waiting for metadata lockで待たされる - 1.のDMLが
COMMITまたはROLLBACKされると、2.のDDLが開始し、後続の3.のDMLが処理される
ここで、Waiting for metadata lockの最大待ち時間はlock_wait_timeoutパラメータで決定され、デフォルトは1年です。この値を1年未満に設定したとしても、Webサーバのレスポンスタイムの低下に影響をおよぼすため、お客様からみた場合にある機能の処理が遅くなることになります。
サービスレベルを定めていればlock_wait_timeoutをそれに応じて変更する必要があり、単位時間あたりのリクエスト数やテーブルへの書き込み回数、またはトランザクションの実行時間が時刻によって変動する場合は、それらの負荷が少ない時間帯にDDLを実行するなどの対策が考えられます。
freeeはtoBサービスであり、平日の日中は負荷が高く夜間や休日は低い傾向にあります。そのため、データベースに関するスキーマ変更操作は原則夜間に行なっており、pt-oscを使う際も同様にトリガーの作成や削除・テーブルのスワップといったDDLを含む操作をそれぞれ分割し夜間に行えるようにしています。pt-oscではデフォルトでは前述の一連の手順をすべて即時実行しますが、いくつかのステップで中断し、後続のステップ開始時刻を作業者がコントロールできるようにしています。
トリガー作成時のメタデータロック
CREATE TRIGGERの実行時も、コピー元のテーブルのメタデータロックを獲得します。このときのlock_wait_timeoutはpt-oscがセッション単位で設定し、デフォルトは60秒です。ロック獲得待ちのために最大60秒ユーザリクエストが待たされるのを許容できない場合は、--set-varsオプションで上書きできるので、設定しておきましょう。
また、lock_wait_timeoutを超えてDDLが失敗した場合は、pt-oscが自動で再試行します。デフォルトは1秒間隔で10回再試行し、こちらは--triesオプションで上書き可能です。こちらもテーブルの読み書きが多く失敗する確率が高い場合には調整を検討してみてください。
レコードコピー中断時のpt-oscの挙動
pt-oscはデフォルトでは以下の条件でレコードの停止および中断を行ないます:
SHOW GLOBAL STATUSで得られるThreads_running（現在スリープ状態でない、アクティブなスレッド数）が25以上の場合はレコードコピーを一時停止するThreads_runningが50以上の場合はpt-osc自体を中断する
これらの値はそれぞれ--max-loadおよび--critical-loadオプションで変更可能（Threads_runningでなくほかの統計情報を使用するように変更することも可能）です。普段のThreads_runningの傾向やレコードコピーを事前に負荷試験し、必要に応じて値を調整してください。
--critical-loadを超えたりpt-oscに対してシグナルを送信して中断させた場合は、中途半端に残った新テーブルやトリガーを自動で削除してから終了します。トリガーを削除する場合もまたメタデータロックに気を配る必要があります。レコードコピーが日中まで続いたとき、そこで中断したときにメタデータロック獲得待ちが発生するとユーザ影響が大きいと判断したため、--no-drop-triggersオプションを指定してトリガーの削除を抑制するようにしています。これにより、落ち着いた夜間帯に別途DROP TRIGGER <trigger_name>;を実行することで、ユーザ影響を抑えるようにしています。
新テーブルを削除する場合、テーブルサイズやデータベースの設定によっては削除の負荷が非常に高まるおそれがあります*5。そのため、--no-drop-new-tableオプションによってDROP TABLEを抑制し、中途半端に残ったテーブルの削除は別途行なえるようにしています。
レコードコピー終了時の挙動
レコードコピー終了時のRENAME TABLEにおいてもメタデータロックが発生します。レコードコピーの開始から終了までの時間はそのときのデータベースの負荷によって変わるため、見積もることが困難でした。そのため、日中にレコードコピーが終了し、そのままRENAME TABLEが実行されてリクエストが詰まるといった状況を回避するため、--no-swap-tablesによってテーブルスワップを即座に実行させないようにしています。
その結果、RENAME TABLEとDROP TRIGGERをpt-oscの外で行なうことになるため、こちらは別途スクリプトを用意して実行させています。古いテーブルの削除は、負荷の観点から--no-drop-old-tableオプションを指定することで行なわないようにしています。
チャンクの調整
レコードコピー時のCPUや書き込み、レイテンシーのような負荷の増加が大きな懸念点となったため、レコードコピーに伴う負荷試験は事前に行いました。調整可能なパラメータは以下のようなものが用意されています:
--sleep N: チャンクごとのコピーの間にスリープを挟む。Nの単位は秒--chunk-size N: チャンク内のレコード数の調整を行なう。Nのデフォルトは1000だが、後述のチャンクタイムをもとにチャンクサイズを動的に変更させられる。--chunk-sizeオプションを明示的に指定した場合は動的なチャンクサイズの変更が効かない--chunk-time X: チャンク1つあたりのコピーにX秒かかるよう、チャンクサイズを動的に変更する（ソースコード）。Xのデフォルトは0.5
これらをそれぞれ変化させて、レコードコピーで単純にどれくらいの負荷が増加するかを調査することができます。実際にはほかのトランザクションによる読み書きやロックなども含まれるため、あくまで参考程度にとどめています。
レコードコピーの実行時間を早めたい場合はチャンクサイズを大きくすることである程度の効果はありますが、時間的な要件がなければチャンクタイムを延ばしたりスリープを入れることでデータベース全体の負荷低減に繋げることもできます。
レコードコピーにおけるコピー先テーブルでのロック競合
実際に本番環境で発生した問題として、レコードコピーの際にコピー先テーブルでロック競合が発生し、pt-osc側のINSERTが失敗することがありました。コピー先テーブルへの書き込みはトリガーとpt-oscプロセスの2つしかないので、これらがかち合ったことになります。
ロック競合が発生しうる状況としては、次のような例が考えられます:
- テーブルにおいて範囲をとるような
UPDATEやDELETEの更新系クエリを実行する。トリガーによって新しいテーブルで同様の書き込みがなされる。範囲内でギャップロックが発生する - pt-oscが1.のギャップロックの範囲に含まれるレコードをコピーしようとする。更新系クエリが完了しギャップロックが解放されるまで待機
- 2.が
innodb_lock_wait_timeoutの値を超えて待機したため、2.が終了する
innodb_lock_wait_timeoutはpt-oscによってセッション単位で指定されており、デフォルトは1秒です。こちらも、読み書きが多いテーブルにおいてロック競合が発生する頻度が高いと予測される状況では、先述のlock_wait_timeoutと同様に--set-varsで上書きしたり--triesで再試行回数を増やしたりするとよいでしょう。
ANALYZE TABLEの実行やバッファプールに新テーブルのレコードを乗せるかどうかの検討
こちらは環境に依存するので一概にいえませんが、ANALYZE TABLEの有無でEXPLAINの結果やレイテンシーの改善に寄与するかどうか、および新テーブルのレコードをバッファプールに乗せることで同様の改善がなされるかどうかは確認しておいてもよさそうです。
ANALYZE TABLEはテーブルサイズが大きいとそれなりに負荷のかかる処理です。pt-oscをデフォルトのまま使うとしても、--no-analyze-before-swapオプションを指定することでテーブルスワップ前のANALYZE TABLEを省略できるので、検討してみてください。
freeeにおけるRailsアプリケーションとpt-oscの併用例
ここまで、pt-oscを実行するうえでの注意事項を紹介してきました。ここからは、Railsアプリケーションにおけるpt-oscからのスキーマ変更の例を紹介します。pt-oscを実行しただけではRailsのスキーマファイルdb/schema.rbと整合しないため、帳尻を合わせる必要があります。Railsアプリケーション上の変更を含めた、freeeにおける一連の手順の例を以下に示します。
なお、作業対象のデータベースはAmazon Aurora（MySQL 5.7互換）で、Writer-Reader構成となっています*6。
1. pt-oscを実行し、スキーマ変更済みのテーブルを作成し、レコードをコピーする
リクエストの少ない時間帯を見計らってpt-oscを実行し、トリガー作成とレコードのコピーを行ないます。以下の操作を行なうようなラッパースクリプトを用意しました。
--no-swap-tablesを指定してコピーが終わってもテーブルスワップを行わないようにします--no-drop-new-tableを指定して、コピーが終わっても新しいテーブルが削除されないようにします--charset=utf8を指定して、文字化けしないようにします--no-drop-triggersを指定して、コピーが終わったり中断してもトリガーが削除されないようにします- その他負荷調整のパラメータを必要に応じて設定します
2. コピーが完了したことを確認し、適切なタイミングでテーブルスワップ・トリガー削除を実行する
pt-oscによるテーブルスワップを行なわないようにしたので、別途スワップ操作を行なう必要があります。こちらも直接SQLを発行するようなラッパースクリプトを呼び出して実行しています。pt-oscが行なっていたようなリトライ処理やSET SESSIONによるシステム変数の設定も別途実装します。
3. db/migrate/*.rbを用意しておき、そのバージョンをあらかじめINSERTする
Railsの場合は、db/migrate/以下にデータベースマイグレーションのファイルを用意し、rails db:migrateコマンドを実行することでデータベース側のテーブル定義とdb/schema.rbの更新が行なわれます。db/migrate/以下の各ファイルは日時でバージョン管理されており、どのバージョンのファイルが実際に適用されたかはデータベース側のschema_migrationsテーブルのversionカラムにそれらのバージョンが格納されています。
pt-oscで行なったスキーマ変更と同じ動作となるようなマイグレーションファイルを作成しておきますが、このファイルを動作環境に適用させる前に当該ファイルのバージョン情報をschema_migrationsにINSERTしておきます。これにより、すでにマイグレーションを行なったとみなし、コードのデプロイ時におけるrails db:migrateではなにも起こらないようにします*7。
なにも起こらないということはファイルの内容と実際のスキーマが異なっていても無視されるため、マイグレーションファイルのコードレビューはとくに気を配る必要があります。
4. db/migrate/*.rbを含むコードをデプロイする
作成したdb/migrate/*.rbとdb/schema.rbをデプロイします。これにより、Railsアプリケーションからスキーマ変更後の内容でテーブル操作を行なうことができます。
実際に作業を行なった所感
上記手順をステージング環境で実施し、問題ないことを確認してから本番作業で同様の作業を実施しました。
歴史の長いRailsアプリケーションではidカラムの型がINTEGERの場合があり*8、発番されたidがINTEGERの上限値を超えるおそれがあるためBIGINT化する必要がありました。カラム型の変更はALGORITHM=COPYであるため通常はオンラインで実施できませんが、この変更作業をpt-oscを用いて完全無停止で完了させました。作業対象テーブルはデータベース内でトップクラスのテーブルサイズだったため、今後発生しうるスキーマ変更に関する操作のほとんどはメンテナンスによる停止時間なしに実施可能になることが期待されます。
所感や作業時に気をつけた点は以下のような具合です:
- Amazon Auroraにおいてレプリケーション遅延が顕著に増加することはなく（高くても100ms前後に収まった）*9、pt-oscで遅延をチェックする必要はないが、Auroraインスタンスでない場合はLagの増加に注意する必要がある
- もともと読み書き量が多いテーブルに対する作業だったので、夜間作業でもロック獲得待ちが頻繁に発生した。読み書きが比較的少ないテーブルで事前に動作確認した際は問題にならなかったため、当日の試行錯誤が大変だった
- 作業時はMySQLサーバで実行中のスレッド（
SHOW PROCESSLISTで得られるような内容）をリアルタイムに監視して、どのくらいクエリが発生し作業中にほかのクエリが待たされているかを注視した - pt-oscに限らずOnline DDL全般の話だが、Amazon AuroraでWriter-Reader構成の場合に、Reader側でメタデータロックが発生しない代わりに対象テーブルを読んでいるトランザクションがKillされる点に注意*10
- pt-oscによるレコードコピーが数日におよぶ可能性があり、コピーが中断した場合は最初からやり直すことになるため、コピーをやり切るためのインフラ（たとえば作業環境がタイムアウトなどでシャットダウンしないようにする）やパラメータ調整などを行なう必要がある
- カラムの型変更やInstantでない
ADD COLUMNは概ねpt-oscによる実行で問題ないが、DROP COLUMNする場合はRailsアプリケーションで対象カラムを操作しないよう事前にignored_columnsを設定する必要がある - データベースのマシンスペックなどによるが、pt-oscによる負荷やレスポンスタイムの変化はなかったため、運用に乗せても問題ないことを確認できた
まとめ
pt-online-schema-changeを導入するにあたり、社内での検討事項や運用方針を紹介しました。これから使ってみたいという方の参考になれば幸いです。
いくつか先行事例に関する記事もあり、導入の際に非常に参考になりました。この場を借りて感謝いたします。
freeeではデータベースに関心があるエンジニアを募集しています。DBREの取り組みに興味を持った方はぜひお話を聞かせてください！
*1:たとえばADD COLUMNが該当し、その挙動の解説はMySQLのInstant ADD COLUMNをちゃんと調べてみる - y-asaba@hatenablogが参考になります
*2:MySQL :: MySQL 8.0 Reference Manual :: 15.12.3 Online DDL Space RequirementsのTemporary log filesの項目を参照
*3:ユーザーにとって本質的な価値があると自信を持って言えることをすること。詳細は職場環境と制度 | freee株式会社 採用情報を参照ください
*4:https://dev.mysql.com/doc/refman/8.0/en/insert.html より、"LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE)." と言及されています
*5:InnoDBバッファプールサイズが32GBよりも大きい場合にDROP TABLEによってデータベース全体で処理が待たされる場合があります。テーブルサイズが大きいほど顕著で、この挙動はMySQL 8.0.23で修正されています
*6:確定申告を乗り越えるDBパフォーマンス改善 - Aurora 移行の舞台裏 - freee Developers Hubにて、弊社データベースをRDS for MySQLからAmazon Auroraへ移行した事例を紹介しています
*7:freeeでは安全のため、アプリケーションコードのデプロイとスキーマファイルのデプロイはタイミングを分けて行なう運用をとっています
*8:Rails 5.1からデフォルトでBIGINTとなっています（https://github.com/rails/rails/pull/26266）。Rails 5.1以前でも明示的にBIGINTを指定すればそのようにテーブルを作成することも可能です
*9:Auroraのレプリケーションはバイナリログでなくストレージ自体を共有しているため、もともとレプリケーション遅延は抑えられています
*10:この挙動の解説はAmazon Aurora レプリカ では metadata lock 待ちが発生しない - mita2 database lifeが参考になりました
