STORES Product Blog

こだわりを持ったお商売を支える「STORES」のテクノロジー部門のメンバーによるブログです。

Aurora V3(MySQL8.0)への移行を振り返る

STORES 予約 インフラ

こんにちは。STORES 予約 で開発エンジニアをしているmyahagiです。

11月の頭にSTORES 予約 のDBをAurora V2(MySQL5.7互換)からAurora V3(MySQL8.0互換)へ移行しました。
Aurora V2(MySQL5.7互換)の標準サポートが10月末で終了しましたが、サポート終了後、2年間は延長サポート費用を支払うことでバージョンアップせずとも利用が可能といったこともあり、これからアップデートを計画するところもあるかと思います。

今回、私たちがアップデートにあたって事前にどのような検証をし、どういった移行手順で移行を行ったのか、また、移行後に限定的な範囲でトラブルに見舞われてしまったのですが、どう対処して乗り切ったのかを紹介します。

事前検証と後半のトラブルシューティングの部分は特に、今後同じような轍を踏まないで済むように参考にしてもらえたら幸いです。

なお、この記事は STORES アドベントカレンダーの10日目の記事です。

アップデート手順の策定

事前検証実施と並行し、実際にデータベースをアップデートする際にどういった手順で実施するかを策定しました。

夜間帯にメンテナンスの時間を設け、完全に利用者からのアクセスを遮断した上で実施する前提で作成しています。
STORES 予約 では、利用者がほぼ国内であること、また、特にシステムが止まると困る管理画面側の利用者のピークタイムは日中であり、夜間帯のアクセスが少ないことからこのような選択肢を取れています。

具体的な移行手順

Aurora V2クラスターのスナップショットから構築したAurora V3クラスターを事前に構築し、V2クラスターのライターインスタンスからV3クラスターのライターインスタンスへのレプリケーションを貼る作業までを事前に実施
その後、メンテナンス時間を確保して、DBへの接続がない状態にし、メンテナンス中にレプリケーションを止めてアプリケーションのDBの参照先をAurora V3クラスターのものへと切り替えるといった手順で移行しました。

図に示すと以下のような形です。

Blue/Greenデプロイ等の選択肢もありましたが、今回このような方法を選択した背景は以下になります。

  • 以前、RDSからAuroraへ移行した際の手順と大きく変わらず、当時の作業メンバー(自分も含む)が今回の作業メンバーにも含まれているため、実績があり手慣れた手順である
  • ライターインスタンスの参照先変更前(つまり切り替えのためのメンテナンス実施前)に本番の参照系クエリの一部をV3のリーダーインスタンスへ向け、参照系で問題が発生しないことを担保した上で移行に臨める

このようなメンテナンスを伴った何らかの作業の実施前には、作業手順書の整備と手順の検証を事前に行うのですが、こういった作業コストを大きく抑えてAurora V3そのものの検証へリソースが割けることを優先した形になります。

移行にあたって影響範囲の洗い出しと事前対応

MySQLのdocsや先行して実施されている他社の移行事例等を拾い読みしつつ洗い出しを実施
MySQL :: MySQL 8.0 リファレンスマニュアル :: 2.11.4 MySQL 8.0 での変更

特にデフォルトcollationの変更への対応と、GROUP BY句における暗黙のソートがされなくなることに関しては個別で対処が必要であろうと判断し、それぞれ以下のように対処しました。

デフォルトcollationの変更への対応

全てのテーブルにおいてcollationをurf8mb4_genaral_ci(MySQL５.７でのデフォルト)に固定することでそもそも個々の変更に対して向き合わない形で回避しています。

また、STORES 予約ではActiveRecordを通してDBマイグレーションを実施しているため、以下のようなコードを追加し、MySQL 8.0以降でのテーブル追加時に都度collationを指定しなくてもurf8mb4_genaral_ci が指定されるようにしています。

module MysqlDefaultCollationCorrector
  DEFAULT_CHARSET = 'utf8mb4'
  DEFAULT_COLLATION = 'utf8mb4_general_ci'

  def create_table(table_name, **options)
    if (options[:charset] == DEFAULT_CHARSET || options[:charset].nil?) && options[:collation].nil?
      logger.warn "Collation is not set for table '#{table_name}'. Please set collation to '#{DEFAULT_COLLATION}' or whatever you want."
      options[:collation] = DEFAULT_CHARSET
      options[:collation] = DEFAULT_COLLATION
    end

    super
  end
