すべてのプロダクト
Search
ドキュメントセンター

ApsaraDB RDS:リソース消費量が最も多いSQL文を見つける

最終更新日:Jan 17, 2024

ApsaraDB RDS for PostgreSQLインスタンスは、大規模なインスタンスアプリケーションです。 RDSインスタンスが大量のリクエストを処理すると、大量のメモリ、CPU、I/O、およびネットワークリソースを消費します。 SQL最適化は、効果的なインスタンス最適化方法です。 最適なSQL最適化の結果を得るには、I/Oリソースなど、最も多くのリソースを消費するSQL文を特定する必要があります。

インスタンスリソースには、CPUリソース、メモリリソース、およびI/Oリソースが含まれます。 pg_stat_statementsプラグインを使用して、RDSインスタンスの消費リソースに関する統計を収集し、実行されたSQL文を分析して、CPU、メモリ、またはI/Oリソースを最も多く消費するSQL文を特定できます。

このトピックでは、pg_stat_statementsプラグインを作成し、最も多くのリソースを消費するSQL文を分析し、リソース消費の統計をリセットする方法について説明します。

次のコマンドを実行して、RDSインスタンスにpg_stat_statementsプラグインを作成します。

拡張の作成pg_stat_statements;

pg_statementsプラグインによって生成されたリソース消費統計

pg_stat_statementsプラグインによって生成されたビューからリソース消費統計を照会できます。 SQL文の一部のフィルタ条件は、重複する統計を減らすためにpg_stat_statementsプラグインの変数に置き換えられます。

pg_stat_statementsプラグインによって生成されるビューは、次の重要な情報を提供します。
  • SQL文の実行回数、合計実行時間、最短実行時間、最長実行時間、平均実行時間、実行時間の分散、スキャンされた行の総数、返された行の総数、処理された行の総数など、各SQL文に関する情報。
  • ヒット率、ミス率、生成されたダーティデータブロックの数、および追い出されたダーティデータブロックの数を含む共有バッファの使用。
  • ヒット率、ミス率、生成されたダーティデータブロックの数、および追い出されたダーティデータブロックの数を含むローカルバッファの使用。
  • 読み取られたダーティデータブロックの数と追い出されたダーティデータブロックの数を含む、一時バッファの使用。
  • RDSインスタンスの各データブロックに対する読み取り操作の期間と書き込み操作の長さ。
次の表に、pg_stat_statementsプラグインによって生成されるリソース消費統計のパラメーターを示します。
名前データ型説明
useridoidpg_authid.oidステートメントを実行したユーザーのOID。
dbidoidpg_database.oidステートメントが実行されたデータベースのOID。
queryidbigintなし内部ハッシュコード。ステートメントの解析ツリーから計算されます。
querytextなし代表的な声明のテキスト。
呼び出しbigintなし実行された回数。
total_timeダブル精度なしステートメントで費やされた合計時間 (ミリ秒単位) 。
min_timeダブル精度なしステートメントで費やされた最小時間 (ミリ秒単位) 。
max_timeダブル精度なしステートメントで費やされた最大時間 (ミリ秒単位) 。
mean_timeダブル精度なしステートメントで費やされた平均時間 (ミリ秒単位) 。
stddev_timeダブル精度なしステートメントで費やされた時間の人口標準偏差 (ミリ秒単位) 。
rowsbigintなしステートメントによって取得または影響を受ける行の総数。
shared_blks_hitbigintなしステートメントによる共有ブロックキャッシュヒットの総数。
shared_blks_readbigintなしステートメントによって読み取られた共有ブロックの総数。
shared_blks_dirtiedbigintなしステートメントによって汚れた共有ブロックの総数。
shared_blks_writtenbigintなしステートメントによって書き込まれた共有ブロックの総数。
local_blks_hitbigintなしステートメントによるローカルブロックキャッシュヒットの総数。
local_blks_readbigintなしステートメントによって読み取られたローカルブロックの総数。
local_blks_dirtiedbigintなしステートメントによって汚れたローカルブロックの総数。
local_blks_writtenbigintなしステートメントによって書き込まれたローカルブロックの総数。
temp_blks_readbigintなしステートメントによって読み取られた一時ブロックの総数。
temp_blks_書き込みbigintなしステートメントによって書き込まれた一時ブロックの総数。
blk_read_timeダブル精度なしステートメントがブロックの読み取りに費やした合計時間 (ミリ秒単位) (track_io_timingが有効な場合、それ以外はゼロ) 。
blk_write_timeダブル精度なしステートメントがブロックの書き込みに費やした合計時間 (ミリ秒単位) (track_io_timingが有効な場合、それ以外はゼロ) 。

最も多くのリソースを消費するSQL文を分析する

  • I/Oリソースを最も多く消費するSQL文
    • 次のコマンドを実行して、1回の呼び出しで最も多くのI/Oリソースを消費する上位5つのSQL文を表示します。
      SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time + blk_write_time)/calls DEC_LIMIT 5;
    • 次のコマンドを実行して、合計で最も多くのI/Oリソースを消費する上位5つのSQL文を表示します。
      SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time + blk_write_time) DECC LIMIT 5;
  • 最も時間がかかるSQL文
    • 次のコマンドを実行して、1回の呼び出しで最も時間を消費する上位5つのSQL文を表示します。
      SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DECC LIMIT 5;
    • 次のコマンドを実行して、合計で最も時間を消費する上位5つのSQLステートメントを表示します。
      SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DECC LIMIT 5;
  • 最も深刻なレスポンスジッタを持つSQL文
    次のコマンドを実行して、最も深刻なレスポンスジッタを持つ上位5つのSQL文を表示します。
    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY stddev_time DECC LIMIT 5;
  • 最も共有されるメモリリソースを消費するSQL文
    次のコマンドを実行して、最も共有されているメモリリソースを消費する上位5つのSQLステートメントを表示します。
    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blks_hit + shared_blks_dirtied) DECC LIMIT 5;
  • SQL文は最も一時的なスペースを消費します
    次のコマンドを実行して、最も一時的なスペースを消費する上位5つのSQL文を表示します。
    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY temp_blks_writted DESC LIMIT 5;

リソース消費統計のリセット

pg_stat_statementsプラグインは累積統計を収集します。 特定の期間の統計を表示するには、RDSインスタンスのスナップショットをクエリする必要があります。 詳細については、「PostgreSQL AWRレポート (ApsaraDB PgSQLの場合) 」をご参照ください。

次のコマンドを実行して、履歴統計を定期的に削除できます。
SELECT pg_stat_statments_reset ();

参考資料

詳細については、『PostgreSQL 9.6.2ドキュメント-F.29』をご参照ください。 pg_stat_文