アプリケーションの作り方や色々なタイミングが重なるとロックして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）監修のもと掲載しています。
元記事は、こちら
