Amazon Aurora MySQL 2（MySQL 5.7 互換）へのアップグレードが完了しました

こんにちは、システム基盤部でソフトウェアエンジニアをしているshmokmtです。 READYFORでは主要のデータベースとしてAurora MySQLを採用しており、エンジンバージョンは1系（MySQL 5.6 互換）で長年運用をしてきましたが、先日のシステムメンテナンスで2系（MySQL 5.7 互換）に障害が発生することなくアップグレードすることができました。今回はその一連のアップグレード作業について書きたいと思います。

corp.readyfor.jp

なぜアップグレードするのか

Aurora MySQL v1のEOL

Amazon Aurora MySQL 互換エディションバージョン 1 (MySQL 5.6 互換) は 2023 年 2 月 28 日にサポートを終了する予定です。

Aurora MySQL v1はEOLが2023年2月28日と決まっており、それまでにアップグレードしない場合はメンテナンスウィンドウの中で強制的にアップグレードがかけられることになります。これ以上放置し続けることはできないため、優先度を最高レベルまで上げて対応することにしました。

docs.aws.amazon.com

EC2で稼働していたRailsアプリケーションをECSに移行するなどの作業は今までやってきたものの、 DBのアップグレード作業はどうしても優先順位が低くなり、本腰を入れて実施することができていなかったというのが正直なところでした。

また、運用上InnoDBロックモニターを使いたいが、Aurora MySQLのバージョンが古すぎるために使いにくい状態になるという運用上の課題も出てきていました。*1

事前準備

アップグレードの準備作業としてやったことの一部を紹介します。

アップグレード方法の決定

Aurora MySQLをアップグレードするためにはmysqldumpからのインポートやインプレースアップグレード、binlogレプリケーションなど様々な方法があります。今回はメンバーの熟練度、サービスとしての許容できるダウンタイムなどを考慮し、インプレースアップグレードを採用することにしました。

具体的なメリット/デメリットについてはANDPADさんの記事がよくまとまっていますので、そちらが参考になるかと思います。 https://tech.andpad.co.jp/entry/2022/06/02/100000

夜間メンテナンスの仕組みの整備

過去の夜間メンテナンス時の記録を見返したところ、夜間メンテナンスの仕組みや手順がコード化されていないということがわかりました。そこで中長期を見据え、これを機会に夜間メンテナンスをTerraform上で実施できるようにすることにしました。今回実現したかったことは以下の通りです。

メンテナンスモードの切り替えをTerraform上で実施する。（AWSコンソールやawscliで実施しない）
動作確認のため、作業者だけはメンテナンスモード中もサービス（ALB、S3等）にアクセスすることができる。
作業者以外がサービスにアクセスすると、メンテナンスページ（S3にアップロード）がHTTPステータスコード503（Service Unavailable）で表示される。

これらを踏まえた構成図を下記に示します。

まず、WAFで予め作業者のIPアドレスをIPセットとして保持しておき、それ以外のリクエストを全てブロックするようにします。そのようにした場合、ブロックされたレスポンスのステータスコードは403（Forbidden）となるため、 CloudFrontのカスタムエラーレスポンスで503（Service Unavailable）に変換し、コンテンツとしてS3バケットに予め配置してあるメンテナンスページを返すようにします。

Terraformで当該部分を以下のように記述しました。 ※ コードはあくまでもイメージです。下記のようにWAFのルールとCloudFrontのカスタムエラーページを動的に切り替えられるようにしておき、 is_maintenance_mode という変数一つだけで制御するようにしました。

resource "aws_cloudfront_distribution" "hoge" {
   （中略）
  dynamic "rule" {
    for_each = (var.is_maintenance_mode == true) ? [1] : []

    content {
      name     = "maintenance-rule"
      priority = 1000

      action {
        block {}
      }

      statement {
        not_statement {
          statement {
            ip_set_reference_statement {
              arn = aws_wafv2_ip_set.maintainers.arn
            }
          }
        }
      }
      visibility_config {
        cloudwatch_metrics_enabled = true
        metric_name                = "metic_name"
        sampled_requests_enabled   = true
      }
    }
  }
}