end

# config/initializers配下でActiveRecord::ConnectionAdapters::AbstractMysqlAdapterに継承させてオーバーライド
ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter.prepend(MysqlDefaultCollationCorrector)

GROUP BY句における暗黙のソートがされなくなる

ここについては、対象のコード内を気合いで目grepして確認しました。
と言いたいところですが、気合い目grepでは見落としが発生することは容易に想像出来ることもあり、同僚がスタイリッシュな方法でOrder指定のないGROUP BY句がないかを検出してくれました。

詳しい方法はその同僚に公開してもらう形がよいだろうと思っているので、ここでは概要だけお伝えします。

ActiveSupport::Notifications.subscribeでsql.active_recordを購読することで、SQL発行時にどういったクエリが発行されたのかを都度拾うことができます。
https://railsguides.jp/active_support_instrumentation.html#active-record

後はその拾ったSQL文に対して、GROUP BY句が見つかるけどOrder句が見つからないクエリを検出して結果を記録する処理を定義し、CIを回すことでそのようなクエリが発行されていないかどうかを機械的に検出していました。

次似たような機会があったら自分も真似したいなと思っております。

検証環境のDBをAurora V3へアップグレードしての動作検証

ここまの過程で、見えている範囲におけるコードレベルでの対応が必要な箇所への対応が完了したので、実際に検証環境のDBをアップグレードしての動作検証を実施しています。

STORES 予約では、個人情報に関わる情報をマスクした本番と同等のデータを備えた検証環境を用意しています。
不具合検証やパフォーマンス検証等でも何かと便利な環境なのですが、ここでも活躍してくれました。

アップグレード実施時に実行されるprecheckが失敗する。

事前にMySQL8.0を利用したテストコードが全てパスすることの確認は取れていましたが、本番相当のデータではそもそもアップグレードが実施できない状態であることが判明しました。

以下が初回のprecheck時に返されたエラー内容になります。

{
  "id": "mysqlIndexTooLargeCheck",
  "title": "Check for indexes that are too large to work on higher versions of MySQL Server than 5.7",
  "description": "Error: The following indexes ware made too large for their format in an older version of MySQL (older than 5.7.34). Normally those indexes within tables with compact or redundant row formats shouldn't be larger than 767 bytes. To fix this problem those indexes should be dropped before upgrading or those tables will be inaccessible."
}

エラー内容をそのまま翻訳しても何故このエラーが返されるのか、アップグレード前にそれらのインデックスを削除する必要があります。と言われても、いやいやいくらアップグレードのためとはいえインデックスを削除する訳にはいかないし ということで解決しなきゃいけない問題の本質が捉えにくいです。

たまたま自分はインデックスは767バイトを超えてはなりませんの767バイトの文字でピンときて、varchar(255)のカラムが定義されていた場合に、utf8であれば1文字が3バイトなため255文字 * 3バイト＝765バイトで問題なくインデックスに載せることが可能だが、utf8mb4では1文字が4バイトとして扱われてしまうため、191文字以上のデータが存在すると767バイトを越してしまいインデックスに載せられなくなる問題に起因しそうだなと投稿したところ、該当テーブルのFILE_FORMATがAntelopeだったことを同僚が突き止めてくれました。
(さも手柄のように書いてますが、有名っちゃ有名ですね)

STORES 予約は既に10年選手のプロダクトであり、MySQLのデフォルトのFILE_FORMATがAntelopeの頃(MySQL5.6以前)に追加されたテーブルが当時のFILE_FORMATのまま運用がされていたりします。
また、MYSQLではROW_FORMATをDYNAMIC(Barracudaのデフォルト)へと変更することでインデックス長の制限が767バイトから、3072バイトに拡張されます。

なので、流石にインデックスを削除したままアップグレードすることは出来ないということで、該当テーブルのFILE_FORMATをBarracudaへROW_FORMATをDYNAMICと変更する作業を実施し、utf8mb4かつ191文字以上のデータでもインデックスに載せられるようにして乗り切りました。

MySQL5.7ではDB自体のFILE_FORMATは既にBaraccudaなためROW_FORMATをDYNAMICへ変更してあげることで自動的に対象テーブルのFILE_FORMATがBaraccudaへと切り替わります。
ROW_FORMATを変更するタイミングで、インデックスの再生成も同時に実施されます。

