TUNAGのDBをAurora MySQL v3にアップグレードしました

はじめに
事前の検証作業
本番アップグレード作業
振り返りと今後について
まとめ

はじめに

プラットフォーム部 SREチームのショウゴ(@shogo_452)です。 TUNAGのメインデータベースをAmazon Aurora MySQL v2(MySQL5.7互換)からv3(MySQL8.0互換)にアップグレードしたので、その検証内容などアップグレード完了に至るまでの過程を共有します。

事前の検証作業

MySQL 5.7 ⇒ 8.0の変更点の影響確認

まずは、MySQL 5.7(以下、5.7)からMySQL 8.0(以下、8.0)へのバージョンアップにおける変更点とその影響有無の調査を行いました。

1. 照合順序

照合順序は、文字の並び替えや比較のルールです。文字コードutf8mb4の照合順序のデフォルトが、8.0でutf8mb4_general_ciからutf8mb4_0900_as_ciに変更になりました。 TUNAGでは、明示的にutf8mb4_general_ciを指定していたため、影響はありませんでした。

各文字セットにはデフォルト照合があります。たとえば、utf8mb4 および latin1 のデフォルトの照合は、それぞれ utf8mb4_0900_ai_ci および latin1_swedish_ci です。

dev.mysql.com

2. 暗黙のソート

5.7では、GROUP BY句で暗黙的にソートが行われていましたが、8.0からはこの暗黙のソートがなくなったため、ORDER BY句を使う必要があります。 TUNAGでは暗黙のソートに依存している箇所はなかったため、影響はありませんでした。

以前は (MySQL 5.7 以下)、GROUP BY は特定の条件下で暗黙的にソートされていました。 MySQL 8.0 では発生しなくなったため、暗黙的ソートを抑制するために最後に ORDER BY NULL を指定する必要はなくなりました (前述のとおり)。ただし、クエリー結果は以前の MySQL バージョンとは異なる場合があります。特定のソート順序を生成するには、ORDER BY 句を指定します。

dev.mysql.com

3. 予約語

8.0では、新たな予約語が追加されましたが、 TUNAGで使用しているRailsでは、テーブル名とカラム名をバッククォートでエスケープしてくれるので影響はありませんでした。

dev.mysql.com

アップグレード方法の検討

最終的に「インプレースアップグレード」を選択しました。 Blue/Greenデプロイ(以下、B/Gデプロイ)も1つの選択肢としてあったのですが、既存のクラスターパラメータグループでbinlog_format=MIXEDに事前に変更する必要があり、8.0用の新規作成するカスタムパラメータグループでbinlog_format=MIXEDと設定することとし、今回はB/Gデプロイの選択を見送りました。

パラメータグループの作成

次に8.0用のカスタムパラメータグループの作成です。まずは、既存の5.7のカスタムパラメータグループのうち、デフォルトの値からカスタムされているパラメータの確認を行いました。確認の際は、AWS CLIとjqコマンドを用いてデフォルトパラメータグループとカスタムパラメータグループの内容をjsonファイル化し、両ファイルの差分をdiffで確認しました。この方法を取ることでカスタマイズをしている内容を漏れなく確認することができました。

$ aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name default.aurora-mysql5.7 | jq . > default-cluster-mysql5.7.json
$ aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name aurora-cluster-mysql5.7 | jq . > production-cluster-mysql5.7.json
$ git diff default-cluster-mysql5.7.json production-cluster-mysql5.7.json

$ aws rds describe-db-parameters --db-parameter-group-name default.aurora-mysql5.7 | jq . > default-instance-mysql5.7.json
$ aws rds describe-db-parameters --db-parameter-group-name stats-aurora-mysql | jq . > production-instance-mysql5.7.json
$ git diff default.aurora-mysql5.7.json production-instance-mysql5.7.json

パフォーマンス確認

アップグレード済みのステージング環境の運用

まずは、何台かのステージング環境を8.0にアップグレードした状態で、他のエンジニアメンバーに普段の開発で使ってもらう運用を2週間程度行いました。これは、普段の開発の中で著しくパフォーマンスが遅くなる画面やAPIを見つけることを狙いました。

k6とDatadogを用いた主要画面のパフォーマンス確認

