MySQL Online Schema Changes で考える Database Reliability Engineering と制約

立命館大学の公認プログラミングサークル「watnow」B3の金久保です。(謝謝)
今日はこの辺の話を持ってきました。

はじめに

対象読者にについて

• (必須) DDLとは何か・どのように動作するのか一定理解している
• (推奨) 企業でソフトウェア開発したことがあり、マイグレーションの課題を把握している
• (推奨) Online Schema Changesの導入を検討したことがある (もしくは現在検討している)

本記事では、以下の前提で説明しますので留意ください
MySQL: Version 8.0
ライブラリ: pt-osc / gh-ost / spirit

マイグレーションにおける課題

近年のSaaS開発において、プロダクトにおけるダウンタイムは極力少なくすることが重要視されます。特に、24時間365日稼働するような基幹プロダクトには、瞬断NGなどの厳しいSLOが要求されるケースも増えてきました。故に、テーブルロックがかかるようなダウンタイムを伴うDDL (オフラインDDL) には慎重に打開策等を検討した上で、メンテナンス期間を設けてマイグレーションすることになります。

オフラインDDLには例えば、以下のようなケースがあります:

• 特定のスキーマのcollationを変更したい (COPY)
• スキーマを型変更したい (COPY)
• 特大サイズのテーブルにカラムを追加したい (INPLACE)

Online Schema Changesのコンセプト

上記の課題を解決するために登場したのがOnline Schema Changes (以下; OSC)です。この技術は、通常のDDLアルゴリズムでは、テーブルにロックがかかってしまうマイグレーション操作において、MySQLの機能を駆使してオンラインDDLを可能にするものです。

Online Schema Changesは「No Downtime Migration (途切れないマイグレーション)」をコンセプトにしており、故に多くのオフラインDDLに対して、OSCツールを導入することで、問題をシンプルに解決できます。その影響もあるのか、OSCが万能薬的な立ち位置で紹介される記事もしばしば存在し、技術的な制約事項に言及した記事があまりなく、導入可能性を探るための調査に大変時間がかかってしまいました。

そこで今回は、OSCの実行基盤を実現するにあたり、様々な調査と検討を行ったので本記事では備忘録的な文脈で、OSCについてわかった技術制約についてまとめました。

tl;dr

本記事の結論:

• カットオーバーにより回避できないテーブルロックと瞬断が発生する
• 外部キー制約を持つテーブルにはOSCが実行できない
• トリガーが仕込まれたテーブルにはOSCが実行できない
• 大きすぎるデータサイズのテーブルにはOSCが実行できない
• インフラの設定によってOSCが実行できないケースが存在する

(前提知識) MySQLのDDLアルゴリズム

この記事では解説を省略します。DDLについて理解したい人は、以下のレバテックの記事がすごく良くまとまっていたため読んでみてください。

当内容では説明コストを抑えるために、以下の部分を前提とします。(重要)

• MySQLでは３つのDDLアルゴリズムが存在する
• 特に指定がなければ以下で紹介している順に、INSTANT → INPLACE → COPYの優先度で評価され、対象のDDL操作が可能なアルゴリズムを採用する

アルゴリズム	INSTANT	INPLACE	COPY
実行速度	超高速	比較的速い	最も遅い
データ操作	メタデータのみ変更	テーブル構造をその場で変更	新しくテーブルを作成し、データを一行ずつコピーする
特徴	データ量に依存しない	通常オンラインDDLが可能	伝統的なDDLアルゴリズム
操作中のDML	可能	ほぼ可能	不可
制限	使用用途が限定的	一部非対応なDDL操作がある	オフラインDDLのみ

OSCツールとの関係性

OSCはオフラインDDLであるCOPYの問題点と、オンラインDDLであるINPLACEの問題点を解決するためのツールです。具体では以下の2つのケースに対応することを目的とします:

• COPYでのDDLによって、長時間のマイグレーションで大量のDML系エラーが発生すること
• INPLACEのDDLにおいて、膨大なデータサイズのコピーによりマイグレーション中にテーブルロックが発生すること

