Pivotal Knowledge Base

Follow

How to check the resource queue status in HAWQ 2.0

Environment 

HAWQ: 2.0

OS: RHEL 6.x

Overview

In HAWQ 2.0, we don't have the gp_toolkit.gp_locks_on_resqueue or hawq_toolkit.gp_locks_on_resqueue, which make us unable to know which process is blocking the other processes to run under the same resource queue. 

Symptom

When we query the gp_toolkit, we will get following error:

prodhd=# select * from gp_toolkit.gp_locks_on_resqueue; 
ERROR: schema "gp_toolkit" does not exist

Also, we cannot use the pg_locks to track that as the locktype column won't show up as waiting on a resource queue. See below:

prodhd=# SELECT * FROM pg_locks; 
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted | mppsessionid | 
mppiswriter | gp_segment_id 
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+--------+-----------------+---------+--------------+ 
-------------+--------------- 
transactionid | | | | | 1082498 | | | | 1082498 | 450674 | ExclusiveLock | t | 3539 | 
f | -10000 
transactionid | | | | | 1082552 | | | | 1082552 | 453959 | ExclusiveLock | t | 3551 | 
f | -10000 
relation | 16508 | 33170 | | | | | | | 1082497 | 450489 | AccessShareLock | t | 3538 | 
f | -10000 
transactionid | | | | | 1082534 | | | | 1082534 | 454413 | ExclusiveLock | t | 3554 | 
f | -10000 
relation | 16508 | 33170 | | | | | | | 1082496 | 450419 | AccessShareLock | t | 3537 | 
f | -10000 
relation | 16508 | 33077 | | | | | | | 1082534 | 454413 | AccessShareLock | t | 3554 | 
f | -10000 
transactionid | | | | | 1082485 | | | | 1082485 | 450041 | ExclusiveLock | t | 3536 | 
f | -10000 
relation | 16508 | 10335 | | | | | | | 1082552 | 453959 | AccessShareLock | t | 3551 | 
f | -10000 
transactionid | | | | | 1082497 | | | | 1082497 | 450489 | ExclusiveLock | t | 3538 | 
f | -10000 
transactionid | | | | | 1082496 | | | | 1082496 | 450419 | ExclusiveLock | t | 3537 | 
f | -10000 
relation | 16508 | 33170 | | | | | | | 1082498 | 450674 | AccessShareLock | t | 3539 | 
f | -10000 
relation | 16508 | 33170 | | | | | | | 1082485 | 450041 | AccessShareLock | t | 3536 | 

Resolution

To know which query is blocking the other queries under the same resource queue, we need to use the following query:

gpadmin=# SELECT c.waiting_resource, c.current_query ,a.rolname, b.rsqname from pg_roles a, pg_resqueue b ,pg_stat_activity c where a.rolresqueue=b.oid and a.rolname=c.usename;
 waiting_resource |                                                                             current_query                                                                              |  rolname   |    rsqname
------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------
 f                | SELECT c.waiting_resource, c.current_query ,a.rolname, b.rsqname from pg_roles a, pg_resqueue b ,pg_stat_activity c where a.rolresqueue=b.oid and a.rolname=c.usename; | gpadmin    | pg_default
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 f                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high  <===== Query 5 
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
 t                | SELECT count(*) from test2 a, test2 b where a.c=b.c;                                                                                                                   | jason_high | reporting_high
(9 rows)

We can see from above that query 5 is blocking other queries, and the user who had kicked off the query 5 is jason_high.

Comments

Powered by Zendesk