Postgresql常用查看锁语句
可以通过以下SQL查询出所有的catalog view: SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type", r.rolname as "Owner" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v','') AND n.nspname = 'pg_catalog' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; 查看当前活动的客户端连接数: SELECT count(*) FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid(); 查询客户端连接的情况: SELECT procpid,waiting,current_timestamp - least(query_start,xact_start) AS runtime,substr(current_query,1,25) AS current_query FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid(); 查看当前锁的一些信息: SELECT locktype, virtualtransaction, transactionid, nspname, relname, mode, granted, cast(date_trunc('second',query_start) AS timestamp) AS query_start, substr(current_query,25) AS query FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity WHERE NOT pg_locks.pid=pg_backend_pid() AND pg_locks.pid=pg_stat_activity.procpid; 查看持有锁的一些信息: SELECT locked.pid AS locked_pid, locker.pid AS locker_pid, locked_act.usename AS locked_user, locker_act.usename AS locker_user, locked.virtualtransaction, locked.transactionid, locked.locktype FROM pg_locks locked, pg_locks locker, pg_stat_activity locked_act, pg_stat_activity locker_act WHERE locker.granted=true AND locked.granted=false AND locked.pid=locked_act.procpid AND locker.pid=locker_act.procpid AND (locked.virtualtransaction=locker.virtualtransaction OR locked.transactionid=locker.transactionid); 还是查看锁的一些信息: SELECT locked.pid AS locked_pid, relname FROM pg_locks locked LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid), pg_stat_activity locker_act WHERE locker.granted=true AND locked.granted=false AND locked.pid=locked_act.procpid AND locker.pid=locker_act.procpid AND locked.relation=locker.relation; 查看当前库表和索引的的大小并排序显示前20条: SELECT nspname, pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog','information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20; pg_total_relation_size = pg_table_size + pg_indexes_size pg_table_size = pg_relation_size + toast table + toast indexes + FSM 9.0以后: SELECT nspname, 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 C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog','information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r','i') ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 20; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |