This topic describes how to create and use resource queues in AnalyticDB for PostgreSQL. You can use resource queues to manage and isolate system resources.
Overview
CPU and memory resources for a database instance are limited. These resources have an impact on the query performance of a database. If database loads reach a threshold, all queries compete for the resources. This lowers overall query performance and affects latency-sensitive businesses.
AnalyticDB for PostgreSQL provides resource queues for you to manage system loads. You can specify the following items based on your business requirements: number of concurrent queries that your database can process, memory size available for each query, and number of CPU resources available for each query. This way, the system can offer resources that meet expectations for each query and achieve expected query performance.
Create a resource queue
Execute the following SQL statement to create a resource queue:
CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ])
The queue_attribute parameter specifies the attribute of a resource queue. Valid values:
ACTIVE_STATEMENTS=integer
[ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]
MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]
[ ACTIVE_STATEMENTS=integer ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]
Parameter | Description |
---|---|
ACTIVE_STATEMENTS | The maximum number of active queries in the resource queue at a certain point in time. An active query refers to a query that is being executed. |
MEMORY_LIMIT | The maximum memory size that can be used by all queries in the resource queue on a
single compute node.
|
MAX_COST | The maximum cost of a query in the resource queue. The default value is-1, which indicates
unlimited cost.
Note The cost refers to the cost estimated for a query by the query optimizer in AnalyticDB
for PostgreSQL.
|
COST_OVERCOMMIT | If the MAX_COST parameter is specified, this parameter must be set.
|
MIN_COST | The minimum cost of a query in the resource queue. A query whose cost is less than the value of MIN_COST is immediately executed without being queued. |
PRIORITY | The priority of the resource queue. Queries in a resource queue with a higher priority
are assigned more CPU resources for execution. Valid values:
|
ACTIVE_STATEMENTS
or MAX_COST
. If you do not do this, the creation fails.
postgres=> CREATE RESOURCE QUEUE adhoc2 WITH (MEMORY_LIMIT='2000MB');
ERROR: at least one threshold ("ACTIVE_STATEMENTS", "MAX_COST") must be specified
- Configure the maximum number of active queries
When you create a resource queue, execute the following statement to configure the maximum number of active queries in this resource queue:
CREATE RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=3);
In this example, a resource queue named
adhoc
is created, and a maximum of three active queries are allowed for this resource queue at a specific point in time. If there are already three active queries in this resource queue, new queries are queued for execution until the three queries are executed. - Configure an upper memory limit
When you create a resource queue, execute the following statement to configure the maximum memory size that can be used by all queries in the resource queue:
CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20, MEMORY_LIMIT='2000MB');
In this example, a resource queue named
myqueue
is created, a maximum of 20 active queries are allowed for the resource queue at a specific point in time, and all queries in this resource queue can use a maximum of 2,000 MB of memory. Therefore, each query in this resource queue can use a maximum of 100 MB of memory. If a query requires independent memory, you can use the statement_mem parameter to set the memory size for this query. The value of this parameter must be lower than the values of the MEMORY_LIMIT and max_statement_mem parameters. Example:SET statement_mem='1GB'; SELECT * FROM test_adbpg WHERE col='adb4pg' ORDER BY id; RESET statement_mem;
- Configure the priority of a resource queue
You can configure priorities for different resource queues to control the use of CPU resources by queries in the resource queues. For example, if AnalyticDB for PostgreSQL receives a large number of concurrent queries, it allocates more resources to queries in a resource queue with a higher priority than queries in a resource queue with a lower priority. This ensures that there are sufficient CPU resources to execute queries in the resource queue with a higher priority.
You can configure the priority of a resource queue when you create it. Example:
CREATE RESOURCE QUEUE executive WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX);
You can also modify the priority of a resource queue after you create it. For more information, see Modify the configuration of a resource queue.
Note The PRIORITY setting for a resource queue differs from the ACTIVE_STATEMENTS and MEMORY_LIMIT settings.- The values of the ACTIVE_STATEMENTS and MEMORY_LIMIT parameters determine whether a query is admitted to the queue and eventually executed.
- The PRIORITY setting ensures that after the system starts to execute a query, available CPU resources are dynamically allocated to the query based on the motion status of the system and the priority of the resource queue to which the query belongs.
- AnalyticDB for PostgreSQL allocates the same number of CPU resources to queries in resource queues with the same priority.
- If AnalyticDB for PostgreSQL receives queries in resource queues with high, medium, and low priorities, it allocates 90% of CPU resources to the queries in the high-priority resource queue. Among the remaining 10% of CPU resources, AnalyticDB for PostgreSQL allocates 90% of them to the queries in the medium-priority resource queue and 10% of them to the queries in the low-priority resource queue.
After you create a resource queue, you can execute the gp_toolkit.gp_resqueue_status
statement to view the status of the resource queue and the resource limits configured
for it.
postgres=> SELECT * from gp_toolkit.gp_resqueue_status WHERE
postgres-> rsqname='adhoc';
queueid | rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+---------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
19283 | adhoc | 3 | 0 | -1 | 0 | -1 | 0 | 0 | 0
(1 row)
You cannot create a resource queue within a transaction block. Example:
postgres=> begin;
BEGIN
postgres=> CREATE RESOURCE QUEUE test_q WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX);
ERROR: CREATE RESOURCE QUEUE cannot run inside a transaction block
- If the resource_select_only parameter is set to on, SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR statements are restricted by resource queues.
- If the resource_select_only parameter is set to off, INSERT, UPDATE, and DELETE statements are restricted by resource queues in addition to SELECT, SELECT INTO, CREATE TABLE AS SELECT, and DECLARE CURSOR statements.
- In AnalyticDB for PostgreSQL, the resource_select_only parameter is set to off by default.
Assign users to a resource queue
After you create a resource queue, you must assign one or more users to it. Then, AnalyticDB for PostgreSQL can manage resources for queries in the resource queue.
- If a user is not assigned to a resource queue, AnalyticDB for PostgreSQL assigns this user to the pg_default resource queue.
- This resource queue supports a maximum of 500 active queries and has no cost limits. Its priority is medium.
postgres=> SELECT * from gp_toolkit.gp_resqueue_status WHERE rsqname='pg_default';
queueid | rsqname | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
6055 | pg_default | 500 | 1 | -1 | 126 | -1 | 2.096128e+09 | 0 | 1
(1 row)
Execute either of the following statements to assign a user to a resource queue:
ALTER ROLE name RESOURCE QUEUE queue_name;
CREATE ROLE name WITH LOGIN RESOURCE QUEUE queue_name;
You can assign a resource queue to a user when the user is created. You can also change the resource queue assigned to a user after the user is created.
Remove a user from a resource queue
Execute the following statement to remove a user from a resource queue:
ALTER ROLE role_name RESOURCE QUEUE none;
Modify the configuration of a resource queue
You can use the following statements to modify the configuration of a resource queue.
- Modify the number of active queries:
ALTER RESOURCE QUEUE adhoc WITH (ACTIVE_STATEMENTS=5);
- Modify the maximum memory size that can be used by all queries and the maximum cost
of a query:
ALTER RESOURCE QUEUE adhoc WITH (MAX_COST=-1.0, MEMORY_LIMIT='2GB');
- Modify the priority:
ALTER RESOURCE QUEUE adhoc WITH (PRIORITY=LOW); ALTER RESOURCE QUEUE reporting WITH (PRIORITY=HIGH);
Delete a resource queue
Execute the following statement to delete a resource queue:
DROP RESOURCE QUEUE name;
- Assigned users
- Queries in the waiting state