INSTANTアルゴリズムで実行可能な場合、OSCツールはMySQLのINSTANTをそのまま実行します。
以下はGithub製のOSCツール「gh-ost」の当該実装部分です:

本実装を読んでいくと、最初にINSTANTが実行可能か試みて、可能な場合はINSTANTアルゴリズムでクエリを発行し、実行不可能な場合は、OSCの仕組みでクエリが構築されるようになっていることがわかります。

トリガー型 vs バイナリログ型

MySQLのOSCにはOST方式で実行されるトリガー型 (pt-online-schema-change)と、MySQLのレプリケーションの仕様を使って、binlogでデータを同期していくbinlog型 (gh-ost/spirit)の2種類があります。

時系列的には pt-osc -> gh-ost -> spiritの順で登場しており、spiritが最新のOSCツールになります。

トリガー型は”オワコン”なのか？

トリガー型のpt-oscはMySQL5.5から存在し、後に新しいbinlogを用いた方式のgh-ostがリリースされています。トリガー型は、物理的なトリガーを配置する関係で並列DML対するロック耐性が弱いです。レプリケーションを利用したbinlogの方法では、過去の履歴(binlog)を用いるので、ロックの取り合いが起きづらいことと、トリガーを配置しないのでパフォーマンスが高いという利点があります。

ここまで聞くとトリガー型が下位互換なのではないかという話になるのですが、もちろんそうではありません。

例えばfreeeでは、OSCの導入にgh-ostと比較した上で、pt-oscを導入しています。良い記事だったのでこちらを参照して説明します。

gh-ostを使ううえでバイナリログの形式は原則ROWでなければなりませんが、freeeでは運用の都合上MIXEDを採用しています。ROWで出力するようなレプリカを作成し、そちらからバイナリログを取得する方法も紹介されていますが、レプリカを用意するぶんの金額的なコストが懸念となります。そのため、まずは導入コストが比較的低いpt-oscを採用し、トリガー同期による負荷が顕著に現れたらgh-ostの導入を再度検討することにしました。

記事の内容を抜粋すると、以下の部分で壁があるようです:

• レプリケーションが必要な分コストが高くつく
• binlogのフォーマットなど、インフラの設定に依存するため、そもそも導入できない可能性がある
• トリガー型は、MySQLの機能でトリガーを配置するだけなので簡単に導入可能

Online Schema Changesが受ける技術的な制約

カットオーバーによる瞬断

まず一番最初にカットオーバーについて触れるべきでしょう。カットオーバーは、OSCの最終段階で、新旧テーブル(マイグレーション前後)を入れ替える操作です。

An Example of Applying DDL to MySQL DBs with Special Structures
引用

具体的には、DDLが適用される新しいテーブルを既存テーブルと置き換えるため、RENAME TABLEクエリが実行されます。このクエリはXロックのためテーブルロックがかかり、この間のDMLは許可されません。データ量に依存しない処理のため、大抵1秒以内の瞬断に終わりますが、非同期でリクエストをリトライする仕組みがないクライアントの場合、処理がエラーとなってしまうことに注意したいです。

カットオーバーは必要性の高い制約である

技術的な制約として挙げているカットオーバーですが、個人的には旨みだと捉えています。COPYによるDDLの場合は言うまでもなく、INPLACEにおいても旨みがあります。

前述の通りINPLACEはデータサイズに依存したアルゴリズム (全てのデータをチェック) のため、大規模テーブルにおいてはテーブルロックの危険性があります。OSCツールはこれを解決し、カットオーバーのみの排他ロックに変換できるので、ロックリスクが大幅に低減することができます。

外部キー制約を持つテーブル

テーブルが外部キー制約を持っている場合、通常OSCは実行できません。参照される親テーブルが書き換わる際に子テーブルが持つ外部キーの参照整合性に引っかかるため、OSCがFKチェックを突破できないようになっています。repositoryでも「外部キー持ちテーブルには実行しないでくれ」という旨で注意書きされています。

