大規模サービスのAmazon Aurora MySQLのテーブル変更で直面した３つの課題

はぜ

開発

はぜ

最終更新日2025/12/25 投稿日2024/12/04

この記事はレコチョク Advent Calendar 2024 の4日目の記事となります。 

はじめに

私は主に音楽サブスクリプションサービスのバックエンド開発を担当しています。今回は、大規模サービスならではのAmazon Aurora MySQLのテーブル変更で直面した３つの課題についてご紹介します。

背景・環境

対象のサービスは、会員数が100万人規模の音楽サブスクリプションサービスです。 2024年10月中旬に大型アップデートに伴うAPIをリリース予定でした。 APIをリリースするにあたり、事前に新規テーブルの作成や既存テーブルのカラム修正を行う必要がありました。これまでもAPIのリリースやテーブル変更は幾度となく行っていましたが、今回のリリース規模は前例がありませんでした。このリリースを行うにあたり、いくつかの課題があったためご紹介します。同じ問題を抱えている方の参考になれば嬉しいです。

稼働中の環境

EC2
RDS
- エンジンバージョン：5.7.mysql_aurora.2.11.3
数千万から数億レコードのテーブルが複数存在しています。

当初の実行方針

当初は、以下のスケジュールを想定していました。

１. サービス全体をメンテナンス状態にする２. ユーザからのアクセスが来なくなったことを確認３. 稼働中のDBに対して直接CREATE TABLEやALTER TABLEを実行

CREATE TABLE：約150テーブル実施
ALTER TABLE：約200テーブル実施

４. APIのリリース５. 動作確認６. メンテナンスを解除

課題1: メンテナンス時間が長すぎる

事前にメンテナンス時間をユーザに告知する必要があったため作業の見積もりを行いました。本番同等のスペックやデータを用意し、テーブル作成やテーブルのカラム変更を行いました。しかし、予想以上にカラム変更に時間がかかり、最終的に約8時間もの間メンテナンスを行う必要があることが判明しました。ユーザ影響が大きすぎると判断し、スケジュールを見直すことに決めました。

課題1の解決策

作業時間を短縮できる部分を検討しましたが、頭打ちの状態でした。メンテナンス方法を抜本的に変える必要があると判断し、サービス内での作業実績がない Blue/Green Deploymentsを検討することにしました。

検討した結果、メンテナンス時間は約3時間で済むと判断しました。そして、新たに決定したスケジュールが以下です。

１.リリース当日までに

Blue/Green環境構築
Green環境のDBに対してCREATE TABLEやALTER TABLEを実行

２. リリース当日

サービス全体をメンテナンス状態にする
トラフィックを切り替え
APIのリリース
動作確認
メンテナンス解除

Blue/Green Deploymentsの概要

詳しい内容はたくさんの記事があるのでここでは簡単にご紹介するまでに留めます。

Blue/Green Deploymentsは、稼働中のDBに対して直接CREATEやALTERを行うのではなくあらかじめ新しい環境を用意しCREATEやALTERを行った後、トラフィックを新しい環境に切り替えます。これによりダウンタイムを最小限に抑え、問題が発生した場合はすぐに古い環境に戻すことができます。

一般的な流れは以下の通りです。１. Blue環境を作成２. Green環境を作成３. Green環境上でテスト４. テストが問題なければトラフィックをGreen環境に切り替え５. Blue環境を削除

課題2: Blue/Green環境作成後、環境間のデータ同期が取れていない

Blue/Green環境を無事構築し、Green環境のDBに対して新規テーブル作成とカラム変更を行っていました。まず1テーブルのみint型をbigint型に修正するDDLを実行し、どのくらい時間がかかるか計測しました。

ALTER TABLE `テーブル名` MODIFY COLUMN `カラム名` bigint unsigned NOT NULL;

しかし、上記DDLを実行完了後30分経過してもBlue環境のデータがGreen環境に同期されていない問題が発生しました。

