inSmartBank

B/43を運営する株式会社スマートバンクのメンバーによるブログです

Aurora MySQLをMySQL8.0へ移行した話

Engineering

こんにちは！SREを担当してます上平と申します。 このエントリーではAurora MySQL5.7互換からMySQL8.0互換への移行を実施した際の流れや学びに関して紹介したいと思います！

B/43 では Aurora MySQL5.7系をサービスリリースから使っており、Aurora MySQL バージョン2のサポート終了日（2024/10/31）が近づいているのもあったので、移行することにしました。

Amazon Aurora バージョン - Amazon Aurora

これからAurora MySQL8.0へ移行を検討されている方の参考になれば幸いです。

想定される読者

  • Aurora MySQL 5.7系を使っていて、アップグレードを検討している方
  • 実際の Aurora MySQL 8.0 への移行手順を知りたい方
  • AWS インフラに興味がある方

前提

Aurora MySQL5.7互換 から Aurora MySQL8.0互換 への移行として記事を書きますが、Aurora Version 2 から Aurora Version 3 への移行を意味するものとし同義とさせてください。

実施日

今回のMySQL8.0化メンテナンスは以下の日程で実施しました。

1点問題があり、メンテナンスを2回実施する結果となりましたが、そのあたりの学びに関してもお伝えできればと思います。

内容 日付(期間)
事前準備1回目 2022年7月〜2022年9月
事前準備2回目 2023年1月〜2023年3月
メンテナンス1回目 2023年3月28日
メンテナンス2回目 2023年4月18日

事前に調べたこと

まずは事前準備として行なった、移行手段、性能面の調査、インフラ面の調査、そしてQAについて紹介します。

基本的にはSREとサーバーサイドが協力して課題を出し合い、検証していくスタイルで実施しました。

移行手段

まず、MySQL8.0へ移行する方法として何がいいかを検討しました。事前準備を始めた2022年7月時点ではインプレースアップグレードがリリースされておらず移行方法に頭を悩ませました。

AWSの担当とオンラインMTGにて方法を検討したり実際ステージング環境を使って検証したりしましたが、手間がかかる点で悩み、2022年9月に1度MySQL8.0化を延期しました。

しかし、9月末にインプレースアップグレードがリリースされたため、2023年に改めてMySQL8.0化PJを再発足することになります。

Amazon Aurora が MySQL 5.7 から 8.0 へのインプレースアップグレードをサポート

改めてステージング環境を使って検証を実施して、問題無いと判断したため、インプレースアップグレードを用いて移行することにしました。

性能面

MySQL8.0にすることで問題が発生するかをSRE・サーバーサイドと確認し合いました。その結果、以下の3点が現状のシステムに影響を与えかねない問題であると洗い出すことができました。

(詳しくは弊社@ohbaryeさんのblogリリースに期待)

  1. collation
  2. 暗黙の昇順ソート
  3. クエリによるCPU負荷

1. collation

MySQL8.0から、utf8mb4のデフォルトの collationが utf8mb4_general_ci から utf8mb4_0900_ai_ci になるという変更があります。明示的にcollationを設定していない場合は utf8mb4_0900_ai_ci になってしまうという問題です。

対応としては我々のシステムではutf8mb4_general_ci を使用していたため、引き続き同じcollationを使うよう全アプリの接続設定を確認したうえで、collation指定が厳密でなかった過去のmigration scriptを修正しました。加えてDBパラメータグループにて以下を utf8mb4_general_ci に設定しました。

  • collation_connection
  • collation_server

AWSのDBパラメーターグループで default_collation_for_utf8mb4 を設定できれば良かったのですが、現状は設定できないのでアプリケーション側での対応が必要です。

2. 暗黙の昇順ソート

この問題は、以下の内容ですが、弊社のアプリケーションコードでは影響を受けないことを確認しました。

