查看数据库大小:
SELECT ux_size_pretty(ux_database_size('dbname'));
查看表大小:
SELECT ux_size_pretty(ux_table_size('tablename'));
查看当前活动的客户端连接数:
SELECT count(*) FROM ux_stat_activity WHERE NOT pid=ux_backend_pid();
查看活动的用户:
SELECT datname,usename,client_addr,state FROM ux_stat_activity WHERE client_addr IS NOT NULL;
查询客户端连接的情况:
SELECT pid,case when wait_event is not NULL then 'already get lock,sql executing' when wait_event is NULL 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_query FROM ux_stat_activity WHERE NOT pid=ux_backend_pid() and state<>'idle' and application_name<>'ux_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,current_timestamp - least(query_start,xact_start) AS runtime,locker_act.query FROM ux_locks locker,ux_stat_activity locker_act, ux_class pc WHERE locker.pid=locker_act.pid AND NOT locker.pid=ux_backend_pid() AND application_name<>'ux_statsinfod' AND locker.relation = pc.oid AND pc.reltype<>0 ORDER BY runtime desc;
查询系统中正在执行的或者等待执行的事务:
select pc.relname lock_table,pc.oid,tans.pid, CASE WHEN wait_event is not NULL THEN 'already get lock,sql executing' WHEN wait_event is NULL THEN 'waiting get lock,sql waiting execute' END lock_satus, least(query_start,xact_start) query_start, current_timestamp - least(query_start,xact_start) AS runtime, psa.query from ux_locks tans,ux_locks pl,ux_class pc ,ux_stat_activity psa where tans.transactionid is NOT null and pc.oid=pl.relation and tans.pid=pl.pid and tans.pid=psa.pid and pc.reltype<>0 order by runtime desc;
查看系统中正在执行的sql与lock_table有关的信息:
SELECT locktype, ux_locks.pid, virtualtransaction, transactionid, nspname, relname, mode, granted, CASE WHEN granted='f' THEN 'get_lock' WHEN granted='t' THEN 'wait_lock' END lock_satus, CASE WHEN wait_event is not NULL THEN 'already get lock,sql executing' WHEN wait_event is NULL THEN 'waiting get lock,sql waiting execute' END lock_satus, current_timestamp - least(query_start,xact_start) AS runtime, cast(date_trunc('second',query_start) AS timestamp) AS query_start, substr(query,1,25) AS query FROM ux_locks LEFT OUTER JOIN ux_class ON (ux_locks.relation = ux_class.oid) LEFT OUTER JOIN ux_namespace ON (ux_namespace.oid = ux_class.relnamespace), ux_stat_activity WHERE NOT ux_locks.pid=ux_backend_pid() AND ux_locks.pid=ux_stat_activity.pid AND ux_class.relname='t' ORDER BY query_start;
查看UXDB正在执行的SQL:
SELECT procpid, start, now() - start AS lap, current_query FROM (SELECT backendid, ux_stat_get_backend_pid(S.backendid) AS procpid, ux_stat_get_backend_activity_start(S.backendid) AS start, ux_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT ux_stat_get_backend_idset() AS backendid) AS S ) AS S ,ux_stat_activity pa WHERE current_query <> '<IDLE>' and procpid<> ux_backend_pid() and pa.pid=s.procpid and pa.state<>'idle' ORDER BY lap DESC;
procpid:进程id。
start:进程开始时间。
lap:经过时间。
current_query:执行中的sql。
停止正在执行的sql:SELECT ux_cancel_backend(进程id);或者用系统函数 kill -9 进程id。
查看当前库表和索引的的大小并排序显示前20条:
SELECT nspname, relname, relkind as "type", ux_size_pretty(ux_table_size(C.oid)) AS size, ux_size_pretty(ux_indexes_size(C.oid)) AS idxsize, ux_size_pretty(ux_total_relation_size(C.oid)) as "total" FROM ux_class C LEFT JOIN ux_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('ux_catalog', 'information_schema') AND nspname !~ '^ux_toast' AND relkind IN ('r','i') ORDER BY ux_total_relation_size(C.oid) DESC LIMIT 20;
查找是否有waiting:
ps -ef|grep uxdb | grep wait