数据库 postgresql 监控 IO 脚本

#!/bin/bash
#########################################################
#查看当前1分钟负载大于50，自动kill 超10秒长查询
########################################################
#使用awk只输出文档行数（截取第一段）
n=`uptime | awk -F 'load average:' '{print $2}'|awk -F '.' '{print $1}'`
RECORD_TIME=`date '+%Y-%m-%d__%H-%M-%S'`

#if [[ ${n} -gt 60 ]]
if [[ ${n} -gt 60 ]]
then
echo '一分钟负载大于等于60！！'

echo "========================== $RECORD_TIME        $n     killed =============================" >> /opt/wallog/load_monitor65.log

iotop -oP -b  -n 2 >> /opt/wallog/load_monitor65.log

/usr/bin/psql  -Upostgres -p5432  -h127.0.0.1  -d  postgres  -c "select client_addr,wait_event_type,query ,query , now() - xact_start as duration ,backend_start,state,query_start,datname From pg_stat_activity where query_start<=now()- interval'9 sec' and state not in ('idle')  and pid<>pg_backend_pid()  order by by duration DESC; " >> /opt/wallog/load_monitor65.log

psql -U postgres -h 127.0.0.1 -d postgres -c " select pg_terminate_backend(pid) From pg_stat_activity where query_start<=now()- interval'10 sec' and state not in ('idle') and pid<>pg_backend_pid() and query ilike 'select%'  and usename<>'repuser'; "

echo "######################## vmstat  ####################" >> /opt/wallog/load_monitor65.log
vmstat  >> /opt/wallog/load_monitor65.log

echo "######################## iostat     -mtx 2 2  ####################" >> /opt/wallog/load_monitor65.log
iostat     -mtx 2 2  >> /opt/wallog/load_monitor65.log
echo "######################## end  ####################" >> /opt/wallog/load_monitor65.log
else
echo '一分钟负载小于60！！'
fi