🐬

大きなテーブルのスキーマ変更を RDS Blue/Greenデプロイでダウンタイムなく実行 & マイグレーションとも辻褄を合わせる

kyoooko

2024/04/26に公開

はじめに

今回大きなテーブルのスキーマ変更のためにマイグレーションを実行したかったのですが、通常のデプロイフローでは対応できず試行錯誤しました。
最終的に、RDS Blue/Greenデプロイを活用しGreen環境でALTER TABLEすることでほぼダウンタイムなく対応できました。Railsのマイグレーションファイルの偽装方法も含め、道のりを記載します。

テーブルは2つあり、それぞれ 約2.5億レコードと約4000万レコード です。

show table status;
+------------------------------+--------+---------+------------+-----------+
| Name                         | Engine | Version | Row_format | Rows      |
+------------------------------+--------+---------+------------+-----------+
| report_histories             | InnoDB |      10 | Dynamic    | 254528512 |
| reports                      | InnoDB |      10 | Dynamic    |  40091307 |

解決したい課題

大きなテーブルのスキーマ変更（今回はstring型→text型の型変更）をダウンタイムを発生させずに行いたい
DBの状態とRailsのマイグレーションに乖離が出ないようにしたい

試行錯誤①通常のデプロイフローでデプロイ

まず、何も考えずいつも通りRailsのマイグレーションファイルを追加しデプロイしましたが、２テーブル合わせてマイグレーション完了まで5時間超もかかってしまいました。

db/migrate/20240409094847_add_column_to_report_history_targete_id.rb

class AddColumnToReportHistoryTargeteId < ActiveRecord::Migration[7.0]
  def change
    change_column :report_histories, :targete_id, :text
  end
end

db/migrate/20240409094927_add_column_to_report_targete_id.rb

class AddColumnToReportTargeteId < ActiveRecord::Migration[7.0]
  def change
    remove_index :reports, :targete_id
    change_column :reports, :targete_id, :text
    add_index :reports, :targete_id, length: 255
  end
end

RDSのスペックは、下記の通りです。

インスタンスタイプ	ストレージ	マイグレーションの所要時間
t4g.large（vCPU: 2、メモリ: 8GiB）	200GB	5時間超

加えて、マイグレーション中に下記の影響が発生しました。
要するに、RDSのスペック不足でした。

【ポイント】なぜメモリとストレージが激減したのか

マイグレーションを開始してから、メモリ使用量は3GB→6.5GB/容量8GB、ストレージは一時的に約50GBも使用量が増加しました。
クエリのプロセスを確認すると、copy to tmp tableとなっていました。
どうやらカラム型変更をする際、裏で新しいテーブル定義のテーブルを一時的に作成してから、元のテーブルのデータがコピーされているようです。

SHOW PROCESSLIST;
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id   | User   | Host | db    | Command | Time  | State                           | Info                                                                                                 |
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+                      
|  529 | user   | xxx  | host | Query   |   748  | copy to tmp table               | ALTER TABLE `reports` CHANGE `target_id` `target_id` text COLLATE utf8mb3_general_ci DEFAULT NULL   |                      
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+

リソースを確認してみると、一時テーブルを作成する際、まずメモリを使用し、足りなくなったらスワップ（ストレージを使用）している様子が見受けられます。
1つ目のテーブルの容量が約50GBだったので、ストレージが110GB→56GBとなっているのは辻褄が合います。
ALTER TABLEが終わるとストレージは回復していました。

空きメモリが減ったので、その分ページによっては表示に時間がかかったり、タイムアウトしてしまったということですね。
さらにメモリはALTER TABLEが終わっても回復していないので、再起動などが必要そうです。

`ALTER TABLE`実行前に、ストレージの空き容量をチェックしましょう

ALTER TABLEで一時テーブルが作られる変更をする場合は、ストレージの空き容量が十分にあるか確認しましょう。
コピーされるテーブルのデータ量は、そのテーブルのデータ量と大体同じなので、その分以上の空きが必要ということになります。
下記の例だと、2つのテーブルの合計が68GB（=49GB+19GB）なのでストレージにそれ以上の空きがあることを確認してからスキーマの変更を開始しました。

MySQL（テーブルのデータ量を調べるクエリ例）

