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

ApsaraDB 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 文を分析し、リソース消費の統計をリセットする方法について説明します。

説明

[SQL Explorer および監査] 機能を有効にして、SQL 文の実行を記録し、実行の集計分析を実行できます。この機能を有効にすると、データベースカーネルからの SQL 文と、実行の詳細、実行アカウント、IP アドレスなどの文情報が自動的に記録されます。これはインスタンスのパフォーマンスには影響しません。詳細については、「SQL Explorer および監査機能を使用する」をご参照ください。

RDS インスタンスに pg_stat_statements 拡張機能を作成するには、次の文を実行します。

CREATE EXTENSION pg_stat_statements;

pg_stat_statements 拡張機能によって生成されるリソース消費統計

pg_stat_statements 拡張機能によって生成されるビューから、リソース消費統計をクエリできます。 SQL 文の一部のフィルター条件は、重複する統計を減らすために、pg_stat_statements 拡張機能の変数に置き換えられます。

pg_stat_statements 拡張機能によって生成されるビューは、次の重要な情報を提供します。

各 SQL 文に関する情報。SQL 文が実行された回数、合計実行時間、最短実行時間、最長実行時間、平均実行時間、実行時間の分散、スキャンされた行の合計数、返された行の合計数、処理された行の合計数など。実行時間の分散は、ネットワークジッターを反映するために使用されます。
共有バッファーの使用状況。ヒット率、ミス率、生成されたダーティデータブロックの数、エビクトされたダーティデータブロックの数など。
ローカルバッファーの使用状況。ヒット率、ミス率、生成されたダーティデータブロックの数、エビクトされたダーティデータブロックの数など。
一時バッファーの使用状況。読み取られたダーティデータブロックの数、エビクトされたダーティデータブロックの数など。
RDS インスタンスの各データブロックに対する読み取りおよび書き込み操作の期間。

次の表に、pg_stat_statements 拡張機能によって生成されるリソース消費統計のパラメーターを示します。

パラメーター	タイプ	例	説明
userid	oid	pg_authid.oid	文を実行したユーザーの OID。
dbid	oid	pg_database.oid	文が実行されたデータベースの OID。
queryid	bigint	なし	文の解析ツリーから計算された内部ハッシュコード。
query	text	なし	代表的な文のテキスト。
calls	bigint	なし	実行回数。
total_time	double precision	なし	文に費やされた合計時間 (ミリ秒)。
min_time	double precision	なし	文に費やされた最小時間 (ミリ秒)。
max_time	double precision	なし	文に費やされた最大時間 (ミリ秒)。
mean_time	double precision	なし	文に費やされた平均時間 (ミリ秒)。
stddev_time	double precision	なし	文に費やされた時間の母集団標準偏差 (ミリ秒)。
rows	bigint	なし	文によって取得または影響を受けた行の合計数。
shared_blks_hit	bigint	なし	文による共有ブロックキャッシュヒットの合計数。
shared_blks_read	bigint	なし	文によって読み取られた共有ブロックの合計数。
shared_blks_dirtied	bigint	なし	文によってダーティ化された共有ブロックの合計数。
shared_blks_written	bigint	なし	文によって書き込まれた共有ブロックの合計数。
local_blks_hit	bigint	なし	文によるローカルブロックキャッシュヒットの合計数。
local_blks_read	bigint	なし	文によって読み取られたローカルブロックの合計数。
local_blks_dirtied	bigint	なし	文によってダーティ化されたローカルブロックの合計数。
local_blks_written	bigint	なし	文によって書き込まれたローカルブロックの合計数。
temp_blks_read	bigint	なし	文によって読み取られた一時ブロックの合計数。
temp_blks_written	bigint	なし	文によって書き込まれた一時ブロックの合計数。
blk_read_time	double precision	なし	文がブロックの読み取りに費やした合計時間 (ミリ秒)。track_io_timing が有効になっている場合。それ以外の場合はゼロ。
blk_write_time	double precision	なし	文がブロックの書き込みに費やした合計時間 (ミリ秒)。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 DESC LIMIT 5;
```
- 全体で最も多くの I/O リソースを消費する上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;
```
最も多くの時間を消費する SQL 文
- 1 回の呼び出しで最も多くの時間を消費する上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;
```
- 全体で最も多くの時間を消費する上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
```
応答ジッターが最も深刻な SQL 文
応答ジッターが最も深刻な上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY stddev_time DESC LIMIT 5;
```
最も多くの共有メモリリソースを消費する SQL 文
最も多くの共有メモリリソースを消費する上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blks_hit+shared_blks_dirtied) DESC LIMIT 5;
```
最も多くの一時領域を消費する SQL 文
最も多くの一時領域を消費する上位 5 つの SQL 文を表示するには、次の文を実行します。
```
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;
```

リソース消費統計をリセットする

pg_stat_statements 拡張機能は、累積統計を収集します。

次の文を実行して、履歴統計を定期的に削除できます。

SELECT pg_stat_statements_reset();

参照

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