pg_stat_statements
モジュールは、サーバで実行されたすべてのSQL文のプラン生成時と実行時の統計情報を記録する手段を提供します。
このモジュールは追加の共有メモリを必要とするため、postgresql.conf
のshared_preload_librariesにpg_stat_statements
を追加してモジュールをロードしなければなりません。
このことは、このモジュールを追加もしくは削除するには、サーバを再起動する必要があるということを意味しています。
pg_stat_statements
はロードされると、サーバのデータベース全体に渡って統計情報を記録します。
この統計情報にアクセスしたり操作したりするために、このモジュールはビューpg_stat_statements
とユーティリティ関数pg_stat_statements_reset
、pg_stat_statements
を提供します。
これらは大域的に利用可能ではなく、CREATE EXTENSION pg_stat_statements
で特定のデータベースで可能になります。
pg_stat_statements
ビュー
このモジュールにって収集された統計情報は、pg_stat_statements
というビューを通して利用することができます。
このビューは、1行に対して、それぞれ個々のデータベースID、ユーザID、および問い合わせIDを含んでいます(モジュールが記録できるSQL文の最大数まで)。
ビューの列は、表 F.21に示す通りです。
表F.21 pg_stat_statements
の列
列 型 説明 |
---|
SQL文を実行したユーザのOID |
SQL文が実行されたデータベースのOID |
SQL文の解析木から計算された内部ハッシュコード |
代表的なSQL文の文字列 |
SQL文がプラン生成された回数
( |
SQL文のプラン生成に費やした総時間(ミリ秒単位)
( |
SQL文のプラン生成に費やした最小時間(ミリ秒単位)
( |
SQL文のプラン生成に費やした最大時間(ミリ秒単位)
( |
SQL文のプラン生成に費やした平均時間(ミリ秒単位)
( |
SQL文のプラン生成に費やした時間の母標準偏差(ミリ秒単位)
( |
SQL文が実行された回数 |
SQL文の実行に費やした総時間(ミリ秒単位) |
SQL文の実行に費やした最小時間(ミリ秒単位) |
SQL文の実行に費やした最大時間(ミリ秒単位) |
SQL文の実行に費やした平均時間(ミリ秒単位) |
SQL文の実行に費やした時間の母標準偏差(ミリ秒単位) |
SQL文によって取得された、あるいは影響を受けた行の総数 |
SQL文によってヒットした共有ブロックキャッシュの総数 |
SQL文によって読み込まれた共有ブロックの総数 |
SQL文によってダーティ状態となった共有ブロックの総数 |
SQL文によって書き込まれた共有ブロックの総数 |
SQL文によってヒットしたローカルブロックキャッシュの総数 |
SQL文によって読み込まれたローカルブロックの総数 |
SQL文によってダーティ状態となったローカルブロックの総数 |
SQL文によって書き込まれたローカルブロックの総数 |
SQL文によって読み込まれた一時ブロックの総数 |
SQL文によって書き込まれた一時ブロックの総数 |
SQL文がブロック読み取りに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ) |
SQL文がブロック書き出しに費やした総時間(ミリ秒単位) (track_io_timingが有効な場合。無効であればゼロ) |
SQL文により生成されたWALレコードの総数 |
SQL文により生成されたWALフルページイメージの総数 |
SQL文により生成されたWALバイトの総量 |
セキュリティ上の理由から、スーパーユーザとpg_read_all_stats
ロールのメンバだけが、他のユーザによって実行されたSQLテキストや問い合わせのqueryid
を見ることができます。
ただし、ユーザのデータベースにビューがインストールされている場合、統計情報については他のユーザから見ることができます。
計画作成が可能な問い合わせ(つまりSELECT
、INSERT
、UPDATE
、DELETE
)は、内部のハッシュ計算に従った、同一の問い合わせ構造を持つ限り、1つのpg_stat_statements
項目に組み合わせられます。
典型的には、2つの問い合わせは、問い合わせの中に現れるリテラル定数の値以外、意味的に等価である場合、この目的では同一とみなされます。
しかし、ユーティリティコマンド(つまりこの他のコマンドすべて)は問い合わせ文字列のテキストを基に厳密に比較されます。
他の問い合わせと合致させるために定数値が無視された場合、pg_stat_statements
の表示の中で定数は$1
のようなパラメータ記号に置換されます。
問い合わせの残りのテキストは、pg_stat_statements
項目に関連付いた特定のqueryid
ハッシュ値を持つ、1つ目の問い合わせのテキストです。
一部の状況では、見た目上異なるテキストを持つ問い合わせが1つのpg_stat_statements
項目にまとめられることがあります。
通常これは意味的に等しい問い合わせでのみ発生しますが、関連がない問い合わせが1つの項目にまとめられるハッシュ競合の可能性がわずかながら存在します。
(しかしこれは別のユーザまたは別のデータベースに属する問い合わせでは発生することはあり得ません。)
queryid
ハッシュ値は問い合わせの解析後の表現に対して計算されますので、search_path
の設定が異なる等の要因の結果として異なる意味を持つ場合、同じテキストを持つ問い合わせが別の項目として現れるという、反対もまたあり得ます。
pg_stat_statements
の消費者は、問い合わせテキストよりもより安定で信頼できる各項目への識別子として(おそらくdbid
やuserid
と組み合わせて)queryid
を使いたいかもしれません。
しかし、queryid
ハッシュ値の安定性には限定された保証しかないのを理解することは重要です。
識別子は解析後の木から得られますので、その値は、とりわけ、この表現に現れる内部オブジェクト識別子の関数です。
これは少々直観に反する結果です。
例えば、pg_stat_statements
は見たところは同一の問い合わせを、それらが2つの問い合わせの実行の間に削除され再作成されたテーブルを参照しているのであれば、別個のものとみなします。
ハッシュ処理はプラットフォームのマシンアーキテクチャやその他の面の違いにも敏感です。
その上、PostgreSQLのメジャーバージョンをまたがってqueryid
が安定であるとみなすのは安全ではありません。
経験上、queryid
値は、基礎となるサーバのバージョンとカタログメタデータの詳細が全く同じである限り、安定していて比較可能とみなすことができます。
物理WAL再生に基づくレプリケーションに参加する2つのサーバでは、同じ問い合わせに対して同一のqueryid
値を持つことが期待できます。
しかし、論理レプリケーションの仕組みは、レプリカが対応する詳細すべてで同一であることを約束しません。そのため、論理レプリカの集まりで増えるコストを識別するのにqueryid
は有用ではありません。
疑わしければ、直接テストすることを薦めます。
代表的な問い合わせテキストの定数を置き換えるのに使われたパラメータ記号は、元の問い合わせテキストの最も大きな$
n
パラメータの次の数字から、もしそれがなければ$1
から始まります。
ある場合には、この番号付けに影響する隠れたパラメータ記号があるかもしれないことに言及しておく価値はあります。
例えば、PL/pgSQLは関数の局所変数の値を問い合わせに挿入するために隠れたパラメータ記号を使います。そのため、SELECT i + 1 INTO j
のようなPL/pgSQL文はSELECT i + $2
のような代表的なテキストになります。
代表的な問い合わせテキストは外部ディスクファイルに保持され、共有メモリを消費しません。
そのため、非常に長い問い合わせテキストであっても保存に成功します。
しかし、数多くの長い問い合わせテキストが蓄積されると、外部ファイルは手に負えないくらい大きくなるかもしれません。
もしそのようなことが起きれば、回復手法として、pg_stat_statements
は問い合わせテキストを破棄することを選ぶでしょう。その結果、各queryid
に関連する統計は保存されるものの、pg_stat_statements
ビュー内に存在するエントリはすべてquery
フィールドがヌルになります。
もしこのようなことが起きたら、再発防止のためpg_stat_statements.max
を減らすことを検討してください。
プラン生成時と実行時の統計情報はそれぞれの終了フェーズで更新され、操作に成功した場合にのみ更新されるため、plans
とcalls
は常に一致するとは限りません。
例えば、SQL文のプラン生成に成功しても実行フェーズの間に失敗した場合、そのプラン生成時の統計情報のみが更新されます。
キャッシュされたプランが使用されプラン生成がスキップされた場合、実行時の統計情報のみが更新されます。
pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void
pg_stat_statements_reset
は指定されたuserid
、dbid
、queryid
に対応するpg_stat_statements
によってこれまでに収集したすべての統計情報を削除します。
いずれかのパラメータを指定しないのであれば、デフォルト値0
(無効)を使ってください。他のパラメータに一致する統計情報がリセットされます。
どのパラメータも指定しない、または、すべての指定されたパラメータが0
(無効)ならば、すべての統計情報を削除します。
デフォルトでは、この関数はスーパーユーザのみ実行できます。
GRANT
を使ってアクセス権を他のユーザに付与できます。
pg_stat_statements(showtext boolean) returns setof record
pg_stat_statements
ビューは同じくpg_stat_statements
という名前の関数の項で定義されています。
クライアントがpg_stat_statements
関数を直接呼び出し、showtext := false
と指定することで問い合わせテキストを省略することが可能です(すなわち、ビューのquery
列に対応するOUT
引数はNULLを返します)。
この機能は不定長の問い合わせテキストを繰り返し取得するオーバヘッドを避けたいと考える外部のツールをサポートすること意図したものです。
そのようなツールは代わりに、それがpg_stat_statements
自身が行なっていることのすべてですので、各項目で最初に観察された問い合わせテキストをキャッシュし、必要とされる問い合わせテキストのみを取得できます。
サーバは問い合わせテキストをファイルに格納しますので、この方法はpg_stat_statements
データの繰り返しの検査に対する物理I/Oを減らすでしょう。
pg_stat_statements.max
(integer
)
pg_stat_statements.max
は、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statements
ビューにおける行の最大数)です。これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。
デフォルトは5000です。
このパラメータはサーバの起動時にのみ指定できます。
pg_stat_statements.track
(enum
)
pg_stat_statements.track
は、どのSQL文をモジュールによって計測するかを制御します。
top
を指定した場合は(直接クライアントによって発行された)最上層のSQL文を記録します。
all
は(関数の中から呼び出された文などの)入れ子になった文も記録します。
none
は文に関する統計情報収集を無効にします。
デフォルトはtop
です。
この設定はスーパーユーザだけが変更できます。
pg_stat_statements.track_utility
(boolean
)
pg_stat_statements.track_utility
は、このモジュールがユーティリティコマンドを記録するかどうかを指定します。
ユーティリティコマンドとは、 SELECT
、INSERT
、UPDATE
およびDELETE
以外のすべてです。
デフォルトはon
です。
この設定はスーパーユーザのみが変更できます。
pg_stat_statements.track_planning
(boolean
)
pg_stat_statements.track_planning
は、このモジュールがプラン生成の操作と時間を記録するかどうかを指定します。
このパラメータを有効にすると、特に多数の同時接続で実行されるクエリの種類が少ない場合にパフォーマンスが著しく低下する可能性があります。
デフォルトはoff
です。
この設定はスーパーユーザのみが変更できます。
pg_stat_statements.save
(boolean
)
pg_stat_statements.save
は、サーバを終了させる際に文の統計情報を保存するかどうかを指定します。
off
の場合、統計情報は終了時に保存されず、サーバ開始時に再読み込みもされません。
デフォルト値はon
です。
このパラメータはpostgresql.conf
ファイル、またはサーバコマンドラインでのみ設定できます。
このモジュールは、pg_stat_statements.max
に比例する追加の共有メモリを必要とします。
pg_stat_statements.track
にnone
が設定されていても、モジュールがロードされている限り常にこのメモリが消費されることに注意してください。
これらのパラメータはpostgresql.conf
の中で設定しなければなりません。
典型的な使用方法は以下のようになります。
# postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; -[ RECORD 1 ]---+-------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 calls | 3000 total_exec_time | 25565.855387 rows | 3000 hit_percent | 100.0000000000000000 -[ RECORD 2 ]---+-------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 calls | 3000 total_exec_time | 20756.669379 rows | 3000 hit_percent | 100.0000000000000000 -[ RECORD 3 ]---+-------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_exec_time | 291.865911 rows | 100000 hit_percent | 100.0000000000000000 -[ RECORD 4 ]---+-------------------------------------------------------------------- query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 calls | 3000 total_exec_time | 271.232977 rows | 3000 hit_percent | 98.8454011741682975 -[ RECORD 5 ]---+-------------------------------------------------------------------- query | alter table pgbench_accounts add primary key (aid) calls | 1 total_exec_time | 160.588563 rows | 0 hit_percent | 100.0000000000000000 bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2'; bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; -[ RECORD 1 ]---+-------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 calls | 3000 total_exec_time | 20756.669379 rows | 3000 hit_percent | 100.0000000000000000 -[ RECORD 2 ]---+-------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_exec_time | 291.865911 rows | 100000 hit_percent | 100.0000000000000000 -[ RECORD 3 ]---+-------------------------------------------------------------------- query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 calls | 3000 total_exec_time | 271.232977 rows | 3000 hit_percent | 98.8454011741682975 -[ RECORD 4 ]---+-------------------------------------------------------------------- query | alter table pgbench_accounts add primary key (aid) calls | 1 total_exec_time | 160.588563 rows | 0 hit_percent | 100.0000000000000000 -[ RECORD 5 ]---+-------------------------------------------------------------------- query | vacuum analyze pgbench_accounts calls | 1 total_exec_time | 136.448116 rows | 0 hit_percent | 99.9201915403032721 bench=# SELECT pg_stat_statements_reset(0,0,0); bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; -[ RECORD 1 ]---+----------------------------------------------------------------------------- query | SELECT pg_stat_statements_reset(0,0,0) calls | 1 total_exec_time | 0.189497 rows | 1 hit_percent | -[ RECORD 2 ]---+----------------------------------------------------------------------------- query | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit / + | nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+ | FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3 calls | 0 total_exec_time | 0 rows | 0 hit_percent |
板垣 貴裕 <itagaki.takahiro@oss.ntt.co.jp>
。
Peter Geoghegan <peter@2ndquadrant.com>
により問い合わせの正規化が追加されました。