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.
  • The memory size is measured in KB, MB, or GB.
  • The default value is -1, which indicates an unlimited memory size.
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.
  • If COST_OVERCOMMIT is set to TRUE, a query whose cost is greater than the value of MAX_COST is executed when the system load is low.
  • If COST_OVERCOMMIT is set to FALSE, a query whose cost is greater than the value of MAX_COST is rejected.
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:
  • MIN
  • LOW
  • MEDIUM
  • HIGH
  • MAX
Default value: MEDIUM.
Note When creating a resource queue, you must set either 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.
    For example, AnalyticDB for PostgreSQL is executing queries in a resource queue with a lower priority, and a query in a resource queue with a higher priority is admitted and is ready to execute. AnalyticDB for PostgreSQL allocates more CPU resources to the query from the resource queue with the higher priority and reduces the CPU resources for queries in the resource queue with the lower priority. The rules to allocate CPU resources are as follows:
    • 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
Note Not all SQL statements are restricted by resource queues.
  • 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.

Note
  • 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.

Note A user can only belong to one resource queue.

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;
Note You are not allowed to delete a resource queue that contains the following items:
  • Assigned users
  • Queries in the waiting state