SELECT table_name "Table", engine,table_rows "Table Rows", ( data_length + index_length ) / 1024 / 1024 / 1024 "Data (GB)", ( data_free )/ 1024 / 1024 / 1024 "Free Space (GB)" FROM information_schema.TABLES WHERE table_schema=database() ORDERBY (data_length+index_length) desc limit 10;
+----------------------------+--------+------------+-----------------+-----------------+
| Table                      | ENGINE | Table Rows | Data (GB)       | Free Space (GB) |
+----------------------------+--------+------------+-----------------+-----------------+
| report_histories           | InnoDB |  254528512 | 49.129882812500 |  0.003906250000 |
| reports                    | InnoDB |   40091307 | 18.813156127930 | 13.663085937500 |

【ポイント】table metadata lockにより、別の書き込み処理が待ちになってしまう

クエリを確認すると、他の処理がWaiting for table metadata lockになっていることがわかります。
5時間超もの間、他の書き込み処理ができなくなってしまうのは大問題です😅

MySQL

SHOW PROCESSLIST;
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id   | User   | Host | db    | Command | Time  | State                           | Info                                                                                                 |
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+                      
|  529 | user   | xxx  | host | Query   |   748  | copy to tmp table               | ALTER TABLE `reports` CHANGE `target_id` `target_id` text COLLATE utf8mb3_general_ci DEFAULT NULL   |                      
|  531 | user   | xxx  | host | Query   |   233  | Waiting for table metadata lock | UPDATE `reports` SET `reports`.`hoge1` = 0, `reports`.`hoge2` = 0                                    |
+------+--------+------+-------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+

`Waiting for table metadata lock`とは

Metadata Lockというのは、ざっくりいうとMetadata（information_schemaに入っているテーブルやカラム等の情報）を変更する際にかかるロックです。テーブル自体がMetadata Lockしていると、そのテーブルに対する他の変更処理が待ちになってしまいます。

こちらの記事がとてもわかりやすかったです。

試行錯誤②RDSのインスタンスサイズを4倍上げて、再トライ

一時的にRDSをt4g.large → t4g.2xlargeにスペックアップしてみました。t4g.2xlargeはt4g系のインスタンスの最大サイズです。
サイズアップはBlue/Greenデプロイを使用しました。

マイグレーションを再トライすると、２テーブル合わせて所要時間は2時間超に短縮しました！
とはいえ、引き続きページのタイムアウトやmetadata lockが2時間超も発生するとなると、メンテナンス時間を取るしかありません...

インスタンスタイプ	ストレージ	マイグレーションの所要時間
t4g.2xlarge（vCPU: 8、メモリ: 32GiB）	200GB	2時間超

試行錯誤③`ALTER TABLE`にBlue/Greenデプロイを使ってみる

ここでやっと思いつきました。
いや、どうせインスタンスサイズ変更でBlue/Greenデプロイするなら、Green環境でALTER TABLE してしまえばいいのでは？

対応手順

流れはこんな感じです。

Blue/Greenデプロイの作成
↓
Green環境をインスタンスサイズアップ（4倍）
↓
Green環境を書き込み可能にする（パラメータグループの変更→再起動）
↓
Green環境のMySQLに入って`ALTER TABLE`を手動実行
↓
Green環境の`ALTER TABLE`が終わり、正しくカラム変更されたことを確認
↓
Green環境のインスタンスサイズとパラメータグループを元に戻す→再起動
↓
Blue/Greenの切り替え

これでマイグレーション中のタイムアウトやロックなどの弊害を回避することができます！
Blue/Greenデプロイの切り替え時に、1分弱ダウンタイムが発生（経験上は数秒程度）しますが、今回サービスには支障はなさそうです。

Railsのマイグレーションファイルにも履歴を残したい

これでDB自体の変更は完了したのですが、Railsのマイグレーション管理対象から外れてしまいます。
マイグレーションファイルを改ざんして、Rails側にも履歴が残るようにしていきます。
これを含めた手順は下記の通りです。

