15億件以上入ったテーブルのスキーマをサービス無停止で変更した

こちらは、Akerun Advent Calendar 2023 18日目の記事です。

はじめまして、今回はWEBエンジニアの @yuto_a が担当させてもらいます。

今年の春から夏にかけて、15億以上のデータが入ったMySQLテーブルのスキーマ変更をサービス無停止で実施しました。今回はこの対応を実施した私が、そもそもなぜサービス無停止でのスキーマ変更が必要だったのか、どのようにサービスを止めずにスキーマ変更したのか、実際に使用したときに工夫したポイントについて共有します。

サービス無停止でスキーマ変更した理由

そもそもなぜサービス無停止でのスキーマ変更が必要だったのか？

我々のサービスでは、APIサーバーに Ruby on Rails (以下、Rails) を使っており、データベースには AWS Aurora MySQL 5.7互換を使っています。このアプリケーションは Rails のバージョンが低いときに作られ、アップデートを重ねて今では Rails 6.1 で稼働しています。

Rails 5.0 以前に作られたMySQLテーブルのほとんどは、PKであるidカラムの型がINTEGERとなっています。というのも、Rails 5.1 で主キーのデフォルト型が BIGINT に変更され、それより前はデフォルト型がINTEGERであったためです。そのため、主キーが INTEGER型になっているというのは、歴史の長い Rails アプリケーションではわりとある話ではないかと思っています。

railsguides.jp

では、idカラムの型がINTEGERだと何が問題なのか？

INTEGER型の上限値は約21億 (2³¹-1) であり、その上限値に到達した場合は新たなレコードが作成できなくなってしまいます。実際、我々の使っているテーブルでデータ件数が15億に到達し、年2-3億のペースで増えているため、数年以内に上限値に到達してしまう状況でした。

そこで我々は idカラムの型をBIGINT UNSIGNEDに変更することで上限値到達のリスク回避を行いました。型を INTEGER から BIGINT にすることで、上限値は約21億 (2³¹-1) から約920京 (2⁶³-1) になります。さらに id カラムはAUTO_INCREMENTで増え続けるだけで負の値は不要であるため、BIGINT UNSIGNED にすることで、上限値は約1800京 (2⁶⁴-1) まで引き上げられます。

ですが、MySQLにおいてカラムのデータ型変更にはテーブルの再構築が必要で、その間テーブルがロックされます。 (MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations)
先にも書いた通り15億件以上ものデータが入ったテーブルがあり、そのテーブルに対して普通にALTER TABLEでスキーマ変更しようとすると、サービスが長時間停止してしまいます。しかし、我々はスマートロックのサービスを提供しており、サービス停止により鍵の開閉等に影響がでてしまうため、サービスの停止時間は可能な限り短くしなければなりません。ためしに見積もったところ数時間程度の計画メンテナンスの時間に収まらないことが予想されたので、普通に ALTER TABLE でスキーマ変更する方法は選択肢から外れました。

そのため、サービス無停止でのテーブルスキーマ変更が必要でした。

サービス無停止でスキーマ変更した方法

では、どのようにサービスを止めずにスキーマ変更したのか？

結論からいうと、pt-online-schema-change というツールを使いました。このツールは、Percona社が開発しているMySQLのツール群 (percona-toolkit) に同梱されているツールの一つです。 MySQLのDDLをオンライン実行するためのツールで、中身は Perl で書かれたスクリプトです。

github.com

以下のような理由で、pt-online-schema-change に決めました。

サービスを止めることなくスキーマ変更が可能
我々の環境で使用可能 (Aurora, MySQL 5.7)
他社での実績がブログ等で公開されていて参考にできる
インストールが簡単
バイナリログの設定変更が不要 (似たツールで gh-ost というのがあり、そちらはバイナリログを使うため、バイナリログの設定に条件あり)

pt-online-schema-change がやっていること

pt-online-schema-change がどのようにDDLのオンライン実行を実現しているか説明します。

