正文
查看postgre都有哪些语句占用CPU
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
查看占用CPU最多的几个postgresql
ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2, $3}'
查看所有postgre语句执行时间：
select procpid, start, now() - start as lap, current_query
from
(select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query
from
(select pg_stat_get_backend_idset() as backendid) as s
) as s
where
current_query <> '<IDLE>' and procpid IN （17637,123,321） --加入查找到的进程ID
order by
lap desc;
- procpid：进程id
- start：进程开始时间
- lap：经过时间
- current_query：执行中的sql
怎样停止正在执行的sql？
kill有三种方式，第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)
第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作
最后一种是不推荐的：kill -9 进程id;
下面是总结的一个可以查看所有占用CPU最多的几个语句的脚本：
ps aux | grep postgres | sort -n -r -k 3 | head -10 | awk '{print $2}' > /tmp/top.tmp
WHERE="1"
TMPFILE="/tmp/top.tmp"
cat $TMPFILE | while read line
do
WHERE="$WHERE , $line"
echo $WHERE > $TMPFILE
done
WHERE=`cat $TMPFILE`
psql -h 服务器地址 -U pdata -s *** -W 密码 -c"select procpid, start, now() - start as lap, current_query from (select backendid, pg_stat_get_backend_pid(s.backendid) as procpid,
pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query from (select pg_stat_get_backend_idset() as backendid) as s ) as s where current_query <> '<IDLE>' and (procpid in ($WH
ERE))order by lap desc;"
