Aurora MySQL 互換 DB クラスターで SELECT ステートメントの実行速度が遅い問題をトラブルシューティングする方法を教えてください。

所要時間2分

Amazon Aurora MySQL 互換エディション DB クラスターで、SELECT ステートメントの実行速度が遅い問題をトラブルシューティングしたいと考えています。

簡単な説明

SELECT ステートメントの実行速度は、以下の理由により Aurora MySQL 互換 DB クラスターで遅くなる可能性があります。

データベースシステムリソースを使い過ぎている。
データベースがロックされている。
SELECT ステートメントが大きなテーブルのテーブル全体をスキャンしている。または、クエリに必要なインデックスがない。
トランザクションの実行が長時間であるため、InnoDB 履歴リストの長さ (HLL) が長くなっている。

解決策

注: AWS コマンドラインインターフェイス (AWS CLI) コマンドの実行中にエラーが発生した場合は、「AWS CLI のエラーのトラブルシューティング」を参照してください。また、AWS CLI の最新バージョンを使用していることを確認してください。

CloudWatch Database Insights を使用して、SELECT ステートメントの実行が遅い理由を確認する

Performance Insights を有効にし、Database Insights を使用して、データベース負荷が高いクエリを検出します。データベースロードチャートで SQL、ユーザー、待機時間などのディメンションをモニタリングします。また、[データベーステレメトリ] タブの [スロー SQL クエリ] セクションもモニタリングしてください。さらに、Performance Insights の SQL 統計からクエリの実行を分析できます。例えば、1 回の呼び出しで調べた行数が通常よりも多い場合、実行プランは非効率的です。

メトリクスを使用して DB インスタンスのシステムリソースをモニタリングする

CPU 使用率が高い、メモリが少ない、またはワークロードが大きく DB インスタンスクラスの処理能力を超えると、SELECT ステートメントの実行が遅くなる可能性があります。DB インスタンスのリソースをモニタリングするには、以下のツールを使用します。

CPU 使用率をモニタリングするには、Amazon Aurora の Amazon CloudWatch メトリクスを使用します。
オペレーティングシステム (OS) メトリクスの詳細を表示するには、拡張モニタリングを使用します。
DB 負荷が vCPU の最大値を超えているかどうかを確認するには、[データベーステレメトリ] タブの OS プロセスデータを使用します。

ディスクシークが原因で SELECT ステートメントの実行が遅くなることがあります。ディスク I/O を最小限に抑えるため、データベースエンジンはディスクから読み込まれたブロックをキャッシュします。データベースが同じデータを必要とする場合、ディスクではなくメモリからデータを取得します。特定のクエリをディスクから処理するのか、メモリから処理するのかを判断するには、次のメトリクスを使用します。

ReadIOPS メトリクスを調べると、ディスク I/O 操作の数を確認できます。この値をできるだけ低く抑えるのがベストプラクティスです。
BufferCacheHitRatio メトリクスを調べると、バッファキャッシュが処理するリクエストの割合を確認できます。この値をできるだけ高く保つことがベストプラクティスです。
FreeableMemory メトリクスを調べると、DB インスタンスの使用可能なメモリを確認できます。この値を一定に保つことがベストプラクティスです。使用可能なメモリが不足すると、BufferCacheHitRatio が低くなり、ReadIOPS が高くなる可能性があります。
注: BufferCacheHitRatio メトリクスが低下し、SELECT ステートメントの処理が遅くなると、エンジンは基盤となるボリュームからのクエリを処理します。

ローカルストレージのディスクシークが原因で、SELECT ステートメントの実行が遅くなることもあります。Aurora MySQL 互換は、手動および内部の一時テーブルにローカルストレージを使用します。詳細については、「Aurora MySQL バージョン 3 での新しい一時テーブルの動作」を参照してください。ローカルストレージのディスクシークをモニタリングして解決するには、Database Insights の Rdstemp OS メトリクスを確認してください。これらの値はできるだけ低く抑えるのがベストプラクティスです。

ネットワークが飽和状態になると、SELECT ステートメントの実行が遅くなる可能性があります。Aurora はネットワーク経由でクラスターボリュームへの I/O 操作を実行し、クエリの結果をネットワーク経由でクライアントに送信します。ネットワークの飽和状態をモニタリングして解決するには、NetworkThroughput および StorageNetworkThroughput のメトリクスを確認してください。ネットワークスループットの合計は、DB インスタンスのネットワーク帯域幅より小さくなければなりません。

ワークロードが原因で、リソース使用量が DB インスタンスタイプの能力を超えている場合は、DB インスタンスクラスをアップグレードしてください。

デッドロックとブロッキングロックを特定する

