查看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;"