All Products
Search

This Product

    PolarDB:Use cases of resource controls

    Document Center

    PolarDB:Use cases of resource controls

    Last Updated:Feb 18, 2025

    This topic describes how to use resource controls to manage the resource usage of database accounts, databases, single queries, and database connections.

    Supported versions

    • The Database Edition of the cluster is Enterprise Edition.

    • When the Edition of the cluster is Cluster Edition, the cluster must run one of the following database engine versions:

      • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.48 or later.

      • PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.27 or later.

    • When the Edition of the cluster is Multi-master Cluster (Limitless), the cluster must run the following database engine version:

      • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.0.33 or later

    Usage notes

    Important

    You can use the resource control feature only after you apply for it. To use the feature, submit a ticket. To obtain technical support, join theDingTalk group 59535005981.

    • The resource control feature in PolarDB differs from the resource group feature in MySQL. The two features use different mechanisms to manage resource usage.

      • The resource control feature in PolarDB allows you to control the resource usage of databases, users, and connections. The feature is more effective for cloud-native databases.

      • The resource group feature in MySQL allows you to specify the number of CPU cores and the thread priorities for different resource groups. For information about how to use the resource group feature in MySQL, see Resource Groups. The resource group feature is not supported in PolarDB for MySQL.

    • To use the resource control feature, you must enable the thread pool feature.

    • Read-only nodes synchronize resource control information from the primary node in an asynchronous manner, which may cause latency.

    Test environment

    Important

    The Elastic Compute Service (ECS) instance and the PolarDB for MySQL cluster must be in the same region and VPC.

    • ECS instance specifications:

      ecs.g7.2xlarge (8 vCPUs, 32 GB of memory)

    • PolarDB for MySQL cluster configurations:

      • Database edition: Enterprise Edition.

      • Edition: Cluster Edition.

      • Specification type: Dedicated.

      • Database engine: PolarDB for MySQL 8.0.1.

      • Node type: polar.mysql.x4.xlarge (8 vCPUs, 32 GB of memory).

    Preparations

    Important

    Create database accounts and databases

    Create two database accounts named user_1 and user_2 and two databases named database_1 and database_2. Grant all permissions on the databases to the database accounts.

    CREATE USER user_1 IDENTIFIED WITH mysql_native_password BY 'password';
CREATE USER user_2 IDENTIFIED WITH mysql_native_password BY 'password';
CREATE DATABASE database_1;
CREATE DATABASE database_2;
GRANT ALL ON database_1.* TO user_1;
GRANT ALL ON database_2.* TO user_1;
GRANT ALL ON database_1.* TO user_2;
GRANT ALL ON database_2.* TO user_2;

    Import test data

    Use Sysbench to import test data to database_1 and database_2.

    sysbench oltp_read_write --threads=16 --mysql-host=<host>  --mysql-user=user_1 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_1 --tables=10 --table-size=500000 --report-interval=1 --time=7200 prepare
sysbench oltp_read_write --threads=16 --mysql-host=<host>  --mysql-user=user_1 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_2 --tables=10 --table-size=500000 --report-interval=1 --time=7200 prepare

    Create resource controls

    Create resource controls rc_1, rc_2, rc_3, and rc_4.

    CREATE polar_resource_control rc_1 max_cpu 20;
CREATE polar_resource_control rc_2 max_cpu 50;
CREATE polar_resource_control rc_3 max_cpu 70;
CREATE polar_resource_control rc_4 max_cpu 100;

    Query information about the created resource controls.

    SELECT * FROM mysql.polar_resource_control;

    Perform the test

    Important

    • The sysbench testing operations are performed on the ECS instance.

    • The privileged account is used to log on to the databases.

    Limit the CPU usage of a database account

    1. Launch sysbench testing to evaluate the performance of database_1 and database_2 in handling loads from the user_1 database account.

      sysbench oltp_read_only --threads=256 --mysql-host=<host>  --mysql-user=user_1 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_1 --tables=10 --table-size=500000 --report-interval=1 --time=3600 run
sysbench oltp_read_only --threads=256 --mysql-host=<host>  --mysql-user=user_1 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_2 --tables=10 --table-size=500000 --report-interval=1 --time=3600 run

    2. Apply and remove resource controls rc_1, rc_2, rc_3, and rc_4 for the user_1 database account in sequence.

      SET polar_resource_control rc_1 FOR USER user_1;
-- Wait for 3 minutes, remove rc_1 from user_1, and then apply rc_2 to user_1.
RELEASE polar_resource_control rc_1 FOR USER user_1;
SET polar_resource_control rc_2 FOR USER user_1;
-- Wait for 3 minutes, remove rc_2 from user_1, and then apply rc_3 to user_1.
RELEASE polar_resource_control rc_2 FOR USER user_1;
SET polar_resource_control rc_3 FOR USER user_1;
-- Wait for 3 minutes, remove rc_3 from user_1, and then apply rc_4 to user_1.
RELEASE polar_resource_control rc_3 FOR USER user_1;
SET polar_resource_control rc_4 FOR USER user_1;

    3. Go to the performance monitoring page of the cluster in the PolarDB console to monitor changes in CPU usage and queries per second (QPS) when CPU usage is restricted for the database account. For more information, see Performance monitoring.

      • CPU usage of the primary node and read-only node

        image

      • QPS of the primary node and read-only node

        image

      Note

      The preceding figures show that CPU usage and QPS change based on the resource controls applied to user_1. For example, the maximum CPU usage is 20% when rc_1 is applied to user_1 and 50% when rc_2 is applied to user_1. To effectively control the CPU usage of user_1, apply different resource controls to the account.

    Limit the CPU usage of a database

    1. Launch sysbench testing to evaluate the performance of database_1 in handling loads from user_1 and user_2.

      sysbench oltp_read_only --threads=256 --mysql-host=<host>  --mysql-user=user_1 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_1 --tables=10 --table-size=500000 --report-interval=1 --time=3600 run