①Blue/Greenデプロイの作成
↓
②Green環境をインスタンスサイズアップ（4倍）
↓
③Green環境を書き込み可能にする（パラメータグループの変更→再起動）
↓
④★マイグレーションの改ざん（空のマイグレーションファイルを作成し実行）
↓
⑤Green環境のMySQLに入って`ALTER TABLE`を手動実行
↓
⑥Green環境の`ALTER TABLE`が終わり、正しくカラム型変更されたことを確認
↓
⑦Green環境のインスタンスサイズとパラメータグループを元に戻す→再起動
↓
⑧Blue/Greenの切り替え
↓
⑨★マイグレーションの改ざんの辻褄合わせ（先程のマイグレーションファイルの中身を記載し実行）

① Blue/Greenデプロイの作成

Blue/Green作成時にインスタンスサイズ変更はできないので、①Blue/Greenデプロイを作成 → ②Green環境の変更（インスタンスサイズ変更）の2段階を踏む必要があります。
ちなみに、今回①に6時間、②に1時間かかりました...
（ストレージファイルシステム設定の更新がされたため通常より長くなったのかも）

② Green環境をインスタンスサイズアップ（4倍） & ③ Green環境を書き込み可能にする（パラメータグループの変更→再起動）

Green環境はデフォルトで読み込み専用のため、書き込み可能に変更する必要があります。
今回はパラメータグループを使用し変更しました。
書き込み可能なパラメータグループの作成方法は下記をご覧ください。

今回は②と③を同時にやっていきます。
Green環境選択し、「変更」ボタンを押します。
インスタンスサイズとパラメータグループを変更し、更新します。
パラメータグループのステータスが「再起動を保留中」の場合は、RDSの再起動をします。

④ ★マイグレーションの改ざん（空のマイグレーションファイルを作成し実行）

まず、実行前の状態を確認します。

Rails環境

rails db:migrate:status
   ...
   up     20240306110022  Create xxxx ←元々あったマイグレーションファイル

rails db:version
Current version: 20240306110022

MySQL

// スキーマのマイグレーションの状況
SELECT * FROM schema_migrations;
...
|  ...           |
| 20240306110022 | ← 元々あったマイグレーション
+----------------+
 // テーブル1
SHOW COLUMNS FROM report_histories;
| target_id           | varchar(255) | YES  |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
 // テーブル2
SHOW COLUMNS FROM reports;
| target_id           | varchar(255) | YES  | MUL | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
SHOW INDEX FROM reports;
| reports           |          1 | index_reports_on_target_id                     |            1 | target_id           | A         |      118405 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------------+------------+------------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

空のmigrationファイルを作成します。

db/migrate/20240409094847_add_column_to_report_history_targete_id.rb

class AddColumnToReportHistoryTargeteId < ActiveRecord::Migration[7.0]
  def change
    # DBで直接実行
    # execute "ALTER TABLE report_histories CHANGE target_id target_id text;"
  end
end

db/migrate/20240409094927_add_column_to_report_targete_id.rb

class AddColumnToReportTargeteId < ActiveRecord::Migration[7.0]
  def change
    # DBで直接実行
    # execute "DROP INDEX index_reports_on_target_id ON reports;"
    # execute "ALTER TABLE reports CHANGE target_id target_id text;"
    # execute "CREATE INDEX index_reports_on_target_id ON reports (target_id(255));"
  end
end

マイグレーション（デプロイ）を実行します。

db/schema.rb

- ActiveRecord::Schema[7.0].define(version: 2024_03_06_110022) do
+ ActiveRecord::Schema[7.0].define(version: 2024_03_13_014700) do

改めてマイグレーションの状況を確認すると、マイグレーションは進んでいるが、DBは変更されていない状態に改ざんすることができました。

Rails環境

rails db:migrate:status
   略
   up     20240306110022  Create xxxx ← 元々あったマイグレーションファイル
   up     20240313013854  Add column to report history target id ← 追加
   up     20240313014700  Add column to report target id         ← 追加
rails db:version
Current version: 20240313014700

MySQL

// スキーマのマイグレーションの状況
SELECT * FROM schema_migrations;
略
|  ...           |
| 20240306110022 | ← 元々あったマイグレーション
| 20240313013854 | ← 追加
| 20240313014700 | ← 追加
+----------------+
 // テーブル1
SHOW COLUMNS FROM report_histories;
| target_id           | varchar(255) | YES  |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
 // テーブル2
