Pivotal Knowledge Base

フォローする

スクリプト – リソースキューの情報

環境

  • Pivotal Greenplum
  • このスクリプトをPivotal HDB 上で実行すると、パニックの原因になるので、Pivotal HDB 上では実行しないこと

目的

管理者がリソースキューレベルの情報を取得する簡単なスクリプトを提供する。

注意: テスト環境で本スクリプトを検証した上で、本番環境で実行すること。

解決策

リソースキュー情報と属性

select a.rsqname as "RQname", 
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='active_statements') as "ActiveStatment",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='max_cost') as "MaxCost",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='min_cost') as "MinCost",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='cost_overcommit') as "CostOvercommit",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='memory_limit') as "MemoryLimit",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='priority') as "Priority",
	(select count(*) from pg_resqueue x,pg_roles y 
	 where x.oid=y.rolresqueue and a.rsqname=x.rsqname) as "RQAssignedUsers"
from ( select distinct rsqname from pg_resqueue_attributes ) a
order by 1;

ユーザ名と適用されているリソースキュー一覧

select  
    rrrsqname as "RQueueName",
    rrrolname as "Username"
from 
    gp_toolkit.gp_resq_role
order by 1,2;

リソースキューの稼働状況

-- 現在のリソースキューの稼働状況

select 
    resqname as "RQueueName",
    resqlast as "RQueueLastRequest",
    resqstatus as "RQueueStatus",
    resqtotal as "TotalRQUsers"
from
    gp_toolkit.gp_resq_activity_by_queue;

-- 特定ユーザに関する現在のリソーキューの稼働状況

select 
    resqprocpid as "Pid",
    sess_id as "SessionID",
    resqrole as "Username",
    resqname as "RQueueName",
    resqstart as "RQueueStartTime",
    now() - resqstart as " WaitDuration",
    resqstatus as "RQueueStatus",
    substring(current_query from 1 for 40) as "Query"
from
    gp_toolkit.gp_resq_activity a,pg_stat_activity b
where resqrole='<Username>' and a.resqprocpid=b.procpid ;

-- 現在の稼働状況に関するリスースキューの使用状況

select 
   rsqname as "RQname",
   rsqcountlimit as "RQActiveLimit",
   rsqcountvalue as "RQCurrentActive",
   rsqcostlimit::bigint as "RQCostLimit",
   rsqcostvalue::bigint as "RQCurrentCost",
   rsqmemorylimit::bigint as "RQMemoryLimit",
   rsqmemoryvalue::bigint "RQCurrentMemory",
   rsqholders as "RQHolders",
   rsqwaiters as "RQWaiters"
from gp_toolkit.gp_resqueue_status;

リソースキューの優先度一覧

select 
   rqpdatname as "DBname",
   rqpusename as "Username",
   rqpsession as "SessionID",
   rqpcommand as "CommandExec",
   rqppriority as "RQPriority",
   substring(rqpquery from 1 for 40) as "Query"
from gp_toolkit.gp_resq_priority_statement ;

コメント

Powered by Zendesk