数据库负载查看

top -c -u postgres
select * from pg_stat_activity;

select now(),pg_stat_statements_reset(); -- 记录一下时间

-- 按总时间排序。
select calls, total_time/calls AS one_call_time, total_time ,rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent, query 
from pg_stat_statements order by total_time DESC limit 10;

-- 按单次平均时间排序。
select calls, total_time/calls AS one_call_time, total_time ,rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent , query 
from pg_stat_statements order by total_time/calls DESC limit 10;

-- 查询语句的使用频率
select calls, total_time/calls AS one_call_time, total_time ,rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent , query 
from pg_stat_statements order by calls DESC limit 10;

如果pg的版本是 8.4及以上的，可以很简单地用下面的语句来杀死所有IDEL进程 
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query='<IDLE>';
pg_terminate_backend 是pg的内部方法，另外还有一个叫pg_cancel_backend，这个方法在8.4以前的版本中就一直存在。这两个方法的区别在于，pg_cancel_backend 只是取消当前某一个进程的查询操作，但不能释放数据库连接。但pg_terminate_backend 可以在pg的后台杀死这个进程，从而释放出宝贵的连接资源。
如果pg的版本是在8.3或者以下的
pg_ctl kill TERM PID

找到CPU占用高的PID
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=25400  ORDER BY lap DESC;