Configure resource isolation within a tenant

This topic describes how to configure resource isolation in MySQL mode.

Prerequisites

• Before you configure resource isolation, we recommend that you understand the basic concepts such as resource group, resource management plan, and resource management plan content. For more information about the basic concepts and applicable scenarios of resource isolation, see Overview.

• CPU resource isolation depends on cgroups. If you want to control CPU resource isolation, you must configure the cgroup directory and enable the cgroup feature before you configure resource isolation. For more information, see Configure cgroups.

  If you configure User-level or Function-level resource isolation and do not need to control CPU resource isolation (only IOPS resource isolation is required), you do not need to configure cgroups. If you configure SQL-level resource isolation, you must configure cgroups regardless of whether you need to control CPU resource isolation.

• Before you configure IOPS resource isolation, you must calibrate the disk performance. For more information, see Calibrate disk performance.

  If you only need to control CPU resource isolation, you do not need to calibrate the disk performance.

  Note

  For V4.3.5, starting from V4.3.5 BP2, both CPU and IOPS resource isolation no longer have a strict dependency on disk performance calibration.

• Make sure that the user for which resource isolation will be configured has been created. For more information, see Create a user.

• If you want to configure SQL-level resource isolation, make sure that the database, table, and column to be isolated have been created.

Background information

Resource isolation is divided into User-level, SQL-level, and Function-level resource isolation. For more information, see Overview.

(Optional) Step 1: Specify valid values for the MAX_IOPS and MIN_IOPS parameters of the tenant

After disk calibration, before you configure a resource isolation plan, make sure that the MAX_IOPS and MIN_IOPS parameters in the unit config of the tenant are set to valid values. A valid value uses the IOPS value corresponding to 16 KB reads as the reference for tenant IOPS configuration.

1. Log in to the sys tenant of the cluster as the root user.

2. Execute the following command to view the resource specifications of the tenant to be isolated.

   obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_UNIT_CONFIGS;
   

   The query result is as follows:

   +----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+---------------------+----------------------+
   | UNIT_CONFIG_ID | NAME            | CREATE_TIME                | MODIFY_TIME                | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS            | MIN_IOPS            | IOPS_WEIGHT | MAX_NET_BANDWIDTH   | NET_BANDWIDTH_WEIGHT |
   +----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+---------------------+----------------------+
   |              1 | sys_unit_config | 2023-12-19 13:55:04.463295 | 2023-12-19 13:56:08.969718 |       3 |       3 |  2147483648 |    3221225472 | 9223372036854775807 | 9223372036854775807 |           3 | 9223372036854775807 |                    4 |
   |           1001 | small_unit      | 2023-12-19 13:56:09.851665 | 2023-12-19 13:56:09.851665 |       1 |       1 |  2147483648 |    6442450944 | 9223372036854775807 | 9223372036854775807 |           1 | 9223372036854775807 |                    2 |
   |           1002 | medium_unit     | 2023-12-19 13:56:10.030914 | 2023-12-19 13:56:10.030914 |       8 |       4 |  8589934592 |   25769803776 | 9223372036854775807 | 9223372036854775807 |           4 | 9223372036854775807 |                    2 |
   |           1003 | large_unit      | 2023-12-19 13:56:10.112115 | 2023-12-19 13:56:10.112115 |      16 |       8 | 21474836480 |   64424509440 | 9223372036854775807 | 9223372036854775807 |           8 | 9223372036854775807 |                    2 |
   +----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+---------------------+----------------------+
   4 rows in set
   

   Based on the query results, if the MAX_IOPS and MIN_IOPS parameters of the tenant both use the default value INT64_MAX (9223372036854775807), you must replan the IOPS resources available to the tenant.

