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

所要時間2分

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

簡単な説明

Aurora 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 使用率をモニタリングする最も簡単な方法です。Aurora で利用可能なメトリクスの詳細については、「Aurora の CloudWatch メトリクス」を参照してください。

2. モニタリングが強化されたことで、OS レベルのメトリクスをより低い粒度で詳細に見ることができます。詳細な内訳では、プロセスが CPU をどのように使用しているかを確認できます。

3. Performance Insights は、DB の負荷を正確に判断します。DB インスタンスの Performance Insights を有効にし、負荷が最大 vCPU を超えていないかを確認します。また、待機時間によってロードベアリングクエリと SQL をモニタリングし、待機時間を最も長くしているユーザーを特定することもできます。

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

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

VolumeReadsIOPS: このメトリクスは、請求ボリュームレベル [disk] の読み取りオペレーションの数です。この値をできるだけ低くするのがベストプラクティスです。
BufferCacheHitRatio: このメトリクスは、バッファキャッシュが処理するリクエストの割合 (%) です。この値をできるだけ高くするのがベストプラクティスです。BufferCacheHitRatio が低下し、SELECT ステートメントが低速である場合は、基になるボリュームからクエリを処理しています。

低速な SELECT ステートメントを識別するためのもう 1 つの重要なリソースは、スロークエリログです。DB クラスターのためにスロークエリログを有効にして、これらのクエリを記録し、後でアクションを実行します。MySQL 5.6 互換バージョンでは、MySQL Performance Schema を使用して、クエリのパフォーマンスを継続的にモニタリングします。

デッドロックと待機イベントを特定する

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

データベースのデッドロックを識別するには、パラメータグループで innodb_print_all_deadlocks パラメータを有効にします。その後、RDS コンソール/CLI/API から mysql-error.log をモニタリングします。

または、管理者アカウントで MySQL にログインし、このコマンドを実行して、Latest Detected Deadlock セクションのコマンド出力からデッドロックを特定します。

mysql> SHOW ENGINE INNODB STATUS\G;

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

クエリにインデックスがない場合、またはフルテーブルスキャンを実行する場合、クエリの実行速度はさらに低下します。インデックスは、SELECT クエリを高速化するのに役立ちます。

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

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

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

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

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

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

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 についても詳しく知ることができます。この情報を利用して、(ライターのエンジン 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';

Aurora MySQL インスタンスでパフォーマンスインサイトがアクティブ化されている場合は、RollbackSegmentHistoryListLength を確認できます。ライターのパフォーマンスインサイトに移動し、次の操作を行います。

1. [メトリクスの管理] を選択し、[データベースメトリクス] を選択します。

2. trx_rseg_history_len メトリクスを選択し、[グラフの更新] を選択します。

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

DML (書き込み) によって HLL が増大する場合：この文のキャンセルまたは終了には、中断されたトランザクションのロールバックが含まれます。この時点までに行われたすべての更新がロールバックされるため、これにはかなりの時間がかかります。
READ が HLL の増大を引き起こしている場合: mysql.rds_kill_query を使用してクエリを終了します。
クエリの実行時間に応じて、DBA と連携して、ストアドプロシージャを使用してクエリを終了できるかどうかを確認します。

これらの方法を使用して HLL をモニタリングすることによって増大を回避し、データベース上におけるオープントランザクションまたは長時間実行されるトランザクションを回避するのがベストプラクティスです。また、データを小さなバッチでコミットするのもベストプラクティスです。

重要: DB クラスターまたはインスタンスを再起動しないでください。バッファプールのメモリ内のデータにアクセスできる場合は、HLL を消去する方が効率的です。データベースを再起動すると、存続可能なページキャッシュが失われる可能性があります。その場合、HLL を消去するために、クラスターボリュームのデータページを読み取る必要があります。これはメモリで実行するよりも低速であり、追加の I/O 請求コストが発生します。