Aurora1からAurora3へアップグレードするときのアプリケーション対応の注意点について

アイキャッチ

はじめまして。プロダクト開発部に所属しているエンジニアの姚です。
今回は「Reader Store（運営：株式会社ソニー・ミュージックエンタテインメント）」のDBをAurora1からAurora3へアップグレードしたときにアプリケーション側が対応したことを話していきます。

Aurora3導入の背景

Amazon Aurora MySQL 1 (MySQL 5.6 互換) は 2023 年 2 月 28 日にサポート終了となります。
サポート期間が終了する前にアップグレードしないといけません。
また、Aurora MySQL 2 (MySQL 5.7 互換) は 2024 年 10 月 31 日にサポート終了となります。
＊2022年11月時点、最新の情報は下記公式ページにご参照ください。
Amazon Aurora メジャーバージョンが利用可能な期間

Aurora2にアップグレードしても、二年後にもう一度アップグレードすることになり、二度手間になりますので、今回はAurora1からAurora3へアップグレードすることにしました。

方針

MySQL8.0ではマイナーバージョンアップ時に後方互換を担保しないため、非推奨機能は使えなくなる可能性があります。Aurora3ではまだLTS版が出てないため、AWSのサポート期間が短かいことも予想されます。
マイナーバージョンアップ時に規模の大きな改修を行わないで済むように、アプリケーションへの影響がでる非推奨機能も調査して事前対応することとしました。

対応の流れ

アプリケーション改修

開発環境の設定変更、DBエンドポイント変更、DBドライバーのバージョンアップ
事前調査結果に基づき、MySQL8.0で廃止/変更機能、非推奨機能の改修
動作検証で検知した問題の追加改修

インフラ

MySQL8.0でシステム変数のデフォルト値への対応
アプリケーション改修と並行してAurora3の環境構築とデータ移行

動作検証

全機能テスト
負荷試験

リリース準備

リリース手順の整備（リカバリー手順も含む）
リハーサル

MySQLのバージョン変更による影響調査

基本は公式ドキュメントを参照し、MySQL5.7、MySQL8.0の廃止/変更機能＋非推奨機能をリストアップして、システムに影響があるかを確認します。

公式ドキュメント参考
- MySQL 5.7 での変更（英語）
- MySQL 8.0 での変更

アプリケーションへの影響が大きい変更

廃止/変更機能
- 予約語
- GROUP BY暗黙/明示的のソート順変化
- クエリーキャッシュ廃止
非推奨機能
- 文字セット(utf8)
- システム変数explicit_defaults_for_timestamp
課題
- 一時テーブルメモリ不足のチューニング

予約語

MySQL5.6, 5.7, 8.0のキーワードーと予約語変更の公式ドキュメント： Keywords and Reserved Words

予約語

下方にキーワード表がありますが、「(R)」が付いているのが予約語です。
キーワードだけど非予約語

非予約語はテーブル名などの識別子として、そのまま使っても大丈夫です。

予約語リスト

参考としてMySQL5.6から8.0の間で変更がある予約語を下記にリストアップします。

追加された予約語（31件）

CUBE
CUME_DIST
DENSE_RANK
EMPTY
EXCEPT
FIRST_VALUE
FUNCTION
GENERATED
GROUPING
GROUPS
JSON_TABLE
LAG
LAST_VALUE
LATERAL
LEAD
NONBLOCKING
NTH_VALUE
NTILE
OF
OPTIMIZER_COSTS
OVER
PERCENT_RANK
RANK
RECURSIVE
ROW
ROWS
ROW_NUMBER
STORED
SYSTEM
VIRTUAL
WINDOW

削除された予約語（1件）

ONE_SHOT

予約語の対応

DB名、テーブル名、カラム名などの識別子としてそのまま使うとダメなので、バッククォートで囲みます。

-- NG
CREATE TABLE rank (id INT PRIMARY KEY, val INT); ❌

-- バッククォートで囲めばOK
CREATE TABLE `rank` (id INT PRIMARY KEY, val INT);　✅

GROUP BY暗黙/明示的なソート順変化

ソート順を指定しないとき、GROUP BYカラムによる暗黙の昇順ソート（ASCが省略されているもの）が8.0以後なくなります。
参考：ORDER BY の最適化