GROUP BY 句の非推奨の ASC 修飾子または DESC 修飾子は削除されます。 以前に GROUP BY ソートに依存していたクエリーでは、以前の MySQL バージョンとは異なる結果が生成される場合があります。 特定のソート順序を生成するには、ORDER BY 句を指定します。

3. クエリによるCPU負荷

MySQL 8.0のパラレルスキャンでは、テーブル全件に対する SELECT COUNT(*) の処理で、バッファプールがいっぱいになり古いデータページを追い出す必要がある状況下の処理で低速になるという問題です。

これはAWSにも確認しました。(2023/03/13時点)

当方にて調査致しましたところ、MySQL バージョン 8.0 において、SELECT COUNT(*) のパフォーマンスに関係するバグ[1][2]が存在することを確認しました。 こちらのバグは、MySQL バージョン 8.0 および Aurora MySQL バージョン 3 では現時点で未だ修正されておらず[3][4]、ご利用の際に当該バグの影響を受ける可能性がございます。 しかしながら、実際にパフォーマンスに影響するかといった点につきましては、お客様ワークロードに依存するものでございますため、一概にご案内差し上げることが難しい点、ご理解賜りますようお願い申し上げます。

AWSのAurora Version3でも SELECT COUNT(*) でCPU負荷が上がる懸念があり、弊社環境では管理画面のページングの際にこのクエリが使われていました。

サービス的な問題ではないものの、エンドユーザー向けのAPIサーバーと管理画面のAPIサーバーは同じDBを参照しているため対応が必要となります。

万が一のため SHOW TABLE STATUS で件数を取得する方法を準備していたものの、MySQL8.0になってから1ヶ月間、エンドユーザー向けDBでは count による高負荷は発生しませんでした。

そのため、弊社のエンドユーザー向けDB環境では影響を受けないと判断しました。

インフラ面

その次に、インフラ面で問題がないかを検証しました。

今までステージング環境などで使用していたsmallが使えなくなり、最低インスタンスタイプがmediumになります。

開発 / ステージング 環境にて先行して MySQL8.0 に移行し、動作検証を実施しました。

また、インプレースアップグレードを実施して完了するまでの移行時間計測も実施し、データ量によって移行時間が変わるのかを検証しました。

結果、データ量に左右されるものの弊社の環境では大きな差はありませんでした。

環境 データ容量 移行時間
ステージング環境 約400MB 約15分
本番環境 約60GB 約30分

QA

弊社では本番相当のデータを使って検証する場面が多いため、本番DBから個人情報などの重要なカラムをマスクする機構があります。その環境を使って数週間QAを実施しました。

QAはフロントエンド、サーバーサイド、SREの全員が協力して必要な時間を確保して集中して取り組みました。

結果的には大きな問題はなく、弊社エンジニア陣の団結力と問題解決力が高いことを実感しました。

移行方法

弊社がメンテナンスで行った実際の移行方法を紹介させていただきます。

メンテナンス当日は私を含めたSREの2名で実施しました。

前日からメンテナンス作業書の読み合わせを実施して当日の作業イメージを共有します。また、読みあわせにて生じた問題点や改善点を話し合い、メンテナンスによるダウンタイムをいかに短くするかを検討します。

メンテナンス当日の流れは以下のような感じです。

  1. メンテナンス事前作業
    1. バッチなどメンテナンス中に動くものを事前に停止
  2. メンテナンス実施
    1. ALBにてメンテナンスを設定します

  3. AWSコンソールからAurora MySQL8.0へのインプレースアップグレードの実施

    • インプレースアップグレードでは pre-upgrade checks という確認を実施してくれますがここで1回目のメンテナンスで問題が発生しました。問題点については後述します。

  4. 今回はRDS Performance Insightも有効にしました

    • コンソールからパフォーマンスインサイトを有効にすると、見た目上は有効になりますが、凡例が意味分からないものになってしまいます。

      • 正常な凡例

      • おかしな凡例

    • これは色々とAWSとやり取りしました。t4g.mediumではうまく performance_schema が有効にならないらしいです。(弊社のr6g.xlargeのインスタンスだと自動で有効になります)

    • そのため、パラメータグループにて以下を手動で設定しております。

        performance_schema 1
  performance-schema-consumer-events-waits-current ON
  performance-schema-instrument wait/%=ON
  performance_schema_consumer_global_instrumentation ON
  performance_schema_consumer_thread_instrumentation ON

  5. Fargateの起動確認

    • AuroraのメンテナンスによってDBとの接続が切れるため、コンテナの起動を確認する
    • 弊社ではrds-proxyを導入しているため、問題にはならなかった。

  6. アプリから動作確認

    • SREはメンテナンスをすり抜ける仕組みがあります

  7. ALBのメンテナンスを解除します

  8. 停止したバッチの実行

