« で、fillfactor、autovacuumパラメータの設定方法 | トップページ | postgresql.confの罠 »

2014.02.05

ロック待ち処理の情報取得をしてみる

システム稼働後やストレステストを実施しているときに、DB側のロック解消待ちで処理が進まない、ということはまあざらにあるわけで。
Oracleだと"For update 処理 wait xx"、Informixなんかだと"SET LOCK MODE TO WAIT xx 処理" としてxx秒間でロックが解放されなかったら、トランザクションをアボートさせることができるのだが、どうもPostgreSQLではこいつはできないらしい。

#2014-12-02追記: Ver9.3から対応されてました(^^;
[役に立つ9.3以後の新機能]

NOWAITオプションはあるものの、一定時間経過後にアボート、というオプションがないため変な処理がロックを獲得すると、芋づる式に全滅というパターンが発生して焦ることが多々。
そこで誰が渋滞の先頭にいるのかを知りたくなるのは道理ということで、そのためのSQL等を準備してみた。

ロック状況についてはpg_locksビューに存在しているのだが、こいつではどのテーブルがどういう種類のロック獲得をしているのか、ロック待ちをしているのか、というところまでしか追い込めない。
今回興味があるのはロック獲得プロセスと、どんな処理が待たせてるのか、ということなので明らかに情報不足。
じゃあどこにそんな情報があるかといえばpg_stat_activityビューを使えば良いというのが解になろうか。

SELECT
  procpid
 , relname
 , application_name
 , client_addr, waiting
 , mode
 , CASE WHEN waiting IS TRUE THEN
  ( SELECT max(l.pid)
   FROM pg_locks AS l
   WHERE pg_locks.pid <> l.pid AND pg_locks.relation = l.relation )
  ELSE 0 END AS Blocked ,(current_timestamp - xact_start)::interval(3) AS duration
 , query_start
 , current_query
 , granted
 , now()
FROM pg_locks
 , pg_class
 , pg_stat_activity
WHERE pg_locks.relation = pg_class.oid
 AND pg_locks.pid = pg_stat_activity.procpid
 AND relkind = 'r'
 AND usename = 'hogehoge'

こんな感じでどうだろうか?
実は監視する、というだけであればpgAdminIII内のツールで上記のような内容を確認することができる。
メニューバーから [ツール]-[サーバ状態] で呼び出されるウィンドウである。
ただ、これはpgAdminIIIであることから、監視ツールとしては非常にいいのであるがあとからログ解析をするためには情報が蓄積できないことから、SQLを準備した。
このSQLをbashなどに仕込んで、psql経由でCOPYコマンドで一定間隔でファイルを吐かせてみてる。下記はそのbashサンプル。

#!/bin/bash
declare -i i;
for((i=0; $i<(${$1}); ++i));
do

echo "\COPY (SELECT ${i}, procpid, relname,application_name, client_addr, waiting , mode ,CASE WHEN waiting IS TRUE THEN ( SELECT max(l.pid) FROM pg_locks AS l WHERE pg_locks.pid <> l.pid AND pg_locks.relation = l.relation ) ELSE 0 END AS Blocked ,(current_timestamp - xact_start)::interval(3) AS duration , query_start, current_query, granted , now() FROM pg_locks, pg_class, pg_stat_activity WHERE pg_locks.relation = pg_class.oid AND pg_locks.pid = pg_stat_activity.procpid AND relkind = 'r') TO '${DIR}/lock_activity_${i}.txt';" > lock_activity.sql

psql -p ${PORT} -U ${USRID} ${DBNAME} -f lock_activity.sql << EOF

EOF

sleep 3
done

cat lock_activity_*.txt > lock_status_result.txt
rm -f lock_activity_*.txt


デフォルトのタブデリミタで出力されるので、後はExcelで好きなように加工可能なのと、必要な情報が他にもあれば、自由に追加するのもいいんではなかろうか?

|

« で、fillfactor、autovacuumパラメータの設定方法 | トップページ | postgresql.confの罠 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59063539

この記事へのトラックバック一覧です: ロック待ち処理の情報取得をしてみる:

« で、fillfactor、autovacuumパラメータの設定方法 | トップページ | postgresql.confの罠 »