外部キーとInnoDB

実際の状況を可視化してみました。

MySQLにおける外部キーはテーブル名ではなくテーブル自体に結びついています。そのためOSCの最後に行うカットオーバーにより、外部キーの参照関係は古いテーブルに取り残されます。親テーブルを入れ替える際、それを参照するすべての子テーブルの外部キーの定義を「一瞬で同時に」書き換える機能がMySQLのストレージエンジンであるInnoDBは存在しないので、必ず外部キーの問題でエラーを返すように設計されています。

InnoDBの根本的な原理のため、外部キー制約を持った状態でOSCする手法は存在しません。

また、もっと詳しい仕組みレベルの内容を、gh-ost開発者の一人であるShlomi Noach（シュロミ・ノアハ）氏が、外部キー制約に焦点を当てて解説している記事があるので、詳細が気になる方はこちらをご覧ください (もっといい内容が書いてあったので)

pt-online-schema-changeなら可能

OSCツールでは扱えないと前述しましたが、pt-oscでは外部キー制約があっても実行できます。--alter-foreign-keys-method=rebuild_constraintsオプションを加えることで対応できます。

オプション名の設定を見ればわかる通り、外部キーを削除して、新しい方のテーブルに新規で再構築するという手法を採用しています。そのため前述のInnoDBの問題をクリアしたわけではないのですが、削除して再構築するというALTER TABLEクエリの実行によって担保しています。

外部キー制約の追加・削除について

既存カラムに対して外部キーを追加・削除する操作はINPLACEが採用されます

https://dev.mysql.com/doc/refman/8.0/ja/create-table-foreign-keys.html#foreign-key-adding

様々な記事でpt-oscを用いた外部キーテーブルへのOSCが紹介されていますが、確認した限りMySQL5.6の話が多いです。MySQL8.0でも同様に実行可能なのかは調べていないので、仕様書を見てdeprecatedになっていないか各確認してください🙏

FKチェック無効化による突破は可能か？

また、外部キー制約を強行突破する方法としてFKチェック無効化という方法がしばしば利用されます。FKチェック無効化とは、一時的にFKチェックを無視することで外部キーをないものとして扱い、DDL中の制約条件を掻い潜る方法で、この方法を使うと外部キーカラムを追加する場合でもマイグレーション中にDMLを実行できます。詳細は以下の記事が参考になります。

FKチェック無効化では外部キー制約の問題を突破できません。
そもそもFKチェックとは、外部キーをなかったものにするものではなく、DML時に整合性を取らない手法なので、本題とはあまり関係がないです。そのため、当然ながら前述のInnoDBの問題を突破することは不可能です。

トリガーとの併用不可

対象のテーブルにトリガーが仕込まれている場合、OSCは実行できません。以下はspiritの内部実装で、マイグレーションが実行される前に、トリガーチェックを行っています。(gh-ostの場合も同様)

こちらから該当部分を抜粋:

// hasTriggersCheck check if table has triggers associated with it, which is not supported
func hasTriggersCheck(ctx context.Context, r Resources, logger *slog.Logger) error {
	sql := `SELECT * FROM information_schema.triggers WHERE 
	(event_object_schema=? AND event_object_table=?)`
	rows, err := r.DB.QueryContext(ctx, sql, r.Table.SchemaName, r.Table.TableName)
	if err != nil {
		return err
	}
	defer rows.Close()
	if rows.Next() {
		return errors.New("tables with triggers associated are not supported")
	}
	if rows.Err() != nil {
		return rows.Err()
	}
	return nil
}

これは、OSCの仕組みである「新旧のテーブル同期」において、OSCはトリガーと独立して動作するため、DMLで発火した旧テーブルのイベント結果を新テーブルに反映できないという欠陥を防ぐためのものです。またトリガーとOSC間でロックの取り合いになる状況を避けると言う観点からも、OSCとトリガーが同時に存在しないことで対策をとっていると思われます。