次に、コアな機能のAPIに対して、関連するレコード数が最多のケースでパフォーマンス確認を行いました。負荷試験ツールのk6を用いて、検証条件のケースをk6のシナリオに記述、リクエストを実行し、SQLの実行時間や画面の描画時間などの確認をDatadogのAPMを用いて行いました。この確認の結果、主要なAPIの一部で大幅なパフォーマンスの劣化が起きることが判明しました。

k6.io

実行計画の変化の確認

8.0へのバージョンアップでパフォーマンスが大幅に劣化したSQL(一部省略)が下記となります。

SELECT
    `table_a`.`id`,
    -- 省略
FROM
    `table_a`
    LEFT OUTER JOIN
    -- 省略
WHERE
    -- 省略
    AND `table_a`.`id` IN (
        SELECT
            `table_a`.`id`
        FROM
            `table_a`
            LEFT OUTER JOIN
            -- 省略
        WHERE
            -- 省略
            AND (
                `table_a`.`id` IN (
                    SELECT -- 👈 サブクエリ1
                        `table_b`.`table_a_id`
                    FROM
                        `table_b`
                    WHERE
                        -- 条件1(省略)
                )
                OR `table_a`.`id` IN (
                    SELECT -- 👈 サブクエリ2
                        `table_c`.`table_a_id`
                    FROM
                        `table_c`
                    WHERE
                        -- 条件2( 省略)
                )
                OR `table_a`.`id` NOT IN (
                    SELECT -- 👈 サブクエリ3
                        `table_c`.`table_a_id`
                    FROM
                        `table_c`
                    WHERE
                        -- 条件3(省略)
                )
            )
    )
ORDER BY `table_a`.`id` DESC;

5.7では、サブクエリ1, 2, 3の部分に対してEXISTS戦略による最適化が適用されていたのですが、 8.0にバージョンアップすると実体化を使用した最適化(materialization)が適用され、実行計画が変わることが分かりました。

サブクエリの最適化状況を確認する場合は、EXPLAIN後にSHOW WARNINGSステートメントで発行できる追加情報もしくはオプティマイザトレースを参照すると確認することができます。

EXPLAIN SELECT * FROM test_table;
SHOW WARNINGS;

dev.mysql.com

SET optimizer_trace='enabled=on';
EXPLAIN SELECT * FROM test_table;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

dev.mysql.com

SQLの改善

パフォーマンス劣化の原因となったSQLについては、様々な対策方法を検討した結果、最終的にEXISTS句とNOT EXISTS句を明示的に使用するように変更することで、5.7の実行計画を維持できるようになりました。

SELECT
    `table_a`.`id`,
    -- 省略
FROM
    `table_a`
    LEFT OUTER JOIN
    -- 省略
WHERE
    -- 省略
    AND `table_a`.`id` IN (
        SELECT
            `table_a`.`id`
        FROM
            `table_a`
            LEFT OUTER JOIN
            -- 省略
        WHERE
          -- 省略
            AND (
                EXISTS (
                    SELECT 1
                    FROM `table_b`
                    WHERE (`table_a`.`id` = `table_b`.`table_a_id`)
                    AND -- 条件1(省略)
                )
                OR EXISTS (
                    SELECT 1
                    FROM `table_c`
                    WHERE (`table_a`.`id` = `table_c`.`table_a_id`)
                    AND -- 条件2(省略)
                )
                OR NOT EXISTS (
                    SELECT 1
                    FROM `table_c`
                    WHERE (`table_a`.`id` = `table_c`.`table_a_id`)
                    AND -- 条件3(省略)
                )
            )
    )
ORDER BY `table_a`.`id` DESC;

本番アップグレード作業

本番のアップグレードは、上記のSQL改善を行った上で、ステージング環境で確認した手順で作業を行いました。アップグレード作業と合わせてCA証明書の更新も行いました。CA証明書は、負荷影響も考慮して既存のrds-ca-2019と同じアルゴリズムが使用されているrds-ca-rsa2048-g1を選択しました。

振り返りと今後について

事前の検証を入念に行ったこともあり、無事に何事もなくバージョンアップ作業を終えることができました。今回作成した8.0用のパラメーターグループでbinlog_format=MIXEDを設定したことにより、今後はB/Gデプロイを利用することができるため、次回はB/Gデプロイを試そうと思っています。また、メジャーバージョンアップの検証手順が今回確立でき、別のデータベースのAurora PostgreSQL 11.17 から Aurora PostgreSQL 15.4 へのアップグレード作業を控えていたため、早速手順を流用し先日アップグレードを無事終えました。