SHOW COLUMNS FROM reports;
| target_id           | varchar(255) | YES  | MUL | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
SHOW INDEX FROM reports;
| reports           |          1 | index_reports_on_target_id                     |            1 | target_id           | A         |      118405 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------------+------------+------------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

⑤ Green環境のMySQLに入って`ALTER TABLE`を手動実行

ALTER TABLEを実行していきます。

MySQL

// テーブル1
ALTER TABLE `report_histories` CHANGE `target_id` `target_id` text;
// テーブル2
DROP INDEX index_reports_on_target_id ON reports;
ALTER TABLE `reports` CHANGE `target_id` `target_id` text;
CREATE INDEX index_reports_on_target_id ON reports (target_id(255));

⑥ Green環境の`ALTER TABLE`が終わり、正しくカラム型変更されたことを確認

型変更が正しく実行されていました。

MySQL

 // テーブル1
SHOW COLUMNS FROM report_histories;
| target_id           | text         | YES  |     | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
 // テーブル2
SHOW COLUMNS FROM reports;
| target_id           | text         | YES  | MUL | NULL    |                |
+---------------------+--------------+------+-----+---------+----------------
SHOW INDEX FROM reports;
| reports           |          1 | index_reports_on_target_id                     |            1 | target_id           | A         |       86982 |      255 |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------------------+------------+------------------------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

⑦ Green環境のインスタンスサイズとパラメータグループを元に戻す→再起動

先ほどと反対の手順で、Green環境のパラメータグループとインスタンスサイズを戻します。
パラメータグループのステータスが「再起動を保留中」の場合は、RDSの再起動をします。

⑧ Blue/Greenの切り替え

ついに切り替えを実行します。
Blue/Greenの切り替えが完了したら、アプリケーションの動作に問題がないか確認しましょう。

⑨ ★マイグレーションの改ざんの辻褄合わせ（先程のマイグレーションファイルの中身を記載し実行）

先程のマイグレーションファイルの中身のコメントアウトを外します。

db/migrate/20240409094847_add_column_to_report_history_targete_id.rb

class AddColumnToReportHistoryTargeteId < ActiveRecord::Migration[7.0]
  def change
    # DBで直接実行済
    execute "ALTER TABLE report_histories CHANGE target_id target_id text;"
  end
end

db/migrate/20240409094927_add_column_to_report_targete_id.rb

class AddColumnToReportTargeteId < ActiveRecord::Migration[7.0]
  def change
    # DBで直接実行済
    execute "DROP INDEX index_reports_on_target_id ON reports;"
    execute "ALTER TABLE reports CHANGE target_id target_id text;"
    execute "CREATE INDEX index_reports_on_target_id ON reports (target_id(255));"
  end
end

再度マイグレーション（デプロイ）を実行します。

db/schema.rb

ActiveRecord::Schema[7.0].define(version: 2024_03_13_014700) do
create_table "report_histories", charset: "utf8mb3", collation: "utf8mb3_unicode_ci", force: :cascade do |t|
...
- t.string "target_id"
+ t.text "target_id"

create_table "reports", charset: "utf8mb3", collation: "utf8mb3_unicode_ci", force: :cascade do |t|
...
- t.string "target_id"
+ t.text "target_id"
...
- t.index ["target_id"], name: "index_reports_on_target_id"
+ t.index ["target_id"], name: "index_reports_on_target_id", length: 255

改めてマイグレーションの状況を確認すると、マイグレーションとDBの状態が一致している状態にすることができました！

Rails環境

rails db:migrate:status
   略
   up     20240306110022  Create xxxx ← 元々あったマイグレーションファイル
   up     20240313013854  Add column to report history target id ← 変わらず
   up     20240313014700  Add column to report target id         ← 変わらず
rails db:version
Current version: 20240313014700

MySQL

// スキーマのマイグレーションの状況
SELECT * FROM schema_migrations;
略
|  ...           |
| 20240306110022 | ← 元々あったマイグレーション
| 20240313013854 | ← 変わらず
| 20240313014700 | ← 変わらず
+----------------+

注意点

Green環境で`ALTER TABLE`できるのはレプリケーション互換のある変更のみ

