AnalyticDB for PostgreSQL:Use resource queues for load management

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 business.

AnalyticDB for PostgreSQL provides resource queues for you to manage system loads. You can specify the following items based on your business requirements: the number of concurrent queries that your database can process, the memory size available for each query, and the amount of CPU resources available for each query. This way, the system can offer resources that meet expectations for each query and deliver expected query performance.

Create a resource queue

Execute the following 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' ]

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 the 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 the resource queue at a specific point in time. If three active queries exist in the 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 the resource queue can use a maximum of 2,000 MB of memory. In this case, each query in the 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 less 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 the resource queue with a higher priority has sufficient CPU resources to execute queries.

  When you create a resource queue, you can configure a priority for the resource queue. Example:

  CREATE RESOURCE QUEUE executive WITH (ACTIVE_STATEMENTS=3, PRIORITY=MAX);

  You can also modify the priority of a resource queue after you create the resource queue. 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 following rules for allocating CPU resources apply:

  • AnalyticDB for PostgreSQL allocates the same amount 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 and limits of the resource queue. Example:

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

Assign users to a resource queue

After you create a resource queue, you must assign one or more users to the resource queue. Then, AnalyticDB for PostgreSQL can manage resources for queries of users in the resource queue.

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 one 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;

When a user is created, you can assign a resource queue to the user. 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;