変更点

GROUP BYの暗黙/明示的ソート順変化

-- 発行のSQL
SELECT hoge FROM fuga GROUP BY hoge;

-- 5.7までの動作
SELECT hoge FROM fuga GROUP BY hoge ORDER BY ASC;

-- 8.0での動作
SELECT hoge FROM fuga GROUP BY hoge ORDER BY NULL;

2. GROUP BYのASCとDESCの付け方

-- 5.7まで
SELECT hoge FROM fuga GROUP BY hoge ASC;

-- 8.0
SELECT hoge FROM fuga GROUP BY hoge ORDER BY ASC;

GROUP BY仕様変更対応

GROUP BYでソート順を指定していない箇所に、GROUP BYカラム順にソートするように明記
GROUP BYにASCとDESCが付いているがORDER BYがない箇所にORDER BYを追加

クエリーキャッシュ廃止

クエリーキャッシュは5.7.20で非推奨に、8.0で削除されます。過去バージョンでクエリーキャッシュが活用されていた場合に性能低下の可能性がありますので、パフォーマンスの変化に気をつける必要があります。

負荷試験を実施し、パフォーマンス劣化がありましたら、下記に限らず適切に対応します。

古いデータ削除やパーティションテーブルを利用して、クエリ対象のデータを減らす
インメモリデータベース（Redisなど）を使ったキャッシュを導入
ProxySQLを使ったキャッシュの導入※
インフラの増強

※参考:MySQL 8.0: Retiring Support for the Query Cache
ProxySQLのキャッシュは旧来のMySQLと異なり、データ更新のタイミングでキャッシュが破棄されないので注意が必要です

文字セット(utf8)

公式ドキュメントに記載がある通りutf8文字セットが非推奨になっています。

MySQLのutf8はutf8mb3のエイリアスなので、utf8mb3 文字セットに合わせて非推奨となります。将来の MySQL リリースで削除される予定のため、かわりに utf8mb4 を使用しようした方が良さそうです。

照合順序

照合順序はデータの文字の大小関係を比較する場合の基準となるものです。

MySQL 5.6は文字セットutf8の照合順序がデフォルトutf8_general_ciとなっています。MySQL 8.0の文字セットutf8mb4のデフォルトの照合はutf8mb4_0900_ai_ciになります。

utf8mb4_0900_ai_ciだと絵文字（🍣、🍺など）が区別できません。ソート順などを現行のまま保持したい場合は、MySQL 8.0ではutf8mb4_general_ciを明示的に指定する必要があります。

注意点
データベース、テーブル、カラムが変更対象です。文字セットを変更するときCOLLATIONを明示的に指定します。

ALTER TABLE `fuga`.`hoge`
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

viewは作成時の照合順序が適用されるので、テーブルの照合順序を変更した後でviewを作り直す必要があります。
なお、viewの照合順序は指定できませんが、view作成時のセッションと同じ照合順序になるためview作成前にセッションの照合順序を明示的に指定します。

-- view作成前にcollationをutf8mb4_general_ciに指定
SET @@session.collation_connection = 'utf8mb4_general_ci';

-- viewを作り直す
CREATE OR REPLACE VIEW `fuga`.`hoge_view` AS
  SELECT concat('ABC',`hoge`.`hoge_column`)
  FROM `hoge`
  WHERE ...;

文字セット移行変更方式の検討

プランA. Aurora3へアップグレード後、ALTERを発行して文字セットを変更する

メリット
- 移行手順が簡単
- データ整合性は保証できる
デメリット
- 照合順序変更すると、インデックスも作り直されるので時間がかかる
- 文字セット変換のAlter文実行中は、テーブルがロックされるのでサービス稼働中に実施できない

プランB. DMSを使ってストア稼働中に文字セットを変換する

メリット
- 移行中でもデータベースは利用可能の状態になる
デメリット
- データ整合性のチェックが必要となる

DMSを使った場合の具体的な対応は下記のイメージになります。

移行先のDBを作成し、utf8mb4に文字セットを変更
AWS DMSでレプリケーション実施
データが一致していることを確認