3. Execute the following statement to query the OBServer nodes on which the tenant is deployed:

   obclient [oceanbase]> SELECT DISTINCT SVR_IP, SVR_PORT FROM oceanbase.CDB_OB_LS_LOCATIONS WHERE tenant_id = xxxx;
   

   The query result is as follows:

   +----------------+----------+
   | SVR_IP         | SVR_PORT |
   +----------------+----------+
   | xx.xxx.xxx.xx1 |    xxxx1 |
   | xx.xxx.xxx.xx1 |    xxxx2 |
   | xx.xxx.xxx.xx1 |    xxxx3 |
   +----------------+----------+
   3 rows in set
   

4. Execute the following statement to query the disk calibration value on each OBServer node where the tenant for which you will configure resource isolation is deployed. Use the 16 KB read calibration value as the upper limit for IOPS settings on that node.

   obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_IO_BENCHMARK WHERE MODE='READ' AND SIZE=16384;
   

   The query result is as follows:

   +----------------+----------+--------------+------+-------+-------+------+---------+
   | SVR_IP         | SVR_PORT | STORAGE_NAME | MODE | SIZE  | IOPS  | MBPS | LATENCY |
   +----------------+----------+--------------+------+-------+-------+------+---------+
   | xx.xxx.xxx.xx1 |    xxxx1 | DATA         | READ | 16384 | 48162 |  752 |     331 |
   | xx.xxx.xxx.xx1 |    xxxx2 | DATA         | READ | 16384 | 47485 |  741 |     336 |
   | xx.xxx.xxx.xx1 |    xxxx3 | DATA         | READ | 16384 | 48235 |  753 |     331 |
   +----------------+----------+--------------+------+-------+-------+------+---------+
   3 rows in set
   

   Based on the query results, use the calibration value obtained for each node as the upper limit when planning IOPS available to the tenant. Multiple tenants in a cluster may be deployed on the same OBServer nodes. Allocate IOPS according to your business needs.

   Assume that a cluster has two tenants deployed on the same OBServer nodes, the baseline IOPS value for 16 KB reads is 20000 on each OBServer node, and the loads of the two tenants are similar. You can evenly distribute the IOPS resources to the two tenants based on the actual situation. Specifically, you can set the MAX_IOPS and MIN_IOPS parameters to 10000 for both tenants. You can also set MIN_IOPS to a value smaller than that of MAX_IOPS based on your business needs.

5. Execute the following commands to modify the MAX_IOPS and MIN_IOPS parameters of the tenant.

   We recommend that you modify the MIN_IOPS parameter first, and then the MAX_IOPS parameter.

   ALTER RESOURCE UNIT unit_name MIN_IOPS = xxx;
   

   ALTER RESOURCE UNIT unit_name MAX_IOPS = xxx;
   

Step 2: Configure a resource isolation plan

Assume that there are two users named tp_user and ap_user in the current tenant.

You can configure a resource isolation plan based on the following steps to control that different users or background tasks use different CPU or IOPS resources.

1. Log in to the MySQL tenant of the cluster as the tenant administrator.

2. Call the CREATE_CONSUMER_GROUP subprogram in the DBMS_RESOURCE_MANAGER system package to create a resource group.

   The syntax is as follows:

   CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   CONSUMER_GROUP => 'group_name' ,
   COMMENT => 'coments'
   );
   

   The parameter descriptions are as follows:

   • CONSUMER_GROUP: the name of the resource group.

   • COMMENT: the comment about the resource group.

   For example, you can create two resource groups named interactive_group and batch_group.

   obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   CONSUMER_GROUP => 'interactive_group' ,
   COMMENT => 'TP'
   );
   

   obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   CONSUMER_GROUP => 'batch_group' ,
   COMMENT => 'AP'
   );
   

   After the resource groups are created, you can query the oceanbase.DBA_RSRC_CONSUMER_GROUPS view for confirmation. For more information, see oceanbase.DBA_RSRC_CONSUMER_GROUPS.

