Managing Resource Queues
rq commands to show, create, remove, and modify resource queues, and to manage the roles that are assigned to resource queues.
queue-settings argument can contain the following properties:
||Integer||Limits the number of queries that can be executed by roles assigned to the resource queue. Either the
||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
||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.|
||Boolean||If a resource queue is limited based on
||Enumeration||Sets the relative priority of queries executed by roles assigned to the resource queue. The allowed values, in order of increasing priority, are
||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.
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—
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
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.
rq modify command to alter queue settings. You can add new settings or update existing settings by specifying properties in the
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
rq show all command to display resource queues. This example displays settings for the
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
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
rq userdel command deletes a role from a resource queue. The user will be associated with the default queue,
rq userdel user from queue-name