<form id="fjn3d"></form>

<address id="fjn3d"></address>

            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
            91竞彩之家