移行するときに問題になったこと

1回目のMySQL8.0化メンテンスの際、 pre-upgrade checks にてエラーが発生しました。

エラー内容は以下のような内容でAWSに問い合わせしました。

{
    "id": "auroraUpgradeCheckForSpecialCharactersInProcedures",
    "title": "Check for inconsistency related to special characters in stored procedures.",
    "status": "ERROR",
    "description": "Invalid default value for 'modified'"
}

結論から言うと、弊社環境ではsql_mode を設定しており、以下の両方を設定していた場合に問題がありました。

  • STRICT_TRANS_TABLES
  • NO_ZERO_DATE

この設定は pre-upgrade checks でのみエラーになるため、STRICT_TRANS_TABLES or NO_ZERO_DATE の設定が必要な場合は、移行前に一旦設定を削除し、移行完了後に設定を行ってください。

パラメータグループはほぼほぼデフォルト状態だったので、移行に影響するとは思っておらず、ステージング環境でテストできなかったのは盲点でした。。

改めてステージングで再発することを確認し、検証後、手順を確立して2回目のメンテナンスにて無事にMySQL8.0にできました。

ちなみに pre-upgrade checks は以下の項目を確認していました。

oldTemporalCheck
reservedKeywordsCheck
utf8mb3Check
mysqlSchemaCheck
nonNativePartitioningCheck
foreignKeyLengthCheck
maxdbFlagCheck
sqlModeFlagCheck
enumSetElementLenghtCheck
partitionedTablesInSharedTablespaceCheck
circularDirectoryCheck
removedFunctionsCheck
groupByAscCheck
depreciatedSyntaxCheck
removedSysLogVars
removedSysVars
sysVarsNewDefaults
zeroDatesCheck
schemaInconsistencyCheck
engineMixupCheck
getDanglingFulltextIndex
getMismatchedMetadata
getEventsWithNullDefiner
getDuplicateTriggers
getValueOfVariablelower_case_table_names
getTriggersWithNullDefiner
checkTableOutput
auroraCheckDDLRecovery
auroraFODUpgradeCheck
auroraUpgradeCheckIndexLengthLimit
auroraUpgradeCheckIndexLengthLimitOnTinytext
auroraUpgradeCheckMissingInnodbMetadataForMysqlHostTable
auroraUpgradeCheckColumnDatatypeMismatchForSysTables
auroraUpgradeCheckForIncompleteXATransactions
auroraUpgradeCheckForFtsSpaceIdZero
auroraUpgradeCheckForRollbackSegmentHistoryLength
auroraUpgradeCheckForUncommittedRowModifications
auroraUpgradeCheckForSpecialCharactersInProcedures
auroraUpgradeCheckForDatafilePathInconsistency
defaultAuthenticationPlugin

移行後どうなったか

無事にMySQL8.0への移行は終わったのですが、その後の傾向に関してお伝えできればと思います。

移行後に問題になったのは以下の1点でした。

問題になったこと

tmp table 溢れ

弊社ではRedashを使って統計情報を取得してるのですが、DBは専用リードレプリカに向けてクエリを発行しています。

とあるクエリを発行すると以下のエラーが出るようになりました。

