Aurora MySQL 2へのバージョンアップにおけるパフォーマンス劣化

初めに

Aurora MySQL ver1から Aurora MySQL ver2(MySQL 5.7系ベース)へバージョンアップしましたが、一部クエリのパフォーマンス劣化が発生しました。こちらについてお話していきます。

前提

マニュアルやリリースノートなどは見ておきましょう。

• MySQL 5.7 Reference Manual :: 1.3 What Is New in MySQL 5.7
• Database engine updates for Amazon Aurora MySQL version 2 - Amazon Aurora

起こった事象

Range Optimizerのバグ

初めは「range_optimizer_max_mem_size」の設定値かな?と思い設定変更しても改善しませんでした。調べた結果、かなり複雑な条件をWHERE句で指定した場合(IN句で大量にID指定するなど)、問題のあるTree構造を構築してしまい、大幅な遅延が発生するようです。元々問題があったクエリだったため、IN句ではなくJOIN、サブクエリなどを使用してパフォーマンス改善を図りました。

MySQL 5.7系はこれ以外にも、オプティマイザ関連のバグがいくつか報告されているようですね...

今回関係なかったがパフォーマンス劣化に関連する設定

MySQL 5.7系でのパフォーマンス劣化の話は、結構あるようです。今回は関係ありませんでしたが、バージョンアップ時に影響がありそうなパラメータをここに記載します。

range_optimizer_max_mem_size

MySQL 5.7から追加されたパラメータ。IN句で大量にIDを指定しているクエリの場合、このオプションで指定されたメモリサイズを超える場合があります。その際、rangeによるテーブルアクセスではなく、フルスキャンが発生することがあり、これによりパフォーマンスが劣化します。

# 発生するエラー
Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

# メモリサイズの制限を外す設定
SET GLOBAL range_optimizer_max_mem_size = 0; 

• MySQL 5.7 Reference Manual :: 8.2.1.2 Range Optimization

optimizer_switch

MySQL 5.7に伴い、optimizer_switchのオプションが追加されました。特に
condition_fanout_filter と derived_merge は既存のパフォーマンスに影響を与える可能性があります。

SET optimizer_switch='condition_fanout_filter=off';
SET optimizer_switch='derived_merge=off';

特に derived_merge は5.7以前に発行できていたクエリが、発行できなくなるパターンもあるので注意が必要です。

However in MySQL 5.7 before MySQL 5.7.11, and for statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that previously was materialized can result in an ER_UPDATE_TABLE_USED error

• MySQL 5.7 Reference Manual :: 1.3 What Is New in MySQL 5.7

5.7ではなく5.6ですが、semijoin あたりの挙動でもパフォーマンス劣化が起こるようです。

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

というよりMySQLのオプティマイザ系の設定は目を通しておいたほうが良いですね。

スロークエリの解析

サイボウズさんの記事が非常に参考になりました。

• Cybozu Inside Out | サイボウズエンジニアのブログ

基礎知識はこちら。

• MySQL 5.7入門(チューニング基礎編)

サイボウスさんの記事をもとに、クエリのプロファイルを...と思いましたが、設定変えるのはちょっと...という方はこちら。

# プロファイルの設定 セッション内のみ
SET SESSION profiling = 1

# 過去に実行されたクエリのプロファイル一覧
SHOW PROFILES;

# 指定したプロファイルの詳細を確認
SHOW PROFILE ALL FOR QUERY N;

ただ、非推奨のようなので、できればperformance_schema=on をはじめとした新しいプロファイルの取得で実施したほうが良いです。

• 再現性のあるスロークエリーには「SHOW PROFILE」を試してみよう | Think IT

私がプロファイルを確認した際はstatisticsの実行時間が増大していたので、こちらのフォーラムでのチェック事項を確認したりしてました。

• erformance - statistics State in MySQL Processlist - Database Administrators Stack Exchange

まとめ

「バージョンアップすれば、パフォーマンス上がるでしょ」という甘い考えは持たないほうがいいです。オプティマイザの挙動変更などによって、ものによっては大幅に劣化するパターンがあります。しっかり公式ドキュメントを読んだうえで、パフォーマンス関連の資料を基にしっかりテストすることが大事です。