How to Check Resource Queue Status?

Post date: Sep 23, 2012 9:35:11 PM

Q. How to Check Resource Queue Status?

Ans: Checking Resource Queue Status

Checking resource queue status involves the following tasks:

•Viewing Queued Statements and Resource Queue Status

•Viewing Resource Queue Statistics

•Viewing the Roles Assigned to a Resource Queue

•Viewing the Waiting Queries for a Resource Queue

•Clearing a Waiting Statement From a Resource Queue

•Viewing the Priority of Active Statements

•Resetting the Priority of an Active Statement

Viewing Queued Statements and Resource Queue Status

The gp_toolkit.gp_resqueue_status view allows administrators to see status and activity for a workload management resource queue. It shows how many queries are waiting to run and how many queries are currently active in the system from a particular resource queue. To see the resource queues created in the system, their limit attributes, and their current status:

=# SELECT * FROM gp_toolkit.gp_resqueue_status;

Viewing Resource Queue Statistics

If you want to track statistics and performance of resource queues over time, you can enable statistics collecting for resource queues. This is done by setting the following server configuration parameter in your master postgresql.conf file:

stats_queue_level = on

Once this is enabled, you can use the pg_stat_resqueues system view to see the statistics collected on resource queue usage. Note that enabling this feature does incur slight performance overhead, as each query submitted through a resource queue must be tracked. It may be useful to enable statistics collecting on resource queues for initial diagnostics and administrative planning, and then disable the feature for continued use.

See the section on the Statistics Collector in the PostgreSQL documentation for more information about collecting statistics in Greenplum Database.

Viewing the Roles Assigned to a Resource Queue

To see the roles assigned to a resource queue, perform the following query of the pg_roles and gp_toolkit.gp_resqueue_status system catalog tables:

=# SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status

WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

You may want to create a view of this query to simplify future inquiries. For example:

=# CREATE VIEW role2queue AS

SELECT rolname, rsqname FROM pg_roles, pg_resqueue

WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

Then you can just query the view:

=# SELECT * FROM role2queue;

Viewing the Waiting Queries for a Resource Queue

When a slot is in use for a resource queue, it is recorded in the pg_locks system catalog table. This is where you can see all of the currently active and waiting queries for all resource queues. To check that statements are being queued (even statements that are not waiting), you can also use the gp_toolkit.gp_locks_on_resqueue view. For example:

=# SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

If this query returns no results, then that means there are currently no statements waiting in a resource queue.

Clearing a Waiting Statement From a Resource Queue

In some cases, you may want to clear a waiting statement from a resource queue. For example, you may want to remove a query that is waiting in the queue but has not been executed yet. You may also want to stop a query that has been started if it is taking too long to execute, or if it is sitting idle in a transaction and taking up resource queue slots that are needed by other users. To do this, you must first identify the statement you want to clear, determine its process id (pid), and then, use pg_cancel_backend with the process id to end that process, as shown below.

For example, to see process information about all statements currently active or waiting in all resource queues, run the following query:

=# SELECT rolname, rsqname, pid, granted,current_query, datname

FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity

WHERE pg_roles.rolresqueue=pg_locks.objid

AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid

AND pg_stat_activity.procpid=pg_locks.pid;

If this query returns no results, then that means there are currently no statements in a resource queue. A sample of a resource queue with two statements in it looks something like this:

-----------------------------------------------------------------------

Use this output to identify the process id (pid) of the statement you want to clear from the resource queue. To clear the statement, you would then open a terminal window (as the gpadmin database superuser or as root) on the master host and cancel the corresponding process. For example:

=# pg_cancel_backend(31905)

Note: Do not use any operating system KILL command.

Viewing the Priority of Active Statements

The gp_toolkit administrative schema has a view called gp_resq_priority_statement, which lists all statements currently being executed and provides the priority, session ID, and other information.

This view is only available through the gp_toolkit administrative schema. See Appendix I, “The gp_toolkit Administrative Schema” for more information.

Resetting the Priority of an Active Statement

Superusers can adjust the priority of a statement currently being executed using the built-in function gp_adjust_priority(session_id, statement_count, priority). Using this function, superusers can raise or lower the priority of any query. For example:

=# SELECT gp_adjust_priority(752, 24905, 'HIGH')

To obtain the session ID and statement count parameters required by this function, Superusers can use the gp_toolkit administrative schema view, gp_resq_priority_statement. This function affects only the specified statement . Subsequent statements in the same resource queue are executed using the queue’s normally assigned priority.