3. Call the CREATE_PLAN subprogram in the DBMS_RESOURCE_MANAGER system package to create a resource management plan.

   The syntax is as follows:

   CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN => 'plan_name',
   comment => 'coments');
   

   The parameter descriptions are as follows:

   • PLAN: the name of the resource management plan.

   • COMMENT: the comment about the resource management plan.

   For example, you can create a resource management plan named daytime and add a comment to the plan.

   obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN => 'daytime',
   comment => 'TPFirst');
   

   After the resource management plan is created, you can query the oceanbase.DBA_RSRC_PLANS view for confirmation. For more information, see oceanbase.DBA_RSRC_PLANS.

4. Call the CREATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER system package to create the content of the resource management plan. In this step, specify the parameters of the resource management plan to enable the plan to limit the CPU and IOPS resources used by each resource group.

   The syntax is as follows:

   CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
      PLAN => 'plan_name',
      GROUP_OR_SUBPLAN => 'group_name' ,
      COMMENT  => 'comments',
      MGMT_P1 => int_value,
      UTILIZATION_LIMIT => int_value,
      MIN_IOPS => int_value,
      MAX_IOPS => int_value,
      WEIGHT_IOPS => int_value,
      MAX_NET_BANDWIDTH => int_value,
      NET_BANDWIDTH_WEIGHT => int_value);
   

   The parameter descriptions are as follows:

   • PLAN: the name of the resource management plan.

   • GROUP_OR_SUBPLAN: the name of the resource group.

   • COMMENT: the comment about the content of the resource management plan. The default value is NULL.

   • MGMT_P1: the maximum CPU utilization when the system is at full capacity, relative to the total available CPU resources. The default value is 100.

   • UTILIZATION_LIMIT indicates the upper limit on the CPU resources available for the resource group. The default value is 100. The value range is (0, 100]. The value 100 indicates that all CPU resources of the tenant are available for the resource group. The value 70 indicates that at most 70% of the CPU resources of the tenant are available for the resource group.

   • MIN_IOPS indicates the IOPS resources reserved for the resource group in the case of I/O resource contention. The sum of MIN_IOPS values of all resource groups cannot exceed 100. The default value is 0.

   • MAX_IOPS: the maximum IOPS resources that can be used by the resource group. The total value of MAX_IOPS across all resource groups can exceed 100. The default value is 100.

   • WEIGHT_IOPS: the weight of IOPS resources. The total value of WEIGHT_IOPS across all resource groups can exceed 100. The default value is 0.

   • MAX_NET_BANDWIDTH specifies the maximum network bandwidth resource that can be used. The sum can exceed 100. The default value is 100.

   • NET_BANDWIDTH_WEIGHT specifies the weight value for network bandwidth. The sum can exceed 100, and bandwidth is allocated proportionally. The default value is 0.

   Here are some examples:

   • Create a plan directive as follows: Set the resource plan to daytime, the resource group to interactive_group, and the maximum CPU resources available to 80% of the total CPU resources of the tenant. When I/O contention occurs, set the minimum IOPS resources available to 30% of the total IOPS resources, the maximum IOPS resources available to 90% of the total IOPS resources, and the weight of IOPS resources to 80. Set the network bandwidth resources available to 40%, and the network bandwidth weight to 40.

     obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
     PLAN => 'daytime',
     GROUP_OR_SUBPLAN => 'interactive_group' ,
     COMMENT  => '',
     UTILIZATION_LIMIT =>80,
     MIN_IOPS => 30,
     MAX_IOPS => 90,
     WEIGHT_IOPS => 80,
     MAX_NET_BANDWIDTH => 40,
     NET_BANDWIDTH_WEIGHT => 40);
     

   • Create a plan directive as follows: Set the resource plan to daytime, the resource group to batch_group, and the maximum CPU resources available to 40% of the total CPU resources of the tenant. When I/O contention occurs, set the minimum IOPS resources available to 40% of the total IOPS resources, the maximum IOPS resources available to 80% of the total IOPS resources, and the weight of IOPS resources to 70. Set the network bandwidth resources available to 30%, and the network bandwidth weight to 30.

     obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
     PLAN => 'daytime',
     GROUP_OR_SUBPLAN => 'batch_group' ,
     COMMENT  => '',
     UTILIZATION_LIMIT => 40,
     MIN_IOPS => 40,
     MAX_IOPS => 80,
     WEIGHT_IOPS => 70,
     MAX_NET_BANDWIDTH => 30,
     NET_BANDWIDTH_WEIGHT => 30);
     

   After the content of the resource management plan is created, you can query the oceanbase.DBA_RSRC_PLAN_DIRECTIVES view and oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view for confirmation.

   For more information about the oceanbase.DBA_RSRC_PLAN_DIRECTIVES view, see oceanbase.DBA_RSRC_PLAN_DIRECTIVES.

   For more information about the oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view, see oceanbase.DBA_OB_RSRC_IO_DIRECTIVES.