課題2の原因と解決策

Green環境で以下のコマンドを実行し、スレーブの状態を確認しました。 (MySQL 8.0.22 以降では、SHOW SLAVE STATUS は非推奨になり、かわりに SHOW REPLICA STATUS の使用が推奨されています)

MySQL 5.7：https://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.html MySQL 8.0：https://dev.mysql.com/doc/refman/8.0/ja/replication-administration-status.html

SHOW SLAVE STATUS\G

(出力結果は一例)

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxx
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-xxxx
          Read_Master_Log_Pos: 25877182
               Relay_Log_File: relaylog.xxxx
                Relay_Log_Pos: 63380587
        Relay_Master_Log_File: mysql-bin-changelog.xxxx
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: xxxxx
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 63380354
              Relay_Log_Space: 160096810
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 775
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: xxxx
                  Master_UUID: xxxx
             Master_Info_File: mysql.xxxx
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for table metadata lock
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

多数の項目がある中で特に次の項目に注目しました。

Slave_IO_Running / Slave_SQL_Running
- Slave_IO_Running: Yes は、IOスレッドが正常に動作していることを示す
- Slave_SQL_Running: Yes は、SQLスレッドが正常に動作していることを示す
- 通常、レプリケーションを開始していない場合や、明示的にSTOP SLAVEで停止した場合を除き、この項目は「Yes」であるべき
Seconds_Behind_Master
- この値が「0」であれば、レプリカで処理中のイベントがない(=同期している)状態
Last_Error / Last_IO_Error / Last_SQL_Error エラーが発生している場合、ここにエラー内容が表示される

上記の一例の場合、 Slave_IO_Running と Slave_SQL_Running が Yes であるが Seconds_Behind_Masterの値が775であることから、同期がまだ終わっていないことがわかります。

最終的にGreen環境にもBlue環境のデータが反映され、 Seconds_Behind_Masterの値が0になったのはALTER TABLEの実行が完了した約1時間後でした。

なぜ同期に1時間もかかったのか、

SHOW PROCESSLIST;

で実行中のプロセスを確認したところ、ALTER TABLEの実行中にバッチが実行されて大量のINSERT処理が動いていました。バッチが実行されていたことが原因で同期が遅れてしまった可能性が高いです。

以降の作業はバッチが実行されていないことを確認したうえで他のテーブルに対してもint型をbigint型に修正するDDLを実行しました。すると同期は遅くとも5分程度で完了しました。また、Blue環境にデータが登録/修正された時、Green環境にも同期されていることを確認できました。

課題3: Blue/Green環境間のデータの同期が急に取れなくなった

課題2が発生してから数日後のことです。「同期されていないのではないか？」という報告があがりました。そこで確認のために show slave statusコマンドを実行すると “違う型に変換できない”という旨のエラーが出力され、同期が取れていないことが判明しました。

以下にエラーメッセージの一部を示します。

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for the slave SQL thread to free enough relay log space
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1677
                   Last_Error: Column 4 of table 'テーブル1' cannot be converted from type 'int' to type 'bigint(20) unsigned'
                 Skip_Counter: 0
              Relay_Log_Space: 1000000024
        Seconds_Behind_Master: NULL
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1677
               Last_SQL_Error: Column 4 of table 'テーブル1' cannot be converted from type 'int' to type 'bigint(20) unsigned'

データベースへの接続自体には問題がなかったこともあり、このコマンドを実行するまで同期が止まっていることに気づきませんでした。このまま気づかずに進めていた場合、一部のデータが欠損した状態でデータベースの切り替えを行う可能性がありました。

課題3の原因と解決策

調査を進めたところ、Green側に設定されているパラメータグループの slave_type_conversionsパラメータの値がエラーの原因となっている可能性があることが分かりました。

以下は、MySQLドキュメントから抜粋した説明を機械翻訳したものです。 https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

