Amazon RDS for MySQL でクエリの実行が低速なのはなぜですか?

所要時間2分

Amazon Relational Database Service (Amazon RDS) for MySQL で低速実行クエリをトラブルシューティングしようとしています。なぜこのような問題が発生するのですか? また、クエリのパフォーマンスを向上させるにはどうすればよいですか?

簡単な説明

クエリのパフォーマンスを向上させるために、次の要素を検討してください。

リソース使用率 (CPU、メモリ、ストレージなど)
ワークロード分析
クエリのチューニングとモニタリング

解決方法

リソース使用率 (CPU、メモリ、ストレージなど)

データベースパフォーマンスの問題の根本原因を理解するには、インスタンスが使用しているサーバー全体のリソースをすべて確認します。ワークロードをモニタリングし、クエリの実行に長すぎる時間がかかるようになった時点と比較して、クエリのパフォーマンスが通常どおりだった期間を調査できます。

Amazon CloudWatch メトリクスを使用して、パフォーマンスが通常どおりであるとみなされた日数を含む期間、これらのリソースをモニタリングします。Amazon RDS コンソールでパフォーマンスメトリクスを表示して、データベースのパフォーマンスをモニタリングすることもできます。

また、インスタンスのステータスを確認して、データベースのパフォーマンスに影響を及ぼしている可能性のある他のアクティブなプロセスまたはスケジュールされたプロセスを特定することもできます。Amazon RDS コンソールで、データベースのパフォーマンスが悪かったときに発生したイベントを確認します。

ワークロード分析

リソース消費に寄与しているワークロードを分析するには、Performance Insights を使用します。Performance Insights は、リソース消費の増加に寄与しているすべてのクエリと待機のグラフィック分析を提供します。

Performance Insights は、インスタンスのために、vCPU の数を使用する代わりに、ワークロードを主要なメトリクスとして使用します。現在のワークロードが vCPU の制限を超えると、サーバーは過負荷になります。サーバーが過負荷になっている場合は、ワークロードに寄与しているクエリを確認し、クエリを最適化する方法を特定します。その後、インスタンスクラスを変更することを検討します。

Performance Insights のワークロードは、待機イベントに分割することもできます。待機イベントの数で DB 負荷をスライスして、待機を消費している上位のリソースを調査します。負荷グラフの濃い色の帯は、ワークロードに最も寄与している待機タイプを示します。詳細については、Amazon RDS での Performance Insights を使用した DB 負荷のモニタリングを参照してください。

また、低速クエリログ (カスタムパラメータグループで有効) を使用して、実行速度が遅いクエリを特定することもできます。

その後、Amazon CloudWatch メトリクスを使用して、インスタンスで実行された作業量が増加したかどうかを確認できます。例:

データベース接続: DB インスタンスに接続されているクライアントセッションの数。
ネットワーク受信スループット (MB/秒): DB インスタンスとの間で送受信されるネットワークトラフィックのレート。
書き込みおよび読み取りスループット: ディスクからの読み取りまたはディスクへの書き込みの 1 秒あたりの平均メガバイト数。
書き込みおよび読み取りレイテンシー: 読み取りまたは書き込みオペレーションの平均時間 (ミリ秒)。
IOPS (読み取りおよび書き込み): ディスクの読み取りまたは書き込みオペレーションの 1 秒あたりの平均回数。
空きストレージ領域 (MB): DB インスタンスによって現在使用されていないディスク領域の量。

レイテンシーメトリクスは、読み取りまたは書き込みディスク I/O オペレーションの完了までに要した時間を示します。レイテンシーメトリクスと増加したデータベース接続またはスループットメトリクスの相関関係は、ワークロードが低速クエリ実行の理由であることを示唆している可能性があります。使用率の要因の特定に関する詳細については、MySQL を実行している Amazon RDS DB インスタンスでストレージを使用しているものを表示するにはどうすればよいですか? を参照してください。

また、拡張モニタリングを使用して、ワークロードに関係するオペレーティングシステムのリストと基盤となるシステムメトリクスを取得することもできます。デフォルトでは、拡張モニタリングのモニタリング間隔は 60 秒です。データポイントをより詳細にするため、これを 1～5 秒間隔に設定するのがベストプラクティスです。

クエリ最適化

実行時間の長いクエリが低速クエリログまたは Performance Insights から特定されたら、クエリのパフォーマンスを向上させる方法を検討します。クエリを調整するには、次の方法を検討します。

最も時間が費やされている状態を見つけるには、低速なクエリをプロファイルします。詳細については、MySQL ウェブサイトの SHOW PROFILE statement を参照してください。
拡張モニタリングとともに SHOW FULL PROCESSLIST コマンドを実行します。一緒に使用すると、データベースサーバーで現在実行されているオペレーションのリストを確認できます。
トランザクションの処理、待機、およびデッドロックに関する情報を取得するには、SHOW ENGINE INNODB STATUS コマンドを実行します。
ブロックしているクエリを見つけて、ブロックを解決します。詳細については、他にアクティブなセッションがないときに、Amazon RDS for MySQL DB インスタンスに対するクエリがブロックされたのはなぜですか? を参照してください。
MySQL ログを Amazon CloudWatch に発行します。割り当てられたストレージ領域のしきい値の 2% を維持するために、ログは 1 時間ごとにローテーションされます。その後、2 週間を超える期間が経過している場合、または合計サイズが 2% のしきい値を超える場合、それらのログは消去されます。
Amazon CloudWatch アラームを設定して、リソースの使用状況をモニタリングし、しきい値を超えるたびにアラートを受け取ることができるようにします。
クエリの実行プランを見つけ、クエリが適切なインデックスを使用しているかどうかを確認します。EXPLAIN プランを使用してクエリを最適化し、MySQL がクエリを実行する方法の詳細を確認できます。
ANALYZE テーブルステートメントを使用して、クエリの統計情報を常に最新の状態に保ちます。クエリオプティマイザーは、統計が古くなっているために不適切な実行プランを選択することがあります。これにより、テーブルとインデックスの両方のカーディナリティの推定が不正確になるため、クエリのパフォーマンスが低下する可能性があります。
MySQL 8.0 は EXPLAIN ANALYZE ステートメントを使用するようになりました。EXPLAIN ANALYZE ステートメントは、MySQL がクエリに時間を費やしている領域とその理由を示すクエリのプロファイリングツールです。MySQL は、EXPLAIN ANALYZE を使用して、クエリを計画、インストルメント化、および実行しながら、行をカウントし、実行計画のさまざまなポイントで費やされた時間を測定します。クエリが完了すると、EXPLAIN ANALYZE はクエリ結果の代わりにプランとその測定値を出力します。
MySQL バージョン 8 では、ロック待機が data_lock_waits テーブルの Performance Schema にリストされていることに注意してください。例:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

詳細については、MySQL ウェブサイトの Using InnoDB transaction and locking information を参照してください。