5. Call the SET_CONSUMER_GROUP_MAPPING subprogram in the DBMS_RESOURCE_MANAGER system package to create a resource isolation matching rule based on the usage scenario.

   The syntax is as follows:

   CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
      ATTRIBUTE => 'column | user | function', 
      VALUE => 'values',
      CONSUMER_GROUP => 'group_name');
   

   The parameter descriptions are as follows:

   • ATTRIBUTE: the type of attribute, which is case-insensitive.

     • column indicates SQL-level resource isolation.

     • user indicates user-level resource isolation.

     • function indicates function-level resource isolation.

   • VALUE: specifies the attribute value.

     • For the attribute type column, specify the values of the database name, table name, column name, constant value, and username.

       Specifically:

       • Database name and username are optional. The default database name is the current database name. If no username is specified, the rule is applied to all users, including those to be created in the current tenant.

       • Table name, column name, and constant value are required, and only one value can be specified for each item. When you specify a constant value, you must specify it as a numeric value or a string.

       • When you specify the table name, column name, or username, the table, column, and user must exist.

       • When you specify the table name, column name, and username, the specified table, column, and user must exist.

     • If the attribute type is user, specify the username. At present, you can specify only one username.

     • If the attribute type is function, specify one of the following background tasks corresponding to the DAG thread: compaction_high, ha_high, compaction_mid, ha_mid, compaction_low, ha_low, ddl, ddl_high, clog_high, and opt_stats. At present, you can specify only one task. For more information about the tasks, see Overview of resource isolation.

       If you do not specify the resource group to which the rule is bound, the default built-in resource group OTHER_GROUPS is used. The resource group OTHER_GROUPS is the built-in resource group to which the rule is bound by default. The resources of the built-in resource group OTHER_GROUPS are as follows:

       • MIN_IOPS = 100 - SUM(resources of other resource groups in the tenant)

       • MAX_IOPS = 100

       • WEIGHT_IOPS = 100

   Example:

   • Create an SQL-level resource isolation matching rule.

     • Specify that when the WHERE clause of an SQL statement executed by the tp_user user contains the string test.t.c3 = 3, the SQL statement is bound to the resource group batch_group and uses the CPU and IOPS resources specified for this resource group.

       Notice

       When an SQL statement is executed, it does not have to contain the string test.t.. It will be bound to the resource group batch_group when c3 is resolved to test.t.c3. Example: SELECT * FROM test.t WHERE c3 = 1;.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'column',
       VALUE => 'test.t.c3=3 for tp_user',
       CONSUMER_GROUP => 'batch_group');
       

     • Specify that when the WHERE clause of an SQL statement contains the string t.c3=5, the SQL statement is bound to the resource group interactive_group and uses the CPU and IOPS resources specified for this resource group.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'column',
       VALUE => 't.c3=5',
       CONSUMER_GROUP => 'interactive_group');
       

     In addition to calling the SET_CONSUMER_GROUP_MAPPING subprogram to bind a resource group, OceanBase Database also supports binding resource groups through hints. You can use a hint to flexibly submit the SQL statement to be executed to a specified resource group. For example, if the SQL statement to be executed is SELECT * FROM T and you want it to use the resources limited by the batch_group resource group, bind the statement to the resource group as follows:

     obclient [test]> SELECT /*+resource_group('batch_group')*/ * FROM t;
     

     Note

     After you specify the resource group by using a hint, if the resource group does not exist, the statement is executed in the default resource group OTHER_GROUPS.

   • Create a user-level resource isolation matching rule.

     • Specify that SQL statements executed by the tp_user user are bound to the resource group interactive_group and use the CPU and IOPS resources specified for this resource group.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'user',
       VALUE => 'tp_user',
       CONSUMER_GROUP => 'interactive_group');
       

     • Specify that SQL statements executed by the ap_user user are bound to the resource group batch_group and use the CPU and IOPS resources specified for this resource group.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'user',
       VALUE => 'ap_user',
       CONSUMER_GROUP => 'batch_group');
       

   • Create a function-level resource isolation matching rule.

     • Specify that when the compaction_high task is executed, the system binds the task to the resource group interactive_group and uses the CPU and IOPS resources specified for this resource group.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'function',
       VALUE => 'compaction_high',
       CONSUMER_GROUP => 'interactive_group');
       

     • Specify that when the ddl_high task is executed, the system binds the task to the resource group batch_group and uses the CPU and IOPS resources specified for this resource group.

       obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'function',
       VALUE => 'ddl_high',
       CONSUMER_GROUP => 'batch_group');
       

   After the matching rule is created, you can query the oceanbase.DBA_RSRC_GROUP_MAPPINGS view to confirm it. For more information about the oceanbase.DBA_RSRC_GROUP_MAPPINGS view, see oceanbase.DBA_RSRC_GROUP_MAPPINGS.

