Resource Queue information

Resource Queue information where LockType = "Resource Queue" is displayed below: 

Greenplum 5x + 6x:

SELECT 

   rsqname as "RQname",

   rsqcountlimit as "RQActivestmt-Limit",

   rsqcountvalue as "RQActivestmt-Current",

   rsqcostlimit as "RQCost-Limit",

   rsqcostvalue as "RQCost-Current",

   rsqmemorylimit::bigint as "RQMemory-Limit",

   rsqmemoryvalue::bigint "RQMemory-Current",

   rsqholders as "RQHolders",

   rsqwaiters as "RQWaiters"

FROM gp_toolkit.gp_resqueue_status;

Greenplum 6x:

SELECT

    l.locktype AS "Blocker locktype",

    d.datname AS "Database",

    l.relation::regclass  AS "Blocking Table",

    a.usename AS "Blocking user",

    l.pid AS "Blocker pid",

    l.mppsessionid AS "Blockers SessionID",

    l.mode AS "Blockers lockmode",

    now()-a.query_start AS "Blocked duration",

    substring(a.query from 1 for 40) AS "Blocker Query"

FROM

    pg_locks l,

    pg_stat_activity a,

    pg_database d

WHERE l.pid=a.pid

AND l.database=d.oid

AND l.granted = true

AND relation in ( select relation from pg_locks where granted='f')

ORDER BY 3;

Note: Check the orphan locking query below to identify if this blocker sessionID shown in the query is actually waiting due to an orphan process lock (under the waiters sessionID).

Waiter information

Waiter information is displayed below: 

Greenplum 5x:

SELECT

l.locktype AS "Waiters locktype",

d.datname AS "Database",

l.relation::regclass  AS "Waiting Table",

a.usename AS "Waiting user",

l.pid AS "Waiters pid",

l.mppsessionid AS "Waiters SessionID",

l.mode AS "Waiters lockmode",

now()-a.query_start AS "Waiting duration",

substring(a.current_query from 1 for 40) AS "Waiters Query"

FROM

pg_locks l,

pg_stat_activity a,

pg_database d

WHERE l.pid=a.procpid

AND l.database=d.oid

AND l.granted = 'f'

ORDER BY 3;

Greenplum 6x:

Orphan information

Orphan process lock information is displayed with this query:

Greenplum 5x + 6x:

SELECT 

w.relation::regclass AS "Table",

w.mode           AS "Waiters Mode",

w.pid            AS "Waiters PID",

w.mppsessionid   AS "Waiters SessionID",

b.mode           AS "Blockers Mode",

b.pid            AS "Blockers PID",

b.mppsessionid   AS "Blockers SessionID",

(select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and role='p') AS "Blocking Segment"

FROM pg_catalog.pg_locks AS w, pg_catalog.pg_locks AS b 

Where ((w."database" = b."database" AND w.relation  = b.relation)

OR w.transactionid = b.transactionid)

AND w.granted='f'

AND b.granted='t'

AND w.mppsessionid <> b.mppsessionid

AND w.mppsessionid in (SELECT l.mppsessionid FROM pg_locks l WHERE l.granted = true AND relation in ( select relation from pg_locks where granted='f'))

AND w.gp_segment_id = b.gp_segment_id

ORDER BY 1;

To check if the locking issue is caused by an orphan process, run the blocker query first and then run the query below. If the blocker sessionID from the above query is on the waiter section, then the blocker from the first query is actually waiting on a segment where it had not acquired a lock and hence is blocking the other session. If the blocker SessionID (from above) is on the blocker section in the below query, then the locks are not held by any orphan process.

Greenplum 6x:

SELECT

    l.locktype AS "Blocker locktype",

    l.relation::regclass  AS "Blocking Table",

    a.usename AS "Blocking user",

    l.pid AS "Blocker pid",

    l.mppsessionid AS "Blockers SessionID",

    l.mode AS "Blockers lockmode",

    now()-a.query_start AS "Blocked duration",

    substring(a.query from 1 for 40) AS "Blocker Query"

FROM

    pg_locks l,

    pg_locks w,

    pg_stat_activity a

WHERE l.pid=a.pid

AND l.transactionid=w.transactionid

AND l.granted = true

AND w.granted = false

AND l.transactionid is not NULL

ORDER BY 3;