--查看当前活动的客户端连接数 SELECT count(*) FROM pg_stat_activity WHERE NOT pID=pg_backend_pID(); --查询客户端连接的情况 SELECT pID,case when waiting='f' then 'already get lock,sql executing' when waiting='t' then 'waiting get lock,sql waiting execute' end lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_queryFROM pg_stat_activity WHERE NOT pID=pg_backend_pID() and state<>'IDle' and application_name<>'pg_statsinfod' order by runtime desc --查看持有锁和等待锁的一些信息:--reltype=0代表其为索引SELECT locker.pID,pc.relname,locker.mode,locker_act.application_name,least(query_start,xact_start) start_time,locker_act.state,CASE WHEN granted='f' THEN 'wait_lock' WHEN granted='t' THEN 'get_lock' END lock_satus,locker_act.queryFROM pg_locks locker,pg_stat_activity locker_act,pg_class pcWHERE locker.pID=locker_act.pID AND NOT locker.pID=pg_backend_pID() AND application_name<>'pg_statsinfod' AND locker.relation = pc.oID AND pc.reltype<>0 --and pc.relname='t'ORDER BY runtime desc;--查询系统中正在执行的或者等待执行的事务--注意其只是代表事务信息,系统中也有可能存在慢的查询select pc.relname lock_table,pc.oID,tans.pID,CASE WHEN waiting='f' THEN 'already get lock,sql executing' WHEN waiting='t' THEN 'waiting get lock,sql waiting execute' END lock_satus,xact_start) query_start,psa.queryfrom pg_locks tans,pg_locks pl,pg_class pc,pg_stat_activity psawhere tans.transactionID is NOT null and pc.oID=pl.relation and tans.pID=pl.pIDand tans.pID=psa.pID and pc.reltype<>0order by runtime desc; --查看系统中正在执行的sql与lock_table有关的信息SELECT locktype,pg_locks.pID,virtualtransaction,transactionID,nspname,relname,mode,granted,CASE WHEN granted='f' THEN 'get_lock' WHEN granted='t' THEN 'wait_lock' END lock_satus,cast(date_trunc('second',query_start) AS timestamp) AS query_start,25) AS queryFROM pg_locks left OUTERJOIN pg_class ON (pg_locks.relation = pg_class.oID) left OUTERJOIN pg_namespace ON (pg_namespace.oID = pg_class.relnamespace),pg_stat_activityWHERE NOT pg_locks.pID=pg_backend_pID() AND pg_locks.pID=pg_stat_activity.pID AND pg_class.relname='t' --此处进行替换ORDER BY query_start;--查看Postgresql正在执行的sql 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,pg_stat_activity paWHERE current_query <> '<IDLE>' and procpID<> pg_backend_pID() and pa.pID=s.procpID and pa.state<>'IDle'ORDER BY lap DESC; procpID:进程IDstart:进程开始时间lap:经过时间current_query:执行中的sql怎样停止正在执行的sqlSELECT pg_cancel_backend(进程ID);或者用系统函数kill -9 进程ID;--查找是否有waitingps -ef|grep postgres | grep wait--查看当前库表和索引的的大小并排序显示前20条SELECTnspname,relkind as "type",pg_size_pretty(pg_table_size(C.oID)) AS size,pg_size_pretty(pg_indexes_size(C.oID)) AS IDxsize,pg_size_pretty(pg_total_relation_size(C.oID)) as "total"FROM pg_class Cleft JOIN pg_namespace N ON (N.oID = C.relnamespace)WHERE nspname NOT IN ('pg_catalog','information_schema') ANDnspname !~ '^pg_toast' ANDrelkind IN ('r','i')ORDER BY pg_total_relation_size(C.oID) DESCliMIT 20;总结
以上是内存溢出为你收集整理的postgresql 查看系统中的锁信息与锁资源全部内容,希望文章能够帮你解决postgresql 查看系统中的锁信息与锁资源所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)