今回Green環境でカラム型変更（string型→text型）をしてRDS Blue/Greenデプロイも成功しましたが、無条件でなんでもGreen環境に変更を加えられるわけではありません。
対応したい変更がGreen環境で実行可能なのかは、事前に確認しておきましょう。
詳細は別記事に書きました。

Blue/Green入れ替え後、クライアントが旧Blue環境へのコネクションを貼ったままになっていないか注意

Blue/Green切り替え後、一部のクライアントの書き込み処理でエラーが発生しました。

また、ジョブも捌かれずキューが溜まってしまっていました。

Mysql2::Error: The MySQL server is running with the --read-only option so it cannot execute this statement とエラーが出ているので、旧Blue環境（=新Green環境）に接続してしまっているようです。（Blue/Greenデプロイでは、Green環境が読み込み専用レプリカになります）

今回クライアントはECS on Fargateのタスクだったので、サービスの更新 > デプロイの強制を行うことで解消しました。

まとめ

💡 大きなテーブルのスキーマ変更をダウンタイムを発生させずに行うにはBlue/Greenデプロイが有効

ただし、Green環境でALTER TABLEできるのはレプリケーション互換のある変更のみなので注意
Blue/Green入れ替え後、クライアントが旧Blue環境とのコネクションを貼ったままになっていないか注意

💡 DBの状態とRailsのマイグレーションに乖離が出ないようにするには、マイグレーションの改ざんをすればいい

💡 大きいテーブルを`ALTER TABLE`する時は、さまざまな影響が発生するケースがあるので注意

RDSのCPUやメモリ使用率が上昇し、他の処理が遅延したりタイムアウトが発生する可能性がある
table metadata lockがかかり、他の処理が遅延するケースがある
一時的にDBのストレージを消費するケースがあるので空き容量に注意

参考にさせていただいた記事

マイグレーションファイルの改ざんについて

株式会社L&E GroupPublication

成果報酬型広告のプラットフォームサービス「Link-AG」を運営しています。新米エンジニアを中心に記事を執筆しています。

はじめに

解決したい課題

試行錯誤①通常のデプロイフローでデプロイ

【ポイント】なぜメモリとストレージが激減したのか

【ポイント】table metadata lockにより、別の書き込み処理が待ちになってしまう

試行錯誤②RDSのインスタンスサイズを4倍上げて、再トライ

試行錯誤③ALTER TABLEにBlue/Greenデプロイを使ってみる

対応手順

Railsのマイグレーションファイルにも履歴を残したい

① Blue/Greenデプロイの作成

② Green環境をインスタンスサイズアップ（4倍） & ③ Green環境を書き込み可能にする（パラメータグループの変更→再起動）

④ ★マイグレーションの改ざん（空のマイグレーションファイルを作成し実行）

⑤ Green環境のMySQLに入ってALTER TABLEを手動実行

⑥ Green環境のALTER TABLEが終わり、正しくカラム型変更されたことを確認

⑦ Green環境のインスタンスサイズとパラメータグループを元に戻す→再起動

⑧ Blue/Greenの切り替え

⑨ ★マイグレーションの改ざんの辻褄合わせ（先程のマイグレーションファイルの中身を記載し実行）

注意点

Green環境でALTER TABLEできるのはレプリケーション互換のある変更のみ

Blue/Green入れ替え後、クライアントが旧Blue環境へのコネクションを貼ったままになっていないか注意

まとめ

💡 大きなテーブルのスキーマ変更をダウンタイムを発生させずに行うにはBlue/Greenデプロイが有効

💡 DBの状態とRailsのマイグレーションに乖離が出ないようにするには、マイグレーションの改ざんをすればいい

💡 大きいテーブルをALTER TABLEする時は、さまざまな影響が発生するケースがあるので注意

参考にさせていただいた記事

マイグレーションファイルの改ざんについて

Discussion

試行錯誤③`ALTER TABLE`にBlue/Greenデプロイを使ってみる

⑤ Green環境のMySQLに入って`ALTER TABLE`を手動実行

⑥ Green環境の`ALTER TABLE`が終わり、正しくカラム型変更されたことを確認

Green環境で`ALTER TABLE`できるのはレプリケーション互換のある変更のみ

💡 大きいテーブルを`ALTER TABLE`する時は、さまざまな影響が発生するケースがあるので注意