9.2.?资源监控

  1. 查看数据库大小:

    SELECT ux_size_pretty(ux_database_size('dbname'));
  2. 查看表大小:

    SELECT ux_size_pretty(ux_table_size('tablename'));
  3. 查看当前活动的客户端连接数:

     SELECT count(*) FROM ux_stat_activity WHERE NOT pid=ux_backend_pid(); 
  4. 查看活动的用户:

    SELECT datname,usename,client_addr,state FROM ux_stat_activity WHERE client_addr IS NOT NULL; 
  5. 查询客户端连接的情况:

    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;
  6. 查看持有锁和等待锁的一些信息,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;
  7. 查询系统中正在执行的或者等待执行的事务:

    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;
  8. 查看系统中正在执行的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;
  9. 查看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。

  10. 查看当前库表和索引的的大小并排序显示前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;
  11. 查找是否有waiting:

    ps -ef|grep uxdb | grep wait
XML 地图 | Sitemap 地图