PolarDB:Using Resource Management

Enable the Resource Control feature

Create a resource control

CREATE polar_resource_control <rc_name> max_cpu <max_cpu_value>;

Parameters:

After a resource control is created, you can run the following SQL query from a high-privilege account to view all resource controls.

SELECT * FROM mysql.polar_resource_control;

You can run the following SQL query from a high-privilege account to retrieve the Thread Groups under a resource control.

SELECT id, resource_control_name FROM information_schema.thread_pool_status;

Update a resource control

ALTER polar_resource_control <rc_name> max_cpu <max_cpu_value>;

Description:

Delete a Resource Control

DROP polar_resource_control <rc_name>;

Description:

Set a resource control for a user or database

Attach

SET polar_resource_control <rc_name> FOR [database|USER] <db_name/user_name>;

The SQL statement is as follows:

Detach

RELEASE polar_resource_control <rc_name> FOR [database|USER] db_name/user_name>;

After this statement is executed, the specified database or user is no longer restricted by the CPU resource limits of the resource control. Statements that are already running remain subject to the limits. Statements that start after the resource control is removed are not subject to the limits.

Configure a resource control for a query or connection

Attach

SET polar_resource_control <rc_name> FOR [query|connection] <connection_id>;

Parameters:

Release

RELEASE polar_resource_control <rc_name> FOR [query|connection] <connection_id>;

Parameters:

Priority of resource controls

Resource controls are applied to a query statement in the following order of priority. The first match found is used.

1. A control explicitly attached to the query by its connection ID. This has the highest priority.

2. Determines whether the current connection is attached to resource control and applies resource limits accordingly.

3. The system checks if a connected user is attached to resource control. If so, resource limits are applied.

4. If the currently connected database is attached to resource control, the system applies resource limits.

5. If none of the preceding conditions are met, resource limits do not apply.

Using a multi-master cluster

A Multi-master Cluster works the same as a single-master cluster, and the syntax is fully compatible. When you execute a CREATE POLAR_RESOURCE_CONTROL, ALTER POLAR_RESOURCE_CONTROL, or DROP POLAR_RESOURCE_CONTROL statement, it is randomly forwarded to a read/write node in the cluster. The statement is then synchronized across all read/write nodes, which may cause some latency. To limit resources for a query or connection, you can use the HINT syntax to specify a node.

Scenarios

• Prevent malicious users from consuming excessive cluster resources.

• Handle sudden slow queries.

• Prevent malicious users from triggering automatic scaling in a Serverless cluster.

• Limit CPU usage in multi-tenant environments or when different services share a cluster.

Usage examples

• Prevent malicious users from consuming excessive cluster resources

  To handle malicious users, you can set CPU resource limits for them. You can apply these limits proactively or in response to malicious activity.

  CREATE polar_resource_control rc_for_trouble_user max_cpu 10;
  SET polar_resource_control rc_for_trouble_user FOR USER trouble_user;

  After you set a resource control for the user trouble_user, the user's threads can use a maximum of 10% of the total cluster CPU. This effectively prevents the malicious user from consuming excessive CPU resources and affecting other services.

• Handle sudden slow queries

  Unexpected slow queries can sometimes occur in a production environment. They might be caused by incorrect SQL parsing or optimization, which can increase the CPU load. This causes the query to run for a long time and consume significant CPU resources. Although you can terminate the query with the kill command, you must wait for the query to reach a specific point in its execution, which is time-consuming. In addition, rolling back the transaction during the kill process also consumes CPU resources and takes time. To quickly reduce the CPU load from a slow query or prevent the CPU from being fully utilized, you can apply a CPU usage limit to that query.

  CREATE polar_resource_control rc_for_slow_query max_cpu 10;
  SET polar_resource_control rc_for_slow_query FOR query query_id;

  Note

  When you set a resource control for a slow query, you can find its query_id by running SHOW PROCESSLIST.

  After you set a resource control for the slow query with the ID query_id, the query's maximum CPU usage is limited to 10% of the total cluster CPU. This quickly reduces the cluster's CPU load and prevents the query from impacting other production services.

  A PolarDB cluster can have multiple compute nodes, and different nodes might have queries with the same connection ID. Therefore, when you set a resource control for a query, use the HINT syntax to apply the setting to a specific node.

  /*force_node='pi-bpxxxxxxxx'*/ SET polar_resource_control rc_for_slow_query FOR query query_id;

• Prevent malicious users from triggering cluster scaling in a serverless cluster

  When a cluster is in Serverless mode, it automatically scales based on the workload to handle sudden high-load bursts. However, you may want to prevent the cluster from scaling in response to requests from malicious users. In this case, you can apply resource limits to these malicious users on the serverless cluster.

  CREATE polar_resource_control rc_for_trouble_user max_cpu 10;
  SET polar_resource_control rc_for_trouble_user FOR USER trouble_user;

• Limit CPU usage in multi-tenant or multi-service clusters

  In Software as a Service (SaaS) scenarios, multiple tenants often run on the same cluster. It is also common for different business services to share a cluster. In these situations, it is important to limit the resources that a single tenant or service can use. This prevents a user or service from consuming too much CPU and affecting the overall cluster performance. To do this, you can set separate CPU resource limits for each service. For example, you can apply limits per user or per database to achieve this.

  CREATE polar_resource_control rc_for_business_1 max_cpu 20;
  CREATE polar_resource_control rc_for_business_2 max_cpu 20;
  
  SET polar_resource_control rc_for_business_1 FOR USER user_1;
  SET polar_resource_control rc_for_business_2 FOR USER user_2;

  Based on these settings, the services used by user_1 and user_2 are each limited to a maximum of 20% of the total cluster CPU. Therefore, the two users can use a combined maximum of 40% of the CPU resources. If the workload for user_1 suddenly increases, its CPU usage remains capped at 20%, ensuring that other services on the cluster are not affected.

  If a single service uses multiple users, you can assign the same resource control to all of them.

  CREATE polar_resource_control rc_for_business_1 max_cpu 20;
  
  SET polar_resource_control rc_for_business_1 FOR USER user_1;
  SET polar_resource_control rc_for_business_1 FOR USER user_2;

  Based on these settings, the service used by user_1 and user_2 is limited to a maximum of 20% of the total cluster CPU. Therefore, the two users can use a combined maximum of 20% of the CPU resources. This prevents the service from exceeding its CPU limit when the workload of one user increases. This ensures fair resource allocation.