查看postgre都有哪些语句占用CPU,以及对应的sql语句
2023-09-27 14:28:05 时间
查看占用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;"
相关文章
- 一条垃圾SQL,把 64 核 CPU 快跑崩了!
- oracle-sql脚本
- mysql merge update_SQL中批量更新 merge into用法
- sql CAST用法
- 【MySQL缓存】怎么验证当前SQL是否走Buffer Pool缓存?
- 使用SQL-Server分区表功能提高数据库的读写性能
- 数据库(5)SQL约束
- database.sql
- Stellar Repair for MS SQL v9.0.0.5 + CRACK
- Python中Pandas通过read_sql方法从Mysql或Oracle数据库中读取数据帧(DataFrame)
- sql - 获取日期中的年
- dedecms /include/uploadsafe.inc.php SQL Injection Via Local Variable Overriding Vul