ALL_LOSSY
このモードでは、情報の損失を意味するような型変換が許可されます。
例えば、このモードのみを有効にすると、INT列からTINYINT列への変換（損失のある変換）は許可されますが、TINYINT列からINT列への変換（損失のない変換）は許可されません。
この場合、後者の変換を試みると、レプリカでエラーが発生してレプリケーションが停止します。

ALL_NON_LOSSY
このモードは、ソース値の切り捨てまたは特別処理を必要とない変換を許可します。すなわち、ターゲット型の範囲がソース型より広い変換を許可します。
このモードを設定することは、不可逆変換が許可されるかどうかに関係ありません。これはALL_LOSSYモードで制御されます。
ALL_NON_LOSSYのみが設定され、ALL_LOSSY は設定されていない場合、
データ (INT から TINYINT、CHAR(25) から VARCHAR(20) など) が失われる原因となる変換を試みると、レプリカはエラーで停止します。

ALL_LOSSY, ALL_NON_LOSSY
このモードが設定されると、サポートされるすべての型変換が、不可逆変換かどうかにかかわらず、許可されます。

エラー発生時の slave_type_conversionsの設定値はALL_LOSSYのみでした。そのため、INT から BIGINT のような損失のない変換の許可がされずエラーが発生してしまったと考えられます。対策として、 ALL_NON_LOSSY を指定する必要があると考えました。

しかしここで一つ疑問が残りました。

なぜ、課題2の時にはエラーが発生せず、同期が完了したのか？

課題2の時点でslave_type_conversionsにALL_LOSSYのみが設定されていたため、 Blue環境のデータが更新されると何らかのエラーが発生していてもおかしくないはずです。ですがこの日レプリケーションされていることは確認済みでした。

この疑問を解決するため再現検証を行いました。

再現検証

slave_type_conversionsにALL_LOSSYのみが設定されている状態で、Blue環境に対し以下のDDLを実行しました。カラム名は仮です。

B/G で型の異なるカラムが含まれているテーブルを対象に全件更新
B/G で型の異なるカラムを対象に1件更新
INNER JOINを使用した、B/G で型の異なるカラムが含まれているテーブルを対象に1件更新
SYSDATE()を使用したUPDATE文 (毎日深夜に実行されるバッチ)

1. UPDATE `テーブルA` SET u_id = 'APP-TEST1';

2. UPDATE `テーブルB` SET p_id = null  WHERE id = 123456789;

3. UPDATE `テーブルB`  INNER JOIN `テーブルC`  ON `テーブルC`.p_id = `テーブルB`.p_id  AND `テーブルB`.p_id = 12345678 SET `テーブルB`.u_id = 'APP-test';

4. UPDATE `テーブルB` b 
SET
  b.pro_id = 123456789, b.rep_date = SYSDATE(), date_diff = SYSDATE(),b.u_id = 'API_TEST', b.u_date = SYSDATE()
WHERE
  b.pro_id = 123456789 
AND b.flag = 1;

その結果、 1、2はGreen環境にも問題なく同期されましたが、 3、4は課題3と同内容のエラーが発生し、同期がストップしました。

この結果をもとにさらに調査を進めました。

今回パラメーターグループに設定していた binlog_formatの値はMIXEDでした。

MIXED形式では、デフォルトでSTATEMENT形式で同期が行われますが、特定条件下ではSTATEMENT形式だと安全でないと判断され、自動的にROW形式に切り変わることがあります。

https://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html から抜粋

5.4.4.3 Mixed Binary Logging Format
When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
・・・

https://dev.mysql.com/doc/refman/5.7/en/replication-rbr-safe-unsafe.html から抜粋

Handling of safe and unsafe statements.  A statement is treated differently depending on whether the statement is considered safe, and with respect to the binary logging format (that is, the current value of binlog_format).

When using row-based logging, no distinction is made in the treatment of safe and unsafe statements.

When using mixed-format logging, statements flagged as unsafe are logged using the row-based format; statements regarded as safe are logged using the statement-based format.

