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, see Overview of resource isolation.

• CPU resource isolation depends on cgroup. 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 cgroup.

  When you configure user-level resource isolation or function-level resource isolation, you do not need to configure cgroup if you do not want to control CPU resource isolation. However, when you configure SQL-level resource isolation, you must configure cgroup regardless of whether you want to control CPU resource isolation.

• Before you configure IOPS resource isolation, you can perform disk performance calibration. For more information, see Calibrate disk performance.

  Note

  In the current version, IOPS resource isolation no longer strongly depends on disk performance calibration.

• Make sure that the user to be isolated is 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 are created.

Background information

Resource isolation is divided into user-level, SQL-level, and function-level resource isolation. For more information, see Overview of resource isolation.

Step 1 (Optional): Configure the MAX_IOPS and MIN_IOPS parameters of a tenant

After you calibrate the disk, before you configure a resource management plan, make sure that the MAX_IOPS and MIN_IOPS parameters of the tenant are set to valid values. In this context, a valid value refers to the IOPS value corresponding to 16 KB reads as the reference value for the tenant's 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 |
   +----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
   |              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 |
   |           1001 | small_unit      | 2023-12-19 13:56:09.851665 | 2023-12-19 13:56:09.851665 |       1 |       1 |  2147483648 |    6442450944 | 9223372036854775807 | 9223372036854775807 |           1 |
   |           1002 | medium_unit     | 2023-12-19 13:56:10.030914 | 2023-12-19 13:56:10.030914 |       8 |       4 |  8589934592 |   25769803776 | 9223372036854775807 | 9223372036854775807 |           4 |
   |           1003 | large_unit      | 2023-12-19 13:56:10.112115 | 2023-12-19 13:56:10.112115 |      16 |       8 | 21474836480 |   64424509440 | 9223372036854775807 | 9223372036854775807 |           8 |
   +----------------+-----------------+----------------------------+----------------------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+
   4 rows in set
   

   If the MAX_IOPS and MIN_IOPS parameters of the tenant are both set to the default value INT64_MAX (9223372036854775807), you need to re-plan the IOPS resources available to the tenant.

3. Execute the following command to view 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 command to view the disk calibration value of the OBServer node where the tenant to be isolated is located. Use the disk calibration value corresponding to 16 KB reads as the upper limit of the IOPS setting for the 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 result, use the disk calibration values of each node as the upper limit to plan the available IOPS for the tenant. Since multiple tenants may be deployed on the same OBServer node in the cluster, you need to allocate the IOPS based on the actual business requirements.

   For example, in a cluster with two tenants deployed on the same OBServer node, each node has a disk IOPS baseline value of 20000 IOPS for 16 KB reads. If the expected load of the two tenants is similar, you can divide the 20000 IOPS equally between the two tenants (the specific allocation of available IOPS for the tenants can be adjusted based on actual business requirements). In this case, you can set the MAX_IOPS and MIN_IOPS parameters of each tenant to 10000. Alternatively, you can set the MIN_IOPS parameter to a value less than the MAX_IOPS parameter 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 the current tenant contains two users, tp_user and ap_user.