デッドロックは、2 つ以上のトランザクションが互いにブロックされて続行できない場合に発生します。データベースのデッドロックを特定するには、パラメータグループで innodb_print_all_deadlocks パラメータを有効にします。詳細については、MySQL のウェブサイトの「innodb_print_all_deadlocks」を参照してください。次に、Aurora と RDS コンソール、AWS コマンドラインインターフェイス、または API から mysql-error.log をモニタリングします。

(オプション) デッドロックを識別するには、MySQL 管理者アカウントにログインし、次のコマンドを実行します。

SHOW ENGINE INNODB STATUS\G;

注: MySQL Workbench の予想される出力で、[Latest Detected Deadlock] セクションを確認してください。

デッドロックが発生していなくても、ロックを保持している長いトランザクションの 1 つがブロッキングロックである可能性があります。詳細については、「他にアクティブなセッションがないにもかかわらず、Amazon RDS for MySQL DB インスタンスへのクエリがブロックされた理由を知りたいです」を参照してください。

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

クエリにインデックスがない場合や、テーブル全体がスキャンされる場合は、クエリの実行速度が遅くなります。インデックスを使用すると、SELECT ステートメントの実行速度が速くなります。クエリでインデックスが使用されているかどうかを確認するには、EXPLAIN ステートメントを使用します。詳細については、MySQL のウェブサイトで「EXPLAIN Statement」(EXPLAIN ステートメント) を参照してください。

EXPLAIN 出力で、クエリ中にエンジンがスキャンするテーブル名、キー、行数を確認します。使用されるキーが出力に表示されていない場合は、WHERE 句でその列にインデックスを作成します。テーブルに必要なインデックスが配置されている場合は、統計テーブルが最新かどうかを確認してください。ANALYZE 句を使用して統計情報を更新します。詳細については、MySQL のウェブサイトで「The INFORMATION_SCHEMA STATISTICS table」(INFORMATION_SCHEMA STATISTICS テーブル) を参照してください。

実行速度が遅い SELECT ステートメントを特定するには、slow_query_log を使用してください。実行速度の遅いクエリを記録するには、DB クラスターのスロークエリのログ記録を有効にしてください。

HLL を確認する

InnoDB は、Multi-Version Concurrency Control (MVCC) を使用します。MVCCは、読み取りの一貫性を保つために同じレコードの複数のコピーを保持します。HLL は、履歴リスト内の、変更を含む取り消しログの総数です。データの書き込みや読み取りを長時間実行するトランザクションがある場合、そのトランザクションが完了するかロールバックが行われるまで、履歴リストの長さは増加します。他のトランザクションは、長時間実行されているトランザクションが使用するテーブルを変更します。ワークロードで複数のオープントランザクションまたは長時間実行トランザクションが必要な場合は、データベースの HLL が高くなる可能性があります。また、消去スレッドが DB の変更に追いつけない場合も、HLL が高くなっている可能性があります。HLL が高くなると、リソースの使用量が増え、SELECT ステートメントのパフォーマンスが遅くなり、一貫性がなくなり、ストレージが増加します。極端なケースでは、HLL が高くなるとデータベースが停止することがあります。

HLL をモニタリングするには、ライターインスタンスの RollbackSegmentHistoryListLength メトリクスを使用してください。または、次のコマンドを実行します。

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 互換の場合、HLL はクラスターレベルになります。クラスターレベルで HLL を確認するには、ライターインスタンスに接続して次のステートメントを実行します。

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 インスタンスの最も古い読み取りビュートランザクション ID (Trx ID) を確認します。いずれかのインスタンスに古い読み取りビューが含まれていることを確認してください。

古い読み取りビューを含むインスタンスに接続するには、次のステートメントを実行します。

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

注: 前のステートメントを使用して、最も古い trx_id を持つセッションまたはトランザクションを特定します。消去操作のブロックを解除するには、セッションを終了できるかどうかを判断してください。

高い HLL の問題を解消するには、次のアクションを実行してください。

DML 書き込みによって HLL が増加した場合は、トランザクションをロールバックしてクエリをキャンセルします。ロールバックする必要がある更新の数が多いため、このプロセスには時間がかかります。
読み取りによって HLL が増加する場合は、mysql.rds_kill_query を使用してクエリをキャンセルします。
注: データベース管理者に問い合わせて、クエリをキャンセルできるかどうかを確認してください。

HLL が高くなるのを防ぐためのベストプラクティスは、データを少量ずつコミットすることです。また、DB クラスターまたはインスタンスを再起動しないでください。HLL がバッファープール内のメモリデータにアクセスできるようになったら、HLL を消去します。データベースを再起動すると、存続可能なページキャッシュが失われる可能性があります。存続可能なページキャッシュが失われた場合は、クラスターボリュームからデータページを読み取って HLL を消去する必要があります。この処理はメモリからの消去よりも時間がかかり、追加の I/O 請求コストが発生します。