Aurora MySQL データベースのクエリパフォーマンスのトラブルシューティング - Amazon Aurora
クエリにかかった時間の確認クエリオプティマイザ設定の確認

Aurora MySQL データベースのクエリパフォーマンスのトラブルシューティング

MySQL は、クエリプランの評価方法に影響するシステム変数、切り替え可能な最適化、オプティマイザとインデックスのヒント、オプティマイザのコストモデルを通じてクエリオプティマイザの制御を提供します。これらのデータポイントは、さまざまな MySQL 環境を比較するときだけでなく、以前のクエリ実行プランを現在の実行プランと比較したり、任意の時点の MySQL クエリの全体的な実行状況を把握したりするのに役立ちます。

クエリのパフォーマンスは、実行プラン、テーブルスキーマとサイズ、統計、リソース、インデックス、パラメータ設定など、さまざまな要因に左右されます。クエリの調整には、ボトルネックの特定と実行パスの最適化が必要です。

  • クエリの実行プランを見つけ、クエリが適切なインデックスを使用しているかどうかを確認します。EXPLAIN を使用し、各プランの詳細を確認することで、クエリを最適化できます。

  • Aurora MySQL バージョン 3 (MySQL 8.0 コミュニティエディションと互換性あり) は EXPLAIN ANALYZE ステートメントを使用します。EXPLAIN ANALYZE ステートメントは、MySQL がクエリのどこで時間を費やしているのか、またその理由を示すプロファイリングツールです。EXPLAIN ANALYZE を使用すると、Aurora MySQL はクエリを計画、準備、実行しつつ、行をカウントし、実行プランのさまざまなポイントで費やされた時間を測定します。クエリが完了すると、EXPLAIN ANALYZE は、クエリ結果の代わりにプランとその測定値を印刷します。

  • ANALYZE ステートメントを使用してスキーマの統計情報を最新の状態に維持してください。クエリオプティマイザは、統計が古いと不適切な実行プランを選択することがあります。これにより、テーブルとインデックスの両方のカーディナリティの推定が不正確になり、クエリのパフォーマンスが低下する可能性があります。innodb_table_stats テーブルの last_update 列には、スキーマ統計が最後に更新された時刻が表示されます。これは「古くなっている」ことを示す良い指標です。

  • データの分布の歪みなど、テーブルのカーディナリティに考慮されていないその他の問題が発生することもあります。詳細については、MySQL ドキュメントの「InnoDB テーブルの ANALYZE TABLE の複雑度の推定」と「MySQL のヒストグラム統計」を参照してください。

クエリにかかった時間の確認

クエリにかかった時間を決定する方法は次のとおりです。

プロファイリング

デフォルトでは、プロファイリングは無効です。プロファイリングを有効にし、スロークエリを実行してプロファイルを確認します。

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. 最も多くの時間が費やされているステージを特定します。MySQL ドキュメントの「一般的なスレッド状態」によると、SELECT ステートメントの行の読み取りと処理は、多くの場合、特定のクエリの存続期間中で最も長い実行状態です。EXPLAIN ステートメントを使用すると、MySQL がこのクエリを実行する方法を理解できます。

  2. スロークエリログを確認して rows_examined と rows_sent を評価し、各環境でワークロードが類似していることを確認します。詳細については、「Aurora MySQL データベースのログ記録」を参照してください。

  3. 特定されたクエリの一部であるテーブルに対して、以下のコマンドを実行します。

    SHOW TABLE STATUS\G;

  4. 各環境でクエリを実行する前と後に、次の出力をキャプチャします。

    SHOW GLOBAL STATUS;

  5. 各環境で以下のコマンドを実行して、このサンプルクエリのパフォーマンスに影響する他のクエリ/セッションがないかどうかを確認します。

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    サーバー上のリソースがビジー状態になると、クエリを含むサーバー上の他のすべての操作に影響することがあります。また、クエリの実行時に定期的に情報をキャプチャしたり、有用な間隔で情報をキャプチャする cron ジョブを設定することもできます。

パフォーマンススキーマ

パフォーマンススキーマは、パフォーマンスへの影響を最小限に抑えながら、サーバーのランタイムパフォーマンスに関する有用な情報を提供します。これは DB インスタンスに関するスキーマ情報を提供する information_schema とは異なります。詳細については、「Aurora MySQL における Performance Insights のPerformance Schema の概要」を参照してください。

クエリオプティマイザトレース

特定のクエリプランが実行対象として選択された理由を理解するために、MySQL クエリオプティマイザにアクセスするように optimizer_trace をセットアップできます。

オプティマイザトレースを実行すると、オプティマイザが使用できるすべてのパスとその選択に関する詳細な情報が表示されます。

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

クエリオプティマイザ設定の確認

Aurora MySQL バージョン 3 (MySQL 8.0 コミュニティエディションと互換性あり) には、Aurora MySQL バージョン 2 (MySQL 5.7 コミュニティエディションと互換性あり) と比較して、オプティマイザ関連の多くの変更があります。optimizer_switch にカスタム値がある場合は、デフォルトの違いを確認して、ワークロードに最適な optimizer_switch 値を設定することをお勧めします。また、Aurora MySQL バージョン 3 で使用できるオプションをテストして、クエリがどのように実行されるかを調べることをお勧めします。

注記

Aurora MySQL バージョン 3 では、innodb_stats_persistent_sample_pages パラメータのコミュニティデフォルト値である 20 を使用しています。

optimizer_switch 値を表示するには、次のコマンドを使用できます。

SELECT @@optimizer_switch\G;

次の表は、Aurora MySQL バージョン 2 および 3 のデフォルトの optimizer_switch 値を示しています。

設定 Aurora MySQL バージョン 2 Aurora MySQL バージョン 3
batched_key_access オフ オフ
block_nested_loop オン オン
condition_fanout_filter オン オン
derived_condition_pushdown – オン
derived_merge オン オン
duplicateweedout オン オン
engine_condition_pushdown オン オン
firstmatch オン オン
hash_join オフ オン
hash_join_cost_based オン –
hypergraph_optimizer – オフ
index_condition_pushdown オン オン
index_merge オン オン
index_merge_intersection オン オン
index_merge_sort_union オン オン
index_merge_union オン オン
loosescan オン オン
materialization オン オン
mrr オン オン
mrr_cost_based オン オン
prefer_ordering_index オン オン
semijoin オン オン
skip_scan – オン
subquery_materialization_cost_based オン オン
subquery_to_derived – オフ
use_index_extensions オン オン
use_invisible_indexes – オフ

詳細については、MySQL ドキュメントの「切り替え可能な最適化 (MySQL 5.7)」と「切り替え可能な最適化 (MySQL 8.0)」を参照してください。