また、ROW_FORMATの変更はALGORITHM=INPLACE, LOCK=NONEを指定して実行可能なため、特にメンテナンスは設けずに夜間のアクセスが減ったタイミングで実施しました。

ALTER TABLE piyopiyo ROW_FORMAT = DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;

MySQL8.0ではinnodb_file_formatがBarracudaで固定されるため、このようなチェックが設けられているのだと思います。

ここまでの対応を終えた後に無事precheckが通り、アップグレードが可能な状態となりました。

本番とほぼ同様のデータを持った検証があったことで、このようなハマりポイントを事前に検出することが出来ました。

Aurora V3での事前検証

日々の機能開発で利用しているリグレッションテスト項目(といっても整備したのはここ最近かつ本当に必要最小限)を通すことで検証を実施しました。

事前にMySQL8.0を利用してCIが通ることまでは確認は取れており、ここで何か問題が見つかることはありませんでした。

また、MySQL5.7の時点で把握している一部の重そうなクエリについてを、この段階で本番相当のデータが格納されたMySQL5.7/MySQL8.0のDBインスタンスの両方に対して何度か同一のクエリを投げてベンチマークを取って比較する形での検証を実施しましたが、特別チューニングを要するレベルの劣化は観測されませんでした。

参照系を本番環境へ投入する

ここまでの過程で、Aurora V3を利用した際に致命的に機能不全になることはないだろうといった確証は得られたため、DBの向先変更のみでメンテナンスを要せず、何かあってもすぐにDBの向先変更によって戻せる参照系のクエリをAurora V3のリードレプリカに向けました。

Railsの複数DB機能を活用しているためprimary_replicaロールによって発行された参照クエリのみがAurora V3のリードレプリカに対して発行されるため、この段階で全ての参照系のクエリがAurora V3に対して発行されるようになった訳ではありません。

なので割と気楽に事前投入を実施できた といった側面もあります。

primary_replicaロールといった単語を前触れもなく出してしまったので、何のことか把握出来るようにconfig/database.ymlの中身を一部抜粋したものを貼っておきます。

production:
  primary:
    adapter: trilogy
    encoding: utf8mb4
    ...
  primary_replica:
    adapter: trilogy
    encoding: utf8mb4
    ...

特定の画面でOut of sort memoryが発生する

参照系を本番に投入したところ、特定の画面でOut of sort memoryが発生して画面が閲覧できない状態になっていることに気づきました。
このエラーを観測した時点で一旦Aurora V2へ参照系の向先を切り戻しています。

また、本番相当の検証環境でも同等のエラーを観測出来たため以後はこの再度この環境を利用して検証を実施しました。

内容としてはjson型のカラムを持ったhogeテーブルに対してORDER BY(非json型カラムであっても)を指定した際に、sort_buffer_sizeのメモリ内でソートをし切れずにエラーとなっているようでした。

SELECT
  `hoge`.*
FROM `hoge`
ORDER BY `hoge`.`created_at` DESC;

同様の事象がMySQLのバグとして報告されています。
https://bugs.mysql.com/bug.php?id=103225

今(執筆時点)で改めて見たところとMySQL 8.0.40以降では修正されてそうな報告がされていました。
なので移行がこれからの方はMySQL 8.0.40以降に対する互換性を持ったAurora V3のバージョンを利用することで同様の問題には遭遇しなくなるものと思われます。

sort_buffer_sizeのデフォルトが256KBであり、STORES 予約では特にこの値をいじっていなかったため、大きめの値(2MB)を割り当てたところ動作するようになったため、それで乗り切っています。
MySQL8.0.12以降ではsort_buffer_sizeは必要に応じて段階的にメモリに割り当てられるようになっているため、ある程度大きめの値を割り当てても無駄に割り当てられ過ぎてしまうといったことにはならないようです。 https://dev.mysql.com/doc/refman/8.0/ja/order-by-optimization.html#order-by-filesort

この問題の解消後、再度本番に投入した後には大きな問題は見つかりませんでした。

更新系を含めた完全移行

先述した移行手順通り移行作業を実施し、めでたく更新系もAurora V3を向き完全移行することが出来ました。
移行後もユーザー操作起因では特に問題なく現在まで稼働し続けております。

移行後に発生したこと