resource "aws_wafv2_web_acl" "hoge" {
  ...（中略）
  dynamic "custom_error_response" {
    for_each = (var.is_maintenance_mode == true) ? [1] : []
    content {
      error_code         = 403
      response_code      = 503
      response_page_path = var.maintenance_page_path
    }
  }
}

エンドポイントに対してCNAMEを設定する

先述したとおりアップグレード方式はインプレースアップグレードであるため、うまく成功した場合はエンドポイントは変わりません。しかし、アップグレード後に致命的な不具合が発生し、切り戻す場合はスナップショットから新たなAuroraクラスターを作成することとなります。切り戻しを迅速にするためにDBに接続している全てのアプリケーションの接続設定を見直し、CNAMEで記述するように変更しました。

動作確認テスト

事前に開発環境にAurora MySQL v2のクラスターを作成し、ステージング環境の参照先をそちらに向けて動作確認テストを実施しました。そこで遭遇したエラーや不具合、ハマりどころについて紹介します。

サブクエリ最適化の挙動が変わり、フルスキャンが発生してしまう

The optimizer uses semijoin and materialization strategies to optimize subquery execution.

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

MySQL 5.6.5 からの修正でINサブクエリの最適化時の戦略としてSEMIJOINとマテリアライゼーション（実体化）が同時に効くようになっていました。処理としてはサブクエリを先に実行し、その結果をテンポラリテーブルに格納しようとするのですが、そのときにフルスキャンが発生するようになっていました。

実際に詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイドでも以下のように述べられており、できれば避けたい実行計画であることがわかりました。

とはいえ、テンポラリテーブルの構築が伴うマテリアライゼーションはそれなりにコストが高くつくので、できれば避けたい実行計画ではある。極力実体化を伴わないSEMIJOINが採用されるよう、テーブルやインデックスの設計、あるいはクエリの書き方を工夫しよう。

対応方法の一つとしてMySQL 5.7.7 以降を使っている場合はオプティマイザヒントが挙げられます。今回は/*+ NO_SEMIJOIN(MATERIALIZATION) */ とオプティマイザヒントを記述し、実行計画がアップグレード後に変わらないようにしました。 Ruby on Rails 6 以降を使っている方は optimizer_hints を使うとメソッドチェーンの中で綺麗に書くことができます。

It is now possible to provide hints to the optimizer within individual SQL statements, which enables finer control over statement execution plans than can be achieved using the optimizer_switch system variable. Optimizer hints are specified as /+ ... / comments following the SELECT, INSERT, REPLACE, UPDATE, or DELETE keyword of statements or query blocks.

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html#mysqld-5-7-7-optimizer

Topic.optimizer_hints("MAX_EXECUTION_TIME(50000)", "NO_INDEX_MERGE(topics)")
# SELECT /*+ MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(topics) */ `topics`.* FROM `topics`

↑ APIドキュメントの例

https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-optimizer_hints

ORDER BYとDISTINCTを組み合わせて使っているクエリでエラーになってしまう

Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled.

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.5 (2014-09-25, Milestone 15)

MySQL 5.7.5 からの修正で ONLY_FULL_GROUP_BY を有効にしているとき、 ORDER BYで指定しているカラムはDISTINCTのカラムリストに含めなければクエリがエラーになってしまいます。

READYFORでは実行者用のページからソートを含む複雑なクエリが多く発行される傾向にあり、該当するエラーが数件ほど発生していました。

アップグレード時にカスタムパラメーターグループを適用する場合はインスタンスを再起動する必要がある

Aurora MySQL DB クラスターのメジャーバージョンのアップグレード - Amazon Aurora

アップグレードプロセス中にカスタムパラメータグループを指定した場合は、アップグレード終了後にクラスターを手動で再起動する必要があります。再起動すると、クラスターがカスタムパラメータ設定の使用をスタートできます。

Aurora MySQLを利用する方の多くはカスタムパラメーターグループを適用すると思いますが、インプレースアップグレード時にパラメーターを選択するだけでは適用されません。アップグレード直後のインスタンスのパラメーターグループの状態を確認してみると、「同期中」ではなく、「再起動を保留中」と表示されます。