Amazon Aurora MySQL DB クラスターで SELECT クエリの実行が遅いのはなぜですか?

最終更新日: 2020 年 12 月 10 日 

Amazon Aurora for MySQL DB クラスターがあり、SELECT クエリを使用してデータベースからデータを選択したいと思います。DB クラスターで SELECT クエリを実行すると、クエリの実行が遅くなります。遅い SELECT クエリの原因を特定して解決するにはどうすればよいですか?

簡単な説明

Amazon Aurora for MySQL DB クラスターで SELECT クエリの実行速度が遅くなる理由は複数あります。

  • Amazon Relational Database Service (Amazon RDS) システムリソースの使用率が過大です。これは、CPU の使用率が高い、メモリ不足、またはワークロードが DB インスタンスタイプによって処理できる処理能力を超えていることが原因で発生する可能性があります。
  • データベースがロックされており、結果として生じる待機イベントによって SELECT クエリのパフォーマンスが低下しています。
  • SELECT クエリが大きなテーブルに対して全テーブルスキャンを実行しているか、クエリに必要なインデックスがありません。
  • トランザクションの実行時間が長いため、InnoDB 履歴リストの長さ (HLL) が大幅に増加しました。

解決方法

メトリクスを使用して Amazon RDS システムリソースを監視する

Amazon Aurora クラスターで CPU 使用率と空きメモリを常に監視する必要があります。CPU のスパイクがときどき発生するのは正常ですが、長期間にわたって一貫して CPU が高いと、SELECT クエリの実行速度が遅くなる可能性があります。CPU の使用方法と場所を特定するには、次のツールを使用します。

  1. Amazon CloudWatch メトリクスは、CPU 使用率を監視する最も簡単な方法です。Amazon Aurora で利用可能なメトリクスの詳細については、「Amazon Aurora DB クラスターメトリクスのモニタリング 」を参照してください。
  2. 監視が強化されたことで、OSレベルのメトリクスをより低い粒度で詳細に見ることができます。これは、プロセスが CPU をどのように使用しているかの内訳を細かく示します。
  3. Performance Insights、DB の負荷を正確に判断します。DB インスタンスの Performance Insights を有効にし、負荷が最大 vCPU を超えているかどうかを確認します。また、待機時間によってロードベアリングクエリと SQL を監視し、待機時間を最も長くしているユーザーを特定することもできます。

SELECT クエリは、ディスクのシークが原因で実行速度が遅くなることがあります。ディスク I/O を最小化するために、データベースエンジンは、ディスクから読み取ったブロックをキャッシュしようとします。つまり、次回データベースが同じデータブロックを必要とする場合、ディスクに行くことなく、メモリから取得できます。

ディスクまたはメモリから特定のクエリを実行しているかどうかをチェックするには、以下のメトリクスを使用します。

  • VolumeReadsIOPS: このメトリクスは、請求ボリュームレベル [ディスク] の読み取り操作の数で、できる限り低くする必要があります。

  • BufferCacheHitRatio: このメトリクスは、バッファキャッシュが処理するリクエストの割合 (%) です。可能な限り高い値にする必要があります。BufferCacheHitRatio が低下し、SELECT 文が遅い場合は、基になるボリュームからクエリを処理しています。

低速な SELECT 文を識別するためのもう 1 つの重要なリソースは、スロークエリログです。DB クラスターに対してスロークエリログを有効にして、このクエリを記録し、後でアクションを実行します。MySQL 5.6 互換バージョンを使用している場合は、MySQL パフォーマンススキーマを使用してクエリのパフォーマンスを継続的に監視します。

デッドロックと待機イベントの特定

Amazon RDS はデータベース内のデータをロックするため、一度に 1 つのユーザーセッションのみが行を書き込みまたは更新できます。この行を必要とするその他のトランザクションは、保留のままになります。共有ロックでは、読み取りトランザクションがデータを読み取っている間、書き込み/更新トランザクションは保留のままになります。クエリが別のクエリによってロックされている行へアクセスしようと待機している場合、デッドロックが発生する可能性があります。

データベースのデッドロックを識別するには、パラメータグループで innodb_print_all_deadlocks パラメータを有効にします。

次に、最新のデッドロックセクションで次のコマンドを実行して、デッドロックを特定します。

SHOW ENGINE INNODB STATUS\G;

