Amazon RDS for MySQL で実行が遅いクエリをトラブルシューティングし、クエリのパフォーマンスを向上させる方法を教えてください。

解決策

実行速度が遅いクエリをトラブルシューティングしてクエリのパフォーマンスを向上させるには、次の手順を実行します。

リソースの使用状況を確認する

リソースの使用状況を監視し、クエリのパフォーマンスが低下するタイミングを特定するには、次の操作を行います。

• 指定した期間にわたってリソースを監視するには、Amazon CloudWatch メトリクスを使用します。
• データベースのパフォーマンスを監視するには、Amazon RDS コンソールを使用してパフォーマンスメトリクスを確認します。
• データベースのパフォーマンスに影響を与える可能性のあるアクティブなプロセスやスケジュールされたプロセスを特定するには、インスタンスのステータスを確認します。データベースのパフォーマンスが低下したときに発生した Amazon RDS イベントを確認します。

リソース消費の原因となるワークロードを確認する

リソース消費の原因となっているワークロードを確認するには、Amazon RDS で Performance Insights を使用します。現在のワークロードが vCPU の制限を超えると、サーバーが過負荷状態になります。サーバーが過負荷になっている場合は、ワークロードの原因となっているクエリを確認し、クエリを最適化します。次に、インスタンスクラスを変更します。

待機イベントを最も多く消費しているリソースを調査するには、DB 負荷を待機イベントの数で切り分けます。負荷チャートでの色の帯が太いものは、ワークロードに最も影響している待機タイプを示します。詳細については、「Amazon RDS で Performance Insights を使用して DB 負荷を監視する」を参照してください。

低速クエリを特定するには、パラメータグループで slow_query_log を有効にします。インスタンスのワークロードが増加したかどうかを確認するには、CloudWatch メトリクスを使用して次のことを確認します。

• データベース接続数: DB インスタンスに接続されているクライアントセッションの数。
• ネットワーク受信スループット (MB/秒): DB インスタンスに出入りするネットワークトラフィックのレート。
• 書き込みと読み取りのスループット: 1 秒あたりのディスクからの読み取りまたはディスクへの書き込みの平均メガバイト数。
• 書き込みと読み取りの遅延: 読み取りまたは書き込み操作の平均時間 (ミリ秒単位)。レイテンシーメトリクスをデータベース接続の増加またはスループットのメトリクスと相関させると、ワークロードが低速クエリの原因であることが分かる場合があります。詳細については、「ストレージフルと表示される RDS for MySQL または MariaDB インスタンスのトラブルシューティング方法を教えてください」を参照してください。
• IOPS (読み取りと書き込み): 1 秒あたりのディスク読み取りまたは書き込み操作の平均回数。
• 空きストレージ容量 (MB): DB インスタンスが現在使用していないディスク容量。
• バーストバランス (%) - 汎用 SSD (gp2) のバーストバケット I/O クレジットのうち、使用可能な割合

ワークロード内のオペレーティングシステム (OS) のリストおよびシステムメトリクスを確認するには、拡張モニタリングを使用します。デフォルトでは、拡張モニタリングの間隔は60秒です。より詳細なデータポイントについては、間隔を 1 ～ 5 秒に設定することをおすすめします。

クエリにインデックスがあるかどうかを確認し、クエリがテーブルの完全スキャンを行うかどうかを確認します。

クエリにインデックスがある場合や、テーブル全体のスキャンを行う場合は、クエリの実行速度が遅くなります。

クエリでインデックスを使用しているかどうかを確認するには、EXPLAIN クエリを実行します。詳細については、MySQL のウェブサイトで「EXPLAIN ステートメント」を確認してください。EXPLAIN の出力で、テーブル名、使用しているキー、クエリでスキャンされた行数を確認します。出力に使用中のキーが表示されない場合は、WHERE 句で使用している列にインデックスを作成します。テーブルにインデックス付けが必要な場合は、テーブルの統計情報が最新であることを確認します。詳細については、MySQL のウェブサイトで「INFORMATION_SCHEMA STATISTICS テーブル」を参照してください。統計が最新になると、クエリオプティマイザーは正しいカーディナリティを持つ最も選択的なインデックスを使用します。その結果、クエリのパフォーマンスが向上します。

履歴リストの長さを確認する

InnoDB は、多版型同時実行制御 (MVCC) を使用します。MVCCは、読み取りの一貫性を保つために同じレコードの複数のコピーを保持します。履歴リストの長さは、履歴リスト内の変更を含む、取り消しログの総数です。長時間実行中のデータを書き込んだり読み込んだりするトランザクションがある場合、そのトランザクションが完了するかロールバックするまで、履歴リストの長さは増加します。実行時間の長いトランザクションが使用しているテーブルが他のトランザクションによって変更される期間にも、履歴リストの長さは増加します。