ユーザー操作起因では特に問題なかったのですが、STORES 予約では社内のデータ基盤であるBigQueryに対して、日時でEmbulkを利用したデータ転送を行っています。
このデータ転送自体は日次で実行しているのですが、移行翌日からタイムアウトで連携が失敗してしまう事象が多数発生するようになりました。

このデータ連携処理はテーブル単位で全件抽出して転送しており、基本的には以下のようなクエリが発行されています。

SELECT
  `hoge`.*
FROM `hoge`;

よくよく精査せずとも、レコード数の多いテーブルが軒並みタイムアウトするようになった といったところまではすぐに気づけました。

Embulkのデフォルトのソケットタイムアウトは30分ですが、移行以前から一部のデータ量の多いテーブルでは60分等に伸ばされており、それ以上タイムアウトを伸ばすという対応は現実的ではないため対策を講じる必要がありました。

MySQL8.0からはデフォルトで一時テーブルにTempTableストレージエンジンが利用される

事前検証や影響範囲の洗い出しでは完全に見落としてしまっていたのですが、MySQL8.0では一時テーブルで利用されるストレージエンジンがTempTableストレージエンジンというものに変更されています。
MySQL5.7ではMEMORYストレージエンジンというものが利用されており、一時テーブルとして利用されるメモリのサイズはtmp_table_size, max_heap_table_sizeというパラメータで制御されていましたが、TempTableストレージエンジンではまた別のパラメータで制御する必要があり、それらはtemptable_max_ram, temptable_max_mmapが該当します。

つまり、temptable_max_ram, temptable_max_mmapはノーマークでデフォルトの値で稼働させていた状態になります。

temptable_max_ram, temptable_max_mmapのデフォルトの値は利用するDBのインスタンスサイズによっても変わってきそうですが、STORES 予約がデータ連携用に利用しているインスタンスサイズ(r6g.2xlarge)では以下の値が設定されていました。

mysql> show variables like 'temptable_max%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| temptable_max_mmap | 1073741824 |
| temptable_max_ram  | 1073741824 |
+--------------------+------------+
2 rows in set (0.01 sec)

テーブル全体のデータ量が1GiBを超えていた場合、一時テーブルがディスク上に作成されるようになってしまったため、移行前よりもデータの抽出処理に時間を要するようになっていました。

最終的にはtemptable_max_ramに8GiBを指定したところ、ほとんどのクエリではAurora V2(MySQL5.7)の頃と遜色ないパフォーマンスで動くようになりました。

一部これでも改善しないクエリが存在し、そこへの対処に一番苦労したのですが、素直なSELECTクエリではなく、対処も特殊だったため今回は割愛します。

ここでAurora V2の頃のtmp_table_size, max_heap_table_sizeの値がどうであったかを確認したところ、temptable_max_ram, temptable_max_mmapのデフォルトのサイズ(1GiB)よりも小さい値が指定されていたため、何故これだけクエリが失速してしまったかには疑問が残ります。

単純なSELECTなのでもしかしたらSELECT COUNT(*)が失速する以下の問題と関連があるのかなと辺りを付けていますが、実際の関連性は掴めていません。 https://bugs.mysql.com/bug.php?id=97709

また、こちらの問題は上記の報告にある通りMySQL8.0.37で解消されているため、これから移行を検討される方は迷わず8.0.37以降に対して互換性のあるエンジンバージョンを選択することを強くオススメします。 (私たちの移行時にはまだ8.0.37互換のエンジンバージョンはリリースされていませんでした。)

とはいえ、データ連携以外ではこの問題による影響を受けた箇所を見受けられなかったため、そういった点ではSTORES 予約は雑に大量データをSELECTする要件が盛り込まれていないといったことの裏返しにもなるのでアプリケーションの仕様面ではよく調整されたシステムなんだなと感じました。

この項の最後に、TempTableストレージエンジンについてはAWSから出ている以下の記事を読むと把握しやすかったので紹介しておきます。
https://aws.amazon.com/jp/blogs/database/use-the-temptable-storage-engine-on-amazon-rds-for-mysql-and-amazon-aurora-mysql/

まとめ

ここまでSTORES 予約でのAurora V3への移行までの流れと、移行前後で生じたトラブルシューティングについてを紹介していきました。
これから移行を検討される方は私たちと同じ轍を踏まないことを願っています。