はじめまして。プロダクト開発部に所属しているエンジニアの姚です。
今回は「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の廃止/変更機能＋非推奨機能をリストアップして、システムに影響があるかを確認します。
アプリケーションへの影響が大きい変更
- 廃止/変更機能
- 予約語
- GROUP BY暗黙/明示的のソート順変化
- クエリーキャッシュ廃止
- 非推奨機能
- 文字セット(utf8)
- システム変数explicit_defaults_for_timestamp
- 課題
予約語
MySQL5.6, 5.7, 8.0のキーワードーと予約語変更の公式ドキュメント：
Keywords and Reserved Words
予約語リスト
参考として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名、テーブル名、カラム名などの識別子としてそのまま使うとダメなので、バッククォートで囲みます。
CREATE TABLE rank (id INT PRIMARY KEY, val INT); ❌
CREATE TABLE `rank` (id INT PRIMARY KEY, val INT); ✅
GROUP BY暗黙/明示的なソート順変化
ソート順を指定しないとき、GROUP BYカラムによる暗黙の昇順ソート （ASCが省略されているもの）が8.0以後なくなります。
参考：ORDER BY の最適化
変更点
- GROUP BYの暗黙/明示的ソート順変化
SELECT hoge FROM fuga GROUP BY hoge;
SELECT hoge FROM fuga GROUP BY hoge ORDER BY ASC;
SELECT hoge FROM fuga GROUP BY hoge ORDER BY NULL;
2. GROUP BYのASCとDESCの付け方
SELECT hoge FROM fuga GROUP BY hoge ASC;
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作成前にセッションの照合順序を明示的に指定します。
SET @@session.collation_connection = 'utf8mb4_general_ci';
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 の動作は以下となります。
INSERT INTO hoge(..., created_tm, create_user)
values (..., null ,"aaa") ❌
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 へのバーション変更の影響範囲はかなり広いです。ドキュメントやコードを見るだけで全ての影響点を把握しきれてない可能性があります。
全機能テストで問題を検知したこともありますから、しっかりテストを行った方が安心です。
