Postgres waitingがたまっているかをチェック

アプリケーションの作り方や色々なタイミングが重なるとロックしてwaitingが溜まってしまうことがあり、これによりDBがボトルネックになってしまい、アプリが落ちたり、重くなってしまう時があります。
（しっかりと構築していても、どうしようもない時に起こることがあります。）

そこで、waitingが溜まってしまった時に、アラートを出すようなチェックシェルを作ってみました。

○show_locks.sql
ロックして待ち状態になっているSQLをリスト

select * from (select pid from pg_locks group by pid) l,pg_stat_activity a
where l.pid=a.procpid order by query_start

○lockcheck.sh　データベースはhogeとし、mailコマンドが使えることが前提

#!/bin/sh
unalias -a
cd `dirname $0`

##対象のポスグレ(psql)が使えるようにexportとか指定しているファイルはあくまで独自のものです
. /etc/pgenv

##日付取得と
NOW=`date '+%Y/%m/%d %H:%M:%S'`

#waitingの数カウントがなければ終了
WAIT_CNT=`ps -ef | fgrep 'postgres:' | fgrep 'waiting' | wc -l`
[ `expr ${WAIT_CNT} > 1` = 0 ] && exit 0

#hogeデータベースで現在動いているSQLの一覧をテキストに吐き出す
cat show_locks.sql | psql --no-psqlrc -U hoge > lockview.txt

#指定した宛先にメール送信
SEND_TO='alert@hogehoge.co.jp'
SUBJECT="PostgreSQL lock waitting"

cat DATE: ${NOW}
SERVER: hoge-server
INFO:
* Postgres Process waiting count ${WAIT_CNT}
LOCKS:
`cat lockview.txt`
EOF
exit 0

あとはcronで、適当な間隔にこのシェルを設定する形になります。

lockview.txtと同じ内容をメールで見て、SQLやアプリを見直して改善するようにする。
（このメールが頻繁に送られるようだと良くない状態と考えられます。）
また、フルバキュームやインデックスのリビルドの最中には、waitingが溜まる可能性があるので、このチェックは動かさない方がいいです。

こちらの記事はなかの人（kenjionsoku）監修のもと掲載しています。
元記事は、こちら