トリガーの削除要件を確認する

そのためすでにトリガーが仕込まれているテーブルの場合は、トリガーを事前に削除してからOSCを実行する必要があります (削除できないようなトリガーがある場合はOSCは適用できません)。

Insertによって発火するトリガーの場合は以下のように、DDL前後でトリガーを一時的に削除・再構築する対応が考えられます:

この構成では、マイグレーション中にトリガーによって発火されるべきレコードを保持して、マグレーション後に、対象のデータコピーをショットで行うというワークフローを考えることができます。しかし、これも結果整合性となるため、リアルタイムで必要という要件の場合、いずれにしても検討が必要になります。

必ず要件を満たすか確認しましょう。

データサイズに依存した実行可能性

OSCツールはデータコピーの性質上、テーブルのデータサイズに実行可能性が依存することになります。pt-oscやgh-ostなど、MySQL5.6から存在するOSCツールでは、高々1TiB程度のデータサイズになると、パフォーマンスが極端に低下し、途中停止する挙動がリポートされています。

Table/Scenario	Gh-ost	spirit
finch.balances (800MB/1M rows), idle load	28.720s	11.197s
finch.balances (800MB/1M rows), during bench	2:50m+	~15-18s

これに対しspiritでは、データサイズが10TiBのテーブルのマイグレーションを1週間で終わらせることを、パフォーマンス基準としています。このことから、レコード数が億を超えるようなテーブルに対してのOSCについては、pt-osc/gh-ostを選択することは難しく、spiritを利用する必要があります。

Online Schema Changesが受ける環境的な制約

環境的な条件、すなわちインフラ要因で実行できないケースもあるので紹介しておきます。

binlogとレプリケーションの実行基盤

gh-ostとspiritは、OSC内でMySQLのbinlogとレプリケーションの機能を使います。ここで問題になるのが、インフラ・クラウド側の設定です。(gh-ostとspiritは実行条件が同じのため一緒くたにして話します。)

freeeでの前述があった通り、binlogフォーマットの設定やレプリカの兼ね合いがあるので、自身のプロダクトが利用しているMySQLインスタンスの設定が実行可能な状態になっているか・実行可能な環境に変更できるのか、をインフラの設定を見て判断してください。

例えば上記のリンクの通り、RDS/Amazon Auroraについては一定のlimitationが設けられています。インフラのレプリケーション機構によっては、特定の機能が使えないケースがあります。(詳細は省略します[インフラの説明をちまちま書き綴らないといけなくなるので🙏])

DBユーザーの権限不足がないか

インフラの設定によりますが、通常プロダクトにおいてはDBユーザーの権限は絞っているはずなので、必要な権限が揃っているかは各自で確認してください。具体的には以下の権限が必要です。

権限	定義	用途
RELOAD	サーバーの操作やログファイル管理、キャッシュクリアなど、システム設定の再ロード実行を許可する。	FLUSH TABLES, FLUSH LOGS, FLUSH PRIVILEGES, ログファイル管理
REPLICATION SLAVE	ユーザーがレプリケーションスレーブとしてマスターに接続し、バイナリログ（binlog）を読み込むことを許可する。	マスターからのデータ複製（レプリケーションの実行）
REPLICATION CLIENT	SHOW MASTER STATUS や SHOW SLAVE STATUS など、レプリケーション関連のステータス情報を確認することを許可する。	レプリケーションの状態監視、ステータスチェック

この辺の権限をapplicationが使うDBユーザーに渡してしまうと権限過剰になってしまうので、ユーザーを分けるなどの対応を念頭においておきましょう (間違ってもSUPERは与えないように！)

さいごに

今回は、MySQL Online Schema Changesの導入時に、調査して分かった課題点をまとめました。もちろん超有効なツールなことに変わりはないのですが、実態として導入に要する工数・コストが決して軽くはないことがお分かりいただけたと思います。

サクッと検証して導入できるようなものではないので、その点ご留意と検討を行った上で慎重に本技術を精査していただけたらと思います。