スキーマ変更対象 (以下、旧テーブル) と同じスキーマの新規テーブル (以下、新テーブル) を作成します。
新テーブルに対して、DDLを実行します。この時点で新テーブルは空なので、DDLは一瞬で完了します。
旧テーブルから新テーブルへデータの変更を反映するための、トリガーを作成します。このトリガーによって、旧テーブルへのデータの作成・削除・更新が新テーブルへ反映します。
既存のデータをコピーするため、旧テーブルから読み出し、新テーブルへ書き込む処理が実行されます。
4.のデータコピーが完了すると、新テーブルと旧テーブルをRENAME TABLEによって入れ替えます。これによって、元のテーブル名で新しいスキーマのテーブルが利用できるようになります。
後片付けとして、旧テーブルの削除とトリガーの削除が行われます。

上記のような手順を経て、テーブルに長時間のロックをかけることなく、スキーマを変更することができます。

しかし、気を付けるポイントもいくつかあります。

大量の読み込み/書き込みを行うので、CPUやIO等の負荷はそこそこ上昇する
トリガーの作成/削除、テーブル入れ替え時にロックがかかる
トリガーによるオーバーヘッド
リードレプリカがある場合、それらの遅延
外部キー制約がある場合、それらの対応を考える必要がある (というオプションで指定)
新たに UNIQUE制約を加える場合、データ消失のリスクがある

気を付けることは何点かありますが、基本的にはツールを起動してしばらく待っているだけでスキーマ変更が完了するので、非常に便利なツールです。

実際に pt-online-schema-change を使用してスキーマを変更

実際に本番環境のMySQLテーブルのスキーマ変更をするときに、次の点を工夫しました。

本番環境に近い環境での動作検証
サービスの特性に合わせた移行戦略

実際に本番環境のMySQLテーブルのスキーマ変更を実施する前に、本番環境に近い環境として本番環境のMySQLのクローンを用意し、そこで動作検証を行いました。クローンの作成については、AWS Aurora を使っていれば非常に簡単で、クラスターを選択して「アクション」から「クローンの作成」を選ぶだけでした。また、検証後の後片付けも簡単でクローンしたクラスターを削除するだけです。

クローンしたDBで pt-online-schema-change を使ってみて、ツールの使用感やツール実行中の負荷状況、実施にかかる時間等を調べました。また、各種オプションの指定方法や動きを確認したり、リードレプリカの遅延等の影響も確認しました。これらの動作検証により、DBインスタンスのCPU使用率が最大で50%程度上昇することがわかりました。

我々のサービスはその特性上、日中のトラフィック量が大半を占めていて、夜間は極めて少なく、それに比例してDB等のリソース使用率も変動し、夜間のリソースにはかなり余裕があります。

そのことを踏まえて、夜間のトラフィック量が少ないときのみ pt-online-schema-change によるデータコピーを実施し、日中は停止しておくという方法を採用しました。

具体的な方法としては、pt-online-schema-change の一時停止オプションとcronを組み合わせて、シンプルに実現しました。 pt-online-schema-change には一時停止用のオプションとして、--pause-fileというオプションが用意されています。このオプションではファイル名を指定し、指定したファイルが存在している間は、pt-online-schema-change によるデータコピーを停止するというものです。 (トリガーによるデータの反映は継続します。) このオプションで指定したファイルを、cronを使って作成したり削除したりすることで、pt-online-schema-change によるデータコピーの実行時間帯をコントロールしました。

cronは以下のような設定を使いました。

00 06 * * * touch /tmp/ptosc-pause-file
00 00 * * * mv /tmp/ptosc-pause-file /tmp/ptosc-pause-file-old

先述の動作検証含めしっかりと準備をした上で、本番環境のDBに対して pt-online-schema-change を使ったスキーマ変更を実施しました。データ量がそこそこあること、夜間のみの稼働としたことで、1週間程度の時間が必要でしたが、無事に完了することができました。これにより、idカラムがBIGINT UNSIGNEDになり、上限値到達のリスクを回避することができました。

まとめ

pt-online-schema-change を使えば、簡単にオンラインスキーマ変更が可能
CPUやIO等にそこそこ負荷がかかるので、そこはケアする必要がある
一時停止オプションがあるので、トラフィック状況に合わせて稼働させる時間帯を調節した

株式会社フォトシンスでは、一緒にプロダクトを成長させる様々なレイヤのエンジニアを募集しています。 photosynth.co.jp

Akerunにご興味のある方はこちらから akerun.com