Amazon Database Migration Serviceを使えば、文字セットが違うDB間でもデータレプリケーションが可能です。
データ量が少ないDBはプランAで対応しやすいですが、データ量が多いDBやサービスを長時間停止することを避けたい場合は、プランBで対応する方が良さそうです。

システム変数explicit_defaults_for_timestampをOFFにするのは非推奨

explicit_defaults_for_timestampとは

システム変数explicit_defaults_for_timestampはtimestampカラムにnull値セットの処理を有効にするかどうかを決定します。
OFFになっているとき、timestampカラムにnullをセットできますが、ONにするとnullはセットできなくなります。
詳細はMySQL 公式ページをご参照ください。

MySQL 8.0 系においてexplicit_defaults_for_timestampの設定が非推奨であり、デフォルト値 ON の動作は以下となります。

-- NG timestamp null insert
INSERT INTO hoge(..., created_tm, create_user)
values (..., null ,"aaa") ❌
-- OK 
INSERT INTO hoge(..., create_user)
values (..., "aaa") ✅

ORMを利用している場合、Entityのtimestamp項目に値を未設定のまま永続化するとtimestampにnull insertが発生する可能性があります。
なお、Aurora 1において、explicit_defaults_for_timestampを設定せず、無効化になっている事象があります。
アプリケーションがnull insertのSQLを発行しているかを気をつかないといけません。

対策として、explicit_defaults_for_timestampをOFFにすることで、timestampにnull insertのパターンも正常動作できます。
Aurora 3においてパラメータグループで explicit_defaults_for_timestamp の値は変更可能ですが、実際には反映されないので注意が必要です。

AWSサポートに問い合わせをしたところ、下記対応方法を回答いただきました。

explicit_defaults_for_timestamp を無効化する必要がある場合には、アプリケーション等でのセッション開始時に "set explicit_defaults_for_timestamp=0;" を実行いただく必要がございます。
あるいは、ドキュメントの「explicit_defaults_for_timestamp が有効になっている場合...」以下の動作を前提としてアプリケーションを修正いただくこともご検討ください。

Reader Storeにおいては、アプリケーション側でtimestampにnull insertされないように対応しました。

内部一時テーブルメモリ不足のチューニング

MySQL 8.0にアップグレード後、データ量が多いテーブルは、内部一時テーブルメモリが不足する恐れがあるので、チューニングを考慮する必要があります。

内部一時テーブルと使用するストレージエンジンについて

MySQLでは一部のクエリで内部一時テーブルを作成してます。

使用されるストレージエンジンについて、MySQL 5.6ではMemoryしかありません。
MySLQ 8.0からは従来のストレージエンジンであるMemoryに加えて、TempTableが追加され、デフォルトエンジンになります。
ストレージエンジンの仕組みは、AWSのドキュメントをご参照ください。

Aurora 3での注意点

Aurora3 MySQL8.0.23のTempTableに不具合がある

指定した一時ファイルの割り当てサイズよりデータが多かった場合に、本来は使用すべきInnoDBの内部一時テーブルが使用されず、エラーが発生します。
参考：https://forums.percona.com/t/mysql-8-0-the-table-tmp-sql1-f519f-7-is-full/10767
Aurora3 で利用可能なストレージエンジン

パラメーターグループのinternal_tmp_mem_storage_engineでエンジンを指定できますが、リーダーインスタンスはTempTableだけが使えます。

対策

従来のMemoryを使用することにより回避できます。リーダーインスタンスでTempTableを使用する場合は、現行のテーブルサイズから一時テーブルに使用される容量を見積もり、一時テーブルのパラメーターに適切な値を指定します。
TempTableモードで使用されるストレージ上の内部一時テーブルのサイズが不足するとSQLエラーとなってしまうため、temptable_max_mmapに余裕あるサイズを指定します。

まとめ

今回はアプリケーションエンジニアの視点から、Aurora3へのアップグレードの内容をまとめてみました。
作業するときインフラ側と密にやり取りしたり、周りの協力を得られるとスムーズに進められそうです。

MySQL8.0 へのバーション変更の影響範囲はかなり広いです。ドキュメントやコードを見るだけで全ての影響点を把握しきれてない可能性があります。
全機能テストで問題を検知したこともありますから、しっかりテストを行った方が安心です。

booklista tech blog

booklista のエンジニアリングに関する情報を公開しています。