When using statement-based logging, statements flagged as being unsafe generate a warning to this effect. Safe statements are logged normally.

どのようなケースでSTATEMENT形式が安全でないと判断されるかは以下のドキュメントに記載されています。 https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html

今回は次の条件に該当し、自動的にROW形式に切り変わったと考えられます。

Statements using any of the following functions cannot be replicated properly using statement-based replication:
LOAD_FILE(),UUID(), UUID_SHORT(), USER(), FOUND_ROWS(), SYSDATE() (unless both the source and the replica are started with the --sysdate-is-now option), GET_LOCK(), IS_FREE_LOCK(),IS_USED_LOCK(), MASTER_POS_WAIT(),RAND(), RELEASE_LOCK(), SLEEP(), VERSION()

For complex statements, the statement must be evaluated and executed on the replica before the rows are updated or inserted. With row-based replication, the replica only has to modify the affected rows, not execute the full statement.

また、ROW形式でのレプリケーションは損失のある変換と損失のない変換をどのように扱うか設定しないといけない旨が記載されていました。 https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

以上を踏まえ、事象発生までの過程と原因は次のように整理できました。

エラー発生時、binlog_formatの値は MIXED、slave_type_conversions = ALL_LOSSY を設定していた
MIXEDの場合、デフォルトはSTATEMENT形式で同期が行われるが、特定の条件下ではROW形式に切り変わる
- ROW形式に切り替わる特定の条件一例
  - 特定の関数を使用するステートメント (LOAD_FILE(),UUID(), UUID_SHORT(), USER(), FOUND_ROWS(), SYSDATE() etc..)
  - 複雑なステートメント
毎日深夜に実行されているバッチでSYSDATE()を使用していたため、自動的にROW形式に切り変わった
ROW形式での同期は損失のある変換と損失のない変換をどのように扱うか決定しないといけないが、正しい設定が行われていなかった
損失のない型違いの処理ができず、“違う型に変換できない“旨のエラーが出てしまった

slave_type_conversions の値を ALL_LOSSY,ALL_NON_LOSSY に設定することで ROW形式での同期時に損失のない型違いの処理を許可することができ、同期のエラーが表示されることはなくなりました。

更に同期不具合の早期発見のために数時間おきに SHOW SLAVE STATUS\Gを実行し、その結果をメールで送信するシェルを作成しました。

その後、特にトラブルが発生することなくリリースすることが出来ました。

まとめ

今回得られたこと・伝えたいことは以下です。

リリース計画時

メンテナンス時間を短縮するためにBlue/Green Deploymentsを検討する
時間を見積もる際は同期時間も考慮することが重要

Green環境にDDLを実行する前後

Green環境でDDLを実行する前に、バッチなどの実行状況を確認すること
- バッチなどの実行中にDDLを実行すると、同期が遅れる可能性がある
- SHOW PROCESSLISTを使用して実行中のプロセスを確認可能
DDL実行完了後には以下を確認すること
- DESC {テーブル名}を実行し、修正箇所が反映されていることを確認する
- SHOW SLAVE STATUSを実行し、次を確認する：
  - Slave_IO_Running と Slave_SQL_Running がYesになっていること
  - Seconds_Behind_Master の値が0になっていること
  - Last_Error、Last_IO_Error、Last_SQL_Errorに何も表示されていないこと

Green環境にDDLを実行以降〜リリース当日

定期的に同期確認を行い、エラーが発生していないか確認すること
binlog_formatの値がMIXEDの場合、特定の条件下で自動的にROW形式に切り替わることがあるため、注意が必要
- slave_type_conversionsの値をALL_LOSSY,ALL_NON_LOSSY に設定することで、型違いの処理に関するエラーを回避できる

明日のレコチョク Advent Calendar 2024 は5日目「ネイティブアプリ開発チームの開発生産性の指標を定義してみた」です。お楽しみに！

RecoChoku Engineering Blog

目次