ワークロードで複数のオープントランザクションや長時間実行するトランザクションが必要な場合は、データベースの履歴リストが長くなることが予想されます。履歴リストの長さのサイズを監視しないと、パフォーマンスが時間の経過に伴い低下する可能性があります。履歴リストの長さが増加すると、リソースの消費量が多くなったり、SELECT ステートメントのパフォーマンスが遅くなったり、パフォーマンスの一貫性が低下したり、ストレージ消費が増加したりする場合もあります。

注: 履歴リストの長さが急増する原因は、実行時間の長いトランザクション以外にもあります。パージスレッドが DB の変更に追いつけない場合、履歴リストは長くなります。極端な場合、データベースが停止する可能性もあります。

履歴リストの長さを確認するには、次のコマンドを実行します。

SHOW ENGINE INNODB STATUS;

出力例

------------ TRANSACTIONS ------------
Trx id counter 26368570695
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Amazon RDS for MySQL インスタンスで Performance Insights が有効になっている場合は、次の手順を実行し、RollbackSegmentHistoryListLength を確認します。

1. ライター側の Performance Insight に移動します。
2. [メトリクスの管理] を選択し、[データベースメトリクス] を選択します。
3. trx_rseg_history_len メトリクスを選択した後、[グラフを更新] を選択します。

履歴リストの長さが増加する原因となる問題を解決するには、次の手順を実行します。

• DML の書き込みによって履歴リストの長さが増加している場合は、書き込みステートメントをキャンセルまたは停止します。これにより、中断されたトランザクションのロールバックが発生し、更新のロールバックには大幅な時間がかかります。
• READ クエリが原因で履歴リストの長さが増加する場合は、mysql.rds_kill_query を使用してそのクエリを停止します。
• クエリの実行時間が長い場合は、データベース管理者に協力を依頼し、そのクエリを停止できるかどうかを確認してください。

注: データベース上では、オープントランザクションや実行時間が長いトランザクションを避け、データを少量ずつコミットすることをおすすめします。

クエリのパフォーマンスを向上させる

クエリのパフォーマンスを向上させるには、次のベストプラクティスを実施してください。

• 大幅に時間がかかっている状態を特定するには、時間のかかるクエリをプロファイリングします。詳細については、MySQL のウェブサイトで「SHOW PROFILE ステートメント」を参照してください。

• SHOW FULL PROCESSLIST コマンドを拡張モニタリングと同時に実行します。併用することで、データベースサーバーで現在実行中の操作のリストを確認できます。

• SHOW ENGINE INNODB STATUS コマンドを実行し、トランザクション処理、待機イベント、デッドロックに関する情報を取得します。

• ブロッキングクエリを特定し、解決します。詳細については、「他にアクティブなセッションがないにもかかわらず、Amazon RDS for MySQL DB インスタンスへのクエリがブロックされた理由を知りたいです」を参照してください。

• MySQL ログを CloudWatch Logs に発行します。ログは、割り当てられたストレージ容量のしきい値である 2% を維持するために 1 時間ごとにローテーションされます。ログが 2 週間を超えて経過したか、合計サイズが 2% のしきい値を超えた場合は、削除されます。

• リソースの使用状況を監視し、しきい値を超えたときにアラートを受信するには、CloudWatch アラームを設定します。

• クエリ操作計画を策定し、クエリで適切なインデックスを使用しているかどうかを確認します。EXPLAIN を使用してクエリを最適化し、MySQL がクエリを実行する方法の詳細を確認します。詳細については、MySQL のウェブサイトで「EXPLAIN を使用してクエリを最適化する」を参照してください。

• ANALYZE TABLE を使用してクエリ統計を最新の状態に保ちます。詳細については、MySQL のウェブサイトで 「ANALYZE TABLE ステートメント」を参照してください。

• MySQL 8.0 では、EXPLAIN ANALYZE を使用します、。EXPLAIN ANALYZE ステートメントは、MySQL がクエリ上で時間を割り当てる場所と、その時間が割り当てられた理由を示します。クエリが完了すると、EXPLAIN ANALYZE は計画とその測定値を出力します。詳細については、MySQL のウェブサイトで「EXPLAIN ANALYZE を使用して情報を取得する」を参照してください。

• MySQL 8.0 では、ロック待機は data_lock_waits テーブルのパフォーマンススキーマに一覧表示されます。詳細については、MySQL のウェブサイトで「InnoDB トランザクションとロック情報を使用する」を参照してください。

  例

  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;