Managing Resource Queues

Use rq commands to show, create, remove, and modify resource queues, and to manage the roles that are assigned to resource queues.

The queue-settings argument can contain the following properties:

Property Name Type Description
active_statements Integer Limits the number of queries that can be executed by roles assigned to the resource queue. Either the active_statements or max_cost property must be set on each resource queue.
max_cost Float Sets a maximum limit on the total cost of queries that can be executed by roles assigned to the resource queue. The cost of a query is estimated by the query planner and is measured in units of disk page fetches. Either the active_statements or max_cost property must be set on each resource queue.
min_cost Float Sets the minimum estimated query cost for a query to be managed by the resource queue. Queries with estimated costs below this threshold are executed immediately.
cost_overcommit Boolean If a resource queue is limited based on MAX_COST, a query that exceeds the MAX_COST limit is allowed to execute if the system is idle and COST_OVERCOMMIT is true. If COST_OVERCOMMIT is set to false, queries that exceed MAX_COST are always rejected.
priority Enumeration Sets the relative priority of queries executed by roles assigned to the resource queue. The allowed values, in order of increasing priority, are MIN, LOW, MEDIUM, HIGH, and MAX.
memory_limit Integer (kilobytes) Sets the total amount of memory that all active statements submitted to the queue may consume. The minimum is 10240KB. There is no maximum, but when a query executes it is limited by the segment host’s physical memory. Set the parameter to -1 for no limit.

: Table 1. Resource Queue Properties

Specify the resource queue properties in a parameter-name=value format, for example:

mdw/gpdb-cluster > rq modify myrq with active_statements=10

Separate multiple queue settings with a comma. The queue-settings argument must not contain spaces. This example sets three properties:

mdw/gpdb-cluster> rq add ETL with 
active_statements=3,priority=LOW,memory_limit=524288

A Greenplum Database role (login user) is associated with a single resource queue. Newly created roles are added to the pg_default queue if another resource queue is not specified.

Queries submitted by users associated with a resource queue are managed by the queue. The queue’s settings determine whether queries are accepted or rejected, if they run immediately or wait for resources to be returned to the queue, how much memory to allocate to the query, and the relative amount of CPU the query will have.

Resource queues share the memory allocated to each segment. Adding a new resource queue or altering a queue’s settings may require adjusting other resource queues to avoid over-allocating the available memory and causing queries to fail. See the Workload Management section in the Greenplum Database Administrator Guide for guidelines on configuring resource queues.

Adding a New Resource Queue

The rq add command creates a new resource queue. The command has the following syntax:

rq add queue-name with queue-settings

You must set one or both of the threshold properties—active_statements or max_cost—when you create a new resource queue.

The following example creates an ETL queue that can run three concurrent queries at low CPU priority relative to other queries.

mdw/gpdb-cluster> rq add etl with active_statements=3,priority=low

Deleting a Resource Queue

Delete an existing resource queue by name using the rq delete command:

rq delete queue-name

It is not possible to delete a queue that has roles assigned to it.

Modifying a Resource Queue

Use the rq modify command to alter queue settings. You can add new settings or update existing settings by specifying properties in the queue-settings argument.

rq modify queue-name with queue-settings

The following example modifies the ETL queue to run two concurrent queries with a maximum of 524288KB (512MB) of memory. Each query will be allocated 256MB of memory.

rq modify etl with active_statements=2,memory_limit=524288

Displaying Resource Queues

Use the rq show all command to display resource queues. This example displays settings for the etl and pg_default resource queues.

mdw/gpdb-cluster> rq show all
rsqname           resname                  ressetting        restypeid
etl               active_statements        2                 1      
etl               max_cost                 -1                2      
etl               min_cost                 0                 3      
etl               cost_overcommit          1                 4      
etl               priority                 low               5      
etl               memory_limit             524288            6      
pg_default        active_statements        10                1      
pg_default        max_cost                 -1                2      
pg_default        min_cost                 0                 3      
pg_default        cost_overcommit          0                 4      
pg_default        priority                 medium            5      
pg_default        memory_limit             -1                6

Adding Users to Resource Queues

The rq useradd command adds a user to a resource queue. The user is removed from their previous resource queue as users are associated with only one resource queue. The user’s subsequent queries are managed by the new resource queue.

rq useradd user to queue-name

Deleting a User from a Resource Queue

The rq userdel command deletes a role from a resource queue. The user will be associated with the default queue, pg_default.

rq userdel user from queue-name