注: このコマンドは Aurora リーダーでは実行できません。デフォルトでは、MySQL innodb_read_only パラメータが ON に設定されているためです。Aurora MySQL がクラスターのタイプに基づいて DB インスタンスの読み取り専用状態と読み取り/書き込み状態を管理しているため、このパラメータは DB インスタンスでは変更できません。

INFORMATION_SCHEMA INNODB_TRX テーブルをクエリすることもできます。これにより、INNODB で実行中のすべてのトランザクションについて、待機時間を含む、正確な詳細情報が得られます。

クエリがインデックスを使用しているかどうかを確認する

クエリにインデックスがない場合、または全テーブルスキャンを実行する場合、一般的に処理速度は遅くなります。インデックスは、SELECT クエリを高速化するのに役立ちます。

クエリがインデックスを使用しているかどうかを確認するには、EXPLAIN クエリを使用します。これは、低速なクエリのトラブルシューティングに役立つツールです。EXPLAIN 出力で、テーブル名、使用されているキー、およびクエリ中にスキャンされた行数をチェックします。出力に使用中のキーが表示されない場合は、WHERE 句で使用されている列にインデックスを作成します。

テーブルに必要なインデックスが作成されている場合は、 テーブルの統計情報が最新であるかどうかを確認します。統計情報が正確であることが確認できたら、それはクエリオプティマイザは、正しいカーディナリティを持つ最も選択的なインデックスを使用していることを意味します。これにより、クエリのパフォーマンスが向上します。

履歴リストの長さ (HLL) をチェックする

InnoDB では、マルチバージョン同時実行制御 (MVCC) と呼ばれる概念を用いています。MVCC は、読み取りの一貫性を維持するために、同じレコードの複数のコピーを保持します。つまり、トランザクションをコミットすると、InnoDB は古いコピーをパージします。しかし、トランザクションが長期間コミットされていない場合、元に戻すセグメントが増大するため、履歴リストの長さ (HLL) が増加します。InnoDB 履歴リストの長さは、フラッシュされていない変更の数を表します。

ワークロードで長時間実行されるトランザクションやオープントランザクションが多い場合は、データベース上で高い HLL が発生することが予想されます。

注: HLL の急増の原因は、長時間実行されるトランザクションだけではありません。パージスレッドが DB の変更に追いつかない場合でも、HLL は高いままになることがあります。

HLL のサイズを監視しないと、時間の経過に伴うパフォーマンスの低下、リソース消費量の増加、低速で一貫性のない SELECT 文のパフォーマンス、およびストレージの増加につながります。極端な場合、これはデータベースの停止につながる可能性もあります。

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

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

Aurora MySQL では、共有ストレージのボリュームの性質上、履歴リストの長さはクラスターレベルであり、個々のインスタンスレベルではありません。ライターに接続し、次のクエリを実行します。

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

このクエリは、リーダーノードとライターノード間のレプリカラグを理解するのに役立ちます。また、ストレージから読み取るのに DB インスタンスが使用する最も古い LSN と、DB インスタンスの最も古い読み取りビュー TRX ID についても詳しく知ることができます。この情報を利用して、リーダーの 1 つが古い読み取りビューを保持しているかどうかを確認します (ライターのエンジン InnoDB の状態と比較して)。

注: Aurora MySQL 1.19 および 2.06 から、CloudWatch で RollbackSegmentHistoryListLength メトリクスを使用して HLL を監視できます。または、古いバージョンでは、trx_rseg_history_len を使用して、次のコマンドを使って HLL をチェックします。

select NAME AS RollbackSegmentHistoryListLength, 
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

HLL の増大に関する問題を解決するには、次の方法をご使用ください。

  • DML (書き込み) によって HLL が増大する場合:この文のキャンセルまたは終了には、中断されたトランザクションのロールバックが含まれます。この時点までに行われたすべての更新がロールバックされるため、これにはかなりの時間がかかります。

  • 読み取りが HLL の増大を引き起こす場合: mysql.rds_kill_query を使用してクエリを終了します。

  • クエリの実行時間に応じて、DBA と連携して、ストアドプロシージャを使用してクエリを終了できるかどうかを確認します。

増大を回避する最善の方法は、上記の方法を使用して HLL を監視し、データベース上で長時間実行されるトランザクションやオープントランザクションを回避することです。さらに、データを小さなバッチでコミットすることがベストプラクティスです。

重要: DB クラスターまたはインスタンスを再起動しないでください。バッファプール内のデータにアクセスできる場合、HLL のパージはより効率的です。データベースを再起動すると、クリーンアップのためにすべてのデータがディスクから読み込まれます。