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

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プラグインによって生成されるリソース消費統計のパラメーターを示します。

名前	データ型	例	説明
userid	oid	pg_authid.oid	ステートメントを実行したユーザーのOID。
dbid	oid	pg_database.oid	ステートメントが実行されたデータベースのOID。
queryid	bigint	なし	内部ハッシュコード。ステートメントの解析ツリーから計算されます。
query	text	なし	代表的な声明のテキスト。
呼び出し	bigint	なし	実行された回数。
total_time	ダブル精度	なし	ステートメントで費やされた合計時間 (ミリ秒単位) 。
min_time	ダブル精度	なし	ステートメントで費やされた最小時間 (ミリ秒単位) 。
max_time	ダブル精度	なし	ステートメントで費やされた最大時間 (ミリ秒単位) 。
mean_time	ダブル精度	なし	ステートメントで費やされた平均時間 (ミリ秒単位) 。
stddev_time	ダブル精度	なし	ステートメントで費やされた時間の人口標準偏差 (ミリ秒単位) 。
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_書き込み	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_文