6. Enable a suitable resource management plan for the resource group.

   Different resource groups are limited to different resources depending on the resource management plan. Therefore, you must enable a suitable resource management plan for the resource group.

   obclient [test]> SET GLOBAL resource_manager_plan = 'daytime';
   

   Note

   If you do not want to limit the resources, execute the SET GLOBAL resource_manager_plan = ''; statement to disable all resource plans.

Considerations after configuration

• After a matching rule for resource isolation is added, if you delete the user and create the user again, this matching rule still applies.

• After you add a resource isolation matching rule, it may take up to 10 seconds to take effect. The actual time depends on the environment.

• SQL-level resource isolation takes precedence over user-level and function-level resource isolation.

• After you add a resource isolation matching rule, it takes effect only in statements such as SELECT, INSERT, UPDATE, and DELETE. It does not take effect in DDL or DCL statements or in PL. However, it takes effect in prepareStatement.

Performance impact after configuration

• User-level and function-level resource isolation do not affect performance because they can determine which resource group to use before parsing an SQL statement.

• SQL-level resource isolation affects performance mainly due to retries. Unlike user-level and function-level resource isolation, which can determine the resource group to use before parsing an SQL statement, SQL-level resource isolation can only determine the resource group to use when parsing an SQL statement or when the statement hits the plan cache. If the system finds that the resource group to use is different from the one currently being used, it will retry the statement to use the resource group specified by the matching rule.

  The performance impact of SQL-level resource isolation mainly depends on the following scenarios:

  1. If an SQL statement does not match any rule, it has almost no impact on performance.

  2. If an SQL statement matches a rule, assume the rule specifies the batch_group resource group. The statement will eventually execute in the batch_group resource group, and the next statement will also execute in the batch_group resource group until it matches a rule that requires switching to another resource group. At that point, the system will retry the statement to use the resource group specified by the matching rule. For a batch of SQL statements executed consecutively and all bound to the same resource group, using this strategy ensures that only the first statement in the batch requires a retry, while subsequent statements do not. This minimizes retries and has a minimal impact on performance.

  3. If each SQL statement is expected to use a different resource group from the previous one, each statement will require a retry, which significantly impacts performance.

References

• Overview of resource isolation

• Calibrate the disk performance

• Resource isolation in background tasks