You can follow the steps below to configure a resource isolation plan to control the CPU and IOPS resources used by different users or background tasks.

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 parameters are described as follows:

   • CONSUMER_GROUP: specifies the name of the resource group.

   • COMMENT: specifies the remarks of the resource group.

   For example, 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 to confirm the creation. For more information about the oceanbase.DBA_RSRC_CONSUMER_GROUPS view, 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 parameters are described as follows:

   • PLAN: specifies the name of the resource management plan.

   • COMMENT: specifies the remarks of the resource management plan.

   For example, create a resource management plan named daytime and add remarks.

   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 to confirm the creation. For more information about the oceanbase.DBA_RSRC_PLANS view, 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. This is used to limit the CPU and IOPS resources used by the resource groups when the resource management plan is enabled.

   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 parameters are described as follows:

   • PLAN: specifies the name of the resource management plan.

   • GROUP_OR_SUBPLAN: specifies the resource group.

   • COMMENT: specifies the remarks of the content of the resource management plan. The default value is NULL.

   • MGMT_P1: specifies the maximum percentage of CPU resources that can be used when the system is under full load. The default value is 100.

   • UTILIZATION_LIMIT: specifies the upper limit of CPU resources that can be used by the resource group. The default value is 100, and the value range is (0, 100]. 100 indicates that the resource group can use all CPU resources of the tenant. If the value is 70, the resource group can use up to 70% of the CPU resources of the tenant.

   • MIN_IOPS: specifies the minimum IOPS resources reserved for the resource group when I/O contention occurs. The total value cannot exceed 100. The default value is 0.

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

   • WEIGHT_IOPS: specifies the weight of IOPS resources. The total value can exceed 100. The default value is 0.

   • MAX_NET_BANDWIDTH: specifies the maximum network bandwidth resources that can be used. The total value can exceed 100. The default value is 100.

   • NET_BANDWIDTH_WEIGHT: specifies the weight of network bandwidth resources. The total value can exceed 100, and the resources are allocated proportionally. The default value is 0.

   Here are some examples:

   • Specify the resource management plan as daytime, bind the resource group interactive_group, and set the upper limit of CPU resources that can be used to 80% of the total CPU resources of the tenant. When I/O contention occurs, set the minimum IOPS resources to 30%, the maximum IOPS resources to 90% of the total IOPS resources, the IOPS weight to 80, the maximum network bandwidth resources 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);
     

   • Specify the resource management plan as daytime, bind the resource group batch_group, and set the upper limit of CPU resources that can be used to 40% of the total CPU resources of the tenant. When I/O contention occurs, set the minimum IOPS resources to 40%, the maximum IOPS resources to 80% of the total IOPS resources, the IOPS weight to 70, the maximum network bandwidth resources 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 the oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view to confirm the creation.

   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 resource isolation matching rules based on your business scenarios.

   The syntax is as follows:

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

   The parameters are described as follows:

   • ATTRIBUTE: specifies the attribute type. The attribute name is not case-sensitive.

     • column: specifies SQL-level resource isolation.

     • user: specifies user-level resource isolation.

     • function: specifies function-level resource isolation.

   • VALUE: specifies the attribute value.

     • If the attribute type is column, you need to specify the database name, table name, column name, constant value, and username.

       Here are some examples:

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

       • The table name, column name, and constant value are required, and only one value can be specified for each. When specifying a constant value, only numeric or string values are supported.

       • When specifying the table name, column name, or username, the specified table, column, and user must exist.

     • If the attribute type is user, specify the username here. Only one user can be specified.

     • If the attribute type is function, specify one of the following background tasks corresponding to DAG threads: compaction_high, ha_high, compaction_mid, ha_mid, compaction_low, ha_low, ddl, ddl_high, clog_high, or opt_stats. Only one task can be specified. For more information about these tasks, see Overview.

   • CONSUMER_GROUP: specifies the resource group to bind. When an SQL statement matches the rule set in VALUE, it will be executed in the specified resource group. Only one resource group can be specified.

     If no resource group is specified, the system will default to the built-in OTHER_GROUPS resource group. The resources of the built-in OTHER_GROUPS are as follows:

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

     • MAX_IOPS = 100

     • WEIGHT_IOPS = 100

   Here are some examples:

   • Create a SQL-level resource isolation matching rule.

     • Specify that when a user tp_user executes an SQL statement with a WHERE condition containing test.t.c3 = 3, the SQL statement is bound to the batch_group resource group and uses the CPU and IOPS resources limited by this resource group.

       Notice

       When executing an SQL statement, it is not necessary for the statement to contain test.t.. As long as c3 is ultimately resolved to test.t.c3, the SQL statement will be bound to the batch_group resource group. For 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 an SQL statement with a WHERE condition containing t.c3=5 is executed, the SQL statement is bound to the interactive_group resource group and uses the CPU and IOPS resources limited by 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 binding a resource group by calling the SET_CONSUMER_GROUP_MAPPING subprogram, OceanBase Database also supports binding a resource group by using a hint. Users can use hints to flexibly submit SQL statements 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, you can use the following example to bind the resource group by using a hint:

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

     Note

     If a resource group is specified by using a hint but does not exist, the default resource group OTHER_GROUPS will be used when the statement is executed.

   • Create a user-level resource isolation matching rule.

     • Specify that SQL statements executed by the tp_user user are bound to the interactive_group resource group and use the CPU and IOPS resources limited by 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 batch_group resource group and use the CPU and IOPS resources limited by 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 it to the interactive_group resource group and uses the CPU and IOPS resources limited by 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 it to the batch_group resource group and uses the CPU and IOPS resources limited by this resource group.

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

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

6. Enable an appropriate resource management plan for the resource group.

   Different resource management plans may limit different resources for the same resource group. You need to enable an appropriate resource management plan for the resource group.

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

   Note

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

Considerations after configuration

• After adding resource isolation matching rules, if you delete a user and then recreate it, the resource isolation matching rules still apply.

• Resource isolation matching rules do not take effect immediately after they are added. It is expected that they may start to take effect within 10 seconds, depending on the actual environment.

• SQL-level resource isolation has higher priority than user-level and function-level resource isolation.

• After adding resource isolation matching rules, they currently only take effect in statements such as SELECT, INSERT, UPDATE, and DELETE. They do not take effect in DDL or DCL statements, nor in PL. However, they can take effect in prepareStatement.

Performance impact after configuration

• User-level and function-level resource isolation can determine which resource group to use before the SQL is parsed, so they do not affect performance.

• The performance impact of SQL-level resource isolation mainly comes from retries. Unlike user-level and function-level resource isolation, which can determine which resource group to use before the SQL is parsed, SQL-level resource isolation can only determine which resource group to use during SQL parsing or when a plan cache hit occurs. If the resource group determined during parsing or plan cache hit is different from the current one, the system will retry once to use the resource group specified by the matching rule to process the SQL.

  The performance impact of SQL-level resource isolation can be categorized into the following three 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, and the rule specifies the batch_group resource group, the SQL will be executed using the batch_group resource group. The next SQL statement will also be executed using the batch_group resource group until it matches a rule that specifies a different resource group, at which point the system will retry. In scenarios where a batch of SQL statements is executed continuously and all are bound to the same resource group, using this strategy ensures that only the first SQL 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, resulting in a significant impact on performance.

References

• Overview of resource isolation

• Disk performance calibration

• Resource isolation for background tasks