sysbench oltp_read_only --threads=256 --mysql-host=<host>  --mysql-user=user_2 --mysql-password=<password> --mysql-port=3306 --mysql-db=database_1 --tables=10 --table-size=500000 --report-interval=1 --time=3600 run

    2. Apply and remove resource controls rc_1 and rc_2 for database_1 in sequence.

      SET polar_resource_control rc_1 FOR DATABASE database_1;
-- Wait for 3 minutes, remove rc_1 from database_1, and then apply rc_2 to database_1.
RELEASE polar_resource_control rc_1 FOR DATABASE database_1;
SET polar_resource_control rc_2 FOR  DATABASE database_1;
-- Wait for 3 minutes and then remove rc_2 from database_1.
RELEASE polar_resource_control rc_2 FOR DATABASE database_1;

    3. Go to the performance monitoring page of the cluster in the PolarDB console to monitor changes in CPU usage and QPS when CPU usage is restricted for the database. For more information, see Performance monitoring.

      image

      image

      Note

      The preceding figures show that CPU usage and QPS change based on the resource controls applied to database_1. For example, the maximum CPU usage is 20% when rc_1 is applied to database_1 and 50% when rc_2 is applied to database_1. To effectively control the CPU usage of database_1, apply different resource controls to the database.

    Limit the CPU usage of running queries

    Note

    You can use a resource control to limit the CPU usage of the running queries on a connection. After the queries are completed, the limit is automatically removed. Subsequent queries on the same connection are unaffected by the resource control.

    1. Simulate large queries.

      CREATE DATABASE rc_test;
USE rc_test;
CREATE TABLE t1(id INT NOT NULL auto_increment PRIMARY KEY, name VARCHAR(10));
INSERT INTO t1(name) VALUES('aaaaaaaaaa');
INSERT INTO t1(name) SELECT name FROM t1; -- Execute the SQL statement 20 times.

-- Simultaneously execute the statement on two clients to simulate two large queries.
SELECT COUNT(*) FROM t1 a JOIN t1 B ON a.name = B.name;

    2. Obtain the connection IDs of the two large queries.

      SHOW processlist;

    3. Create a resource control named rc_5 and apply rc_5 to the two large queries.

      CREATE polar_resource_control rc_5 max_cpu 5;
-- Apply rc_5 to the two running queries.
SET polar_resource_control rc_5 FOR query <connection_id_1>;
SET polar_resource_control rc_5 FOR query <connection_id_2>;
-- Wait for 5 minutes and then remove rc_5 from the two queries.
RELEASE polar_resource_control rc_5 FOR query <connection_id_1>;
RELEASE polar_resource_control rc_5 FOR query <connection_id_2>;

    4. Go to the performance monitoring page of the cluster in the PolarDB console to monitor changes in CPU usage and QPS when CPU usage is restricted for the running queries. For more information, see Performance monitoring.

      image

    Limit the CPU usage of connections

    Note

    You can use a resource control to limit the CPU usage of queries on a connection in a long-running session.

    1. Simulate large queries.

      CREATE database rc_test;
USE rc_test;
CREATE TABLE t1(id INT NOT NULL auto_increment PRIMARY KEY, name VARCHAR(10));
INSERT INTO t1(name) VALUES('aaaaaaaaaa');
INSERT INTO t1(name) SELECT name FROM t1; -- Execute the SQL statement 20 times.

-- Simultaneously execute the statement on two clients to simulate two large queries.
SELECT COUNT(*) FROM t1 A JOIN t1 B ON a.name = B.name;

    2. Obtain the connection IDs of the two large queries.

      SHOW processlist;

    3. Create a resource control named rc_5 and apply rc_5 to the connections of the two large queries.

      CREATE polar_resource_control rc_5 max_cpu 5;
SET polar_resource_control rc_5 FOR connection <connection_id_1>;
SET polar_resource_control rc_5 FOR connection <connection_id_2>;

    4. Go to the performance monitoring page of the cluster in the PolarDB console to monitor changes in CPU usage and QPS when CPU usage is restricted for connections. For more information, see Performance monitoring.

      image

      Note

      The preceding figure shows that the overall CPU usage of the cluster is significantly reduced after the resource control is applied to the two connections at 15:28:25. After the two large queries are re-executed at 15:31:40 after a manual interruption at 15:30:35, the overall CPU usage of the cluster remains limited.

    5. Remove the resource control from the two connections.

      -- Remove the resource control from the connections.
RELEASE polar_resource_control rc_5 FOR connection <connection_id_1>;
RELEASE polar_resource_control rc_5 FOR connection <connection_id_2>;

      After the resource control is removed from the two connections, the overall CPU usage of the cluster returns to the original level.

      image