2009-07-22

超交流会 2009

京大の OB ではないのですが京大情報学同窓会である 超交流会 2009 に行ってきました。堀場さんのお話もまぁおもしろかったですが、勝屋久さんのテンションがすごかったです。勝屋さんが話されていた内容としては 自分の小さな「箱」から脱出する方法 という本の内容に通じるところが多かったように思います。

  • 相手を変えようとするのではなく自分を変える

  • 相手は自分の鏡

  • などなど…


これらはほんとにそうだと思うので、自然にできる人になりたいものです。最初は意識して努力することが必要ですね。

そんな勝屋さんが最後に紹介されていた本の 1つが以下のものです。機会を作って読んでみようと思います。


アンビシャス 1分間スピーチというタイトルにはやられました。ライトニングトークのようなものを想像して、1分程度になるよう内容を構成し練習していったのですが 1分という縛りはないに等しかったです。でもまぁ良い経験でした。あのような場にもなるべく参加するようにしたほうが良いなぁと思う今日この頃です。

2009-07-02

PostgreSQL 8.4 の新モニタリング機能

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 の作者の方のようです。