AnalyticDB:Use resource queues to perform workload 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 workloads reach a threshold, all queries compete for the resources. This reduces the overall query performance and affects latency-sensitive business.

AnalyticDB for PostgreSQL provides resource queues for you to manage system workloads. 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 allocate resources that meet expectations for each query and deliver expected query performance.

Required permissions

To create and use resource queues, you must use an initial account or a privileged account that has the RDS_SUPERUSER permission.

Create a resource queue

Syntax:

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' ]

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. When you execute queries, the memory available for the MEMORY_LIMIT or ACTIVE_STATEMENTS parameter can be consumed on compute nodes. In this case, each query in the myqueue 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 the "Modify the configuration of a resource queue" section of this topic.

  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 running status of the system and the priority of the resource queue to which the query belongs.

  For example, when AnalyticDB for PostgreSQL is executing queries in a resource queue with a lower priority, 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 query the status and limits of the resource queue from the pg_resqueue table.

SELECT * FROM pg_resqueue WHERE rsqname='adhoc';

You cannot create a resource queue within a transaction block. Example:

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

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;

Common errors and troubleshooting

ALTER ROLE role_name WITH RDS_SUPERUSER;