PostgreSQL 8.4 の新機能 である pg_stat_statements と auto_explain を試してみましたのでまとめておきます。
まずは
pg_stat_statements です。pg_stat_statements は簡単に言うと SQL のプロファイリングを行う機能で、SQL ごとに総実行回数や総実行時間などを取得できるものです。pg_stat_statements は PostgreSQL のソースの tar ボールを展開したディレクトリの contrib/pg_stat_statements にあります。まずはそのディレクトリに移動して make します。次に make してできた pg_stat_statements.so を PostgreSQL のインストールディレクトリにある lib ディレクトリにコピーします。pg_stat_statements.sql もあとで使いますのでどこか適当なディレクトリに移動しておくと良いと思います。
次いで postgresql.conf を以下のように編集します。
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
個別に設定できる値は以下の通りです。
pg_stat_statements.max
- 記録するステートメントの最大値を指定します。デフォルト値は 1000 です。
pg_stat_statements.track
- カウントするステートメントの種類を指定します。指定できる値は top, all, none の 3種類です。all はクエリ内の関数から呼び出される (ネストした) ステートメントもカウントします。top は all でカウントされるようなネストしたクエリはカウントされず、top-level のステートメントのみカウントします。none を指定するとこの昨日自体を無効化します。デフォルト値は top です。
pg_stat_statements.save
- サーバ停止時にステートメントの統計値を保持・維持しておくかどうかを指定します。off を指定するとサーバ再起動時にはステートメントの統計値はリセットされます。デフォルト値は on です。
postgresql.conf を編集できたら PostgreSQL を再起動します。
再起動したら、動作確認用のデータベースを作成します。
$ createdb --encoding=UTF8 test
$ psql -l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-----------+-------+-----------------------
test | postgres | UTF8 | C | C |
今回は pgbench で動作確認をしますので pgbench 用の環境を用意します。
$ pgbench -i test
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.
そして、pg_stat_statements.sql を流し込みます。
$ psql -f pg_stat_statements.sql test
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
GRANT
REVOKE
この SQL を実行すると pg_stat_statements_reset(), pg_stat_statements() という関数と、pg_stat_statements という VIEW が作成されます。作成されたら念のため最初にステートメントの統計情報を初期化しておきます。
$ psql -c "SELECT pg_stat_statements_reset();" test
pg_stat_statements_reset
--------------------------
(1 row)
pgbench を実行し、ステートメントの統計情報を見てます。
$ pgbench -c10 -t300 -M prepared test
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: prepared
number of clients: 10
number of transactions per client: 300
number of transactions actually processed: 3000/3000
tps = 567.416343 (including connections establishing)
tps = 569.828493 (excluding connections establishing)
$ psql test
psql (8.4.0)
Type "help" for help.
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
-[ RECORD 1 ]--------------------------------------------------------------------
userid | 10
dbid | 40966
query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2;
calls | 3000
total_time | 22.7689049999999
rows | 3000
-[ RECORD 2 ]--------------------------------------------------------------------
userid | 10
dbid | 40966
query | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;
calls | 3000
total_time | 18.8488639999999
rows | 3000
-[ RECORD 3 ]--------------------------------------------------------------------
userid | 10
dbid | 40966
query | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2;
calls | 3000
total_time | 0.273943
rows | 3000
次に
auto_explain です。auto_explain はスロークエリの実行計画をログに出力するという機能です。pg_stat_statements 同様 contrib/auto_explain ディレクトリに移動し make します。make してできた auto_explain.so をこれも同様に lib ディレクトリにコピーします。
postgresql.conf も同様に編集します。shared_preload_libraries で複数のライブラリをロードする場合の参考にもなるので、先ほどの pg_stat_statements の設定はあえて残し、auto_explain の設定を追記する形で示します。
shared_preload_libraries = 'pg_stat_statements, auto_explain'
custom_variable_classes = 'pg_stat_statements, auto_explain'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
auto_explain.log_min_duration = '0'
個別に設定できる値は以下の通りです。
auto_explain.log_min_duration
- ロギング対象とするステートメントの実行時間をミリ秒単位で指定します。ゼロを指定するとすべてのステートメントがロギング対象となり、マイナスの値を指定するとロギングを無効化します。他の設定値と同様 1秒 (1000) を 1s などと指定することもできます。
auto_explain.log_analyze
- 単なる EXPLAIN ではなく EXPLAIN ANALYZE の結果を出力するかどうかを指定します。デフォルトは off です。場合によってはパフォーマンスに悪影響が出るので注意が必要です。
auto_explain.log_verbose
- 単なる EXPLAIN ではなく EXPLAIN VERBOSE の結果を出力するかどうかを指定します。デフォルトは off です。
auto_explain.log_nested_statements
- 関数内で実行されるステートメントのようにネストされたステートメントをロギング対象に含めるかどうかを指定します。デフォルトは off です。
ここでは動作確認のため auto_explain.log_min_duration を 0 にし、すべてのステートメントがロギング対象となるようにしています。編集が完了したら PostgreSQL を再起動します。
起動したら適当なクエリを投げてみます。
$ psql -c "SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique;" test
ログを見てみると…
LOG: duration: 0.240 ms plan:
Aggregate (cost=21.79..21.80 rows=1 width=0)
-> Hash Join (cost=15.27..21.46 rows=129 width=0)
Hash Cond: (pg_index.indrelid = pg_class.oid)
-> Seq Scan on pg_index (cost=0.00..4.42 rows=129 width=4)
Filter: indisunique
-> Hash (cost=11.23..11.23 rows=323 width=4)
-> Seq Scan on pg_class (cost=0.00..11.23 rows=323 width=4)
STATEMENT: SELECT count(*) FROM pg_class, pg_index WHERE oid = indrelid AND indisunique;
ちゃんとロギングされています。
いずれの機能も DB 運用担当者には嬉しい機能ですね。
そのほか 8.4 の新機能についてわかりやすくまとまっているページのリンクを張っておきます。個別詳細に踏み込んではいませんが、ざっと眺めるには良いページです。ちなみに著者は pg_stat_statements, auto_explain の作者の方のようです。
という本の内容に通じるところが多かったように思います。