The table '/rdsdbdata/tmp/#sql107_168c_0' is full

tmp tableの枯渇と考え以下の設定変更の対応を実施しましたが改善しませんでした。

  • tmp_table_size
  • max_heap_table_size

結論から言うと MySQL8.0.26 のバグでした。

MySQL Bugs: #99100: GROUP BY will throw table is full when temptable memory allocation exceed limit

では internal_tmp_mem_storage_engine を MEMORY に変更してみてはと思い、変更してみたが、エラーは同じでした。。 MEMORY にしたのに、 /rdsdata/tmp っておかしくないかと思い、AWSに問い合わせしたところ、以下の回答がありました。

お申し出いただいております事象について、リーダーDBインスタンスにて発生しているものと認識しております。 DBパラメータ internal_tmp_mem_storage_engineの設定でございますが、以下ドキュメント[1]に記載がございますが、プライマリDBインスタンスへのみ変更の反映がなされます。 そのため、リーダーDBインスタンスに適用していただいているDBパラメータグループのinternal_tmp_mem_storage_engineをMEMORYに変更していただいても、当該事象が解消されなかったものであると考えております。

個人的に勉強不足で知らなかった内容で学びでした。MySQL8.0.27 では改善されているらしいです。

このパターンはクエリを修正するか Auroraバージョンで MySQL8.0.27パッチが適用されるのを待つしかなさそうです。。

パフォーマンス面

countでCPU負荷

エンドユーザー向けのDBでは問題にはならないとお伝えしましたが、pushを配信するためのシステムのDBにてcountを発行している箇所がありました。サービスで使用しているわけでは無く、redashからの統計にて使用していました。

MySQL8.0にしたことによって今までCPU50%ぐらい使っていたクエリがMySQL8.0によって90%オーバーという結果になってしまいました。。

実行時間は2,3分なのでサービスに影響することはなくホッとしておりますが、countにはご注意ください🙇 

こちらのクエリに関しては現在対応中になりますが、統計の期間などを絞るしかなさそうです。。

またpushのDBのインスタンスタイプはt4g.mediumなので、スペックが低いとCPU負荷が露骨になるのかもしれません。

  • before

  • after

良かった点

弊社エンジニアやPM陣からMySQL8.0にしてよかった点に関しての声をいただきました！

  • セミジョインで高速化したクエリがあったはず
  • AUTO_INCREMENT周りの悩みが減った！
  • window関数が使えて感動！！！
  • migrationがちょっと安全になった！
    • 既存テーブルへのデフォルト値つきカラム追加が安全になった
    • 5.7以前ではテーブル全体が書き換わるため書き込みがブロックされてた
  • 共通テーブル式（CTE）が使えるぅ〜

こういう声を聞けると苦労してMySQL8.0化を実施してよかったと思います👍

まとめ

今回は Aurora MySQL5.7互換(Version 2)からAurora MySQL8.0互換(Version 3) への移行に関して書かせていただきました。

大規模なシステム変更の1つであるデータベースのバージョンアップなどは開発グループが一丸となり取り組むものだと実感しました。本当に弊社のエンジニア陣には感謝です。

その中でも事前調査での性能面、インフラ面に関してそれぞれのチームで話し合い確認し合い、新たな気づきや違う立場での視点がありすごく学びに繋がりました。

反省点としてはメンテナンスを1回で終わらせられなかった点で、やはりステージング環境ですべて本番と同じ状態で検証するということの大切さを学びました。

MySQL8.0化を実施した後に踏んでしまったバグなどはありましたが、MySQL8.0にすることでエンジニアやPM陣からの喜びの声を聞けてMySQL8.0化を実施してよかったと実感しております。

これからMySQL8.0化を検討されている方がおられたらこのブログを少しでも参考にしていただき、移行の助けになれば嬉しいと思います。

We create the new normal of easy budgeting, easy banking, and easy living.
In this blog, engineers, product managers, designers, business development, legal, CS, and other members will share their insights.