Configure resource isolation within a tenant

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

Prerequisites

• Before you configure resource isolation, we recommend that you understand the basic concepts such as resource groups, resource management plans, and resource management plan contents. For more information about the basic concepts and applicable scenarios of resource isolation, 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 about how to configure the cgroup directory and enable the cgroup feature, 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 (you only want to isolate IOPS resources). However, if you want to 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 must calibrate the disk performance. For more information about how to calibrate the disk performance, 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 databases, tables, and columns to be isolated are created.

Background information

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

(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 values of the tenant.

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

   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 two users, namely tp_user and ap_user, exist in the current tenant.

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

1. Log in to the Oracle tenant of the cluster as a tenant administrator.

2. Call the CREATE_CONSUMER_GROUP subprogram in the DBMS_RESOURCE_MANAGER system package to create two resource groups that are required for resource isolation.

   The syntax is as follows:

   BEGIN DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   consumer_group => 'group_name' ,
   COMMENT => 'coments'
   );
   END; 
   

   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, you can create two resource groups, big_group and small_group, as follows:

   obclient [SYS]> delimiter //
   

   obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   consumer_group => 'big_group' ,
   COMMENT => 'TP'
   );
   END; //
   

   obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
   consumer_group => 'small_group' ,
   COMMENT => 'AP'
   );
   END; //
   

   After the resource groups are created, you can query the DBA_RSRC_CONSUMER_GROUPS view to verify the creation. For more information about the DBA_RSRC_CONSUMER_GROUPS view, see 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:

   BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN => 'plan_name',
   COMMENT =>'coments'
   );
   END;
   

   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 plan_a:

   obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN(
   PLAN => 'plan_a');
   END; //
   

   After the resource management plan is created, you can query the DBA_RSRC_PLANS view to verify the creation. For more information about the DBA_RSRC_PLANS view, see DBA_RSRC_PLANS.

4. Call the CREATE_PLAN_DIRECTIVE subprogram in the DBMS_RESOURCE_MANAGER system package to create resource management plan directives. The directives are required for you to specify the CPU and IOPS resources available to each resource group when you enable the resource management plan.

   The syntax is as follows:

   BEGIN 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);
   END;
   

   The parameters are described as follows:

   • PLAN: specifies the name of the resource management plan associated with the resource management plan directive.

   • GROUP_OR_SUBPLAN: specifies the resource group.

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

   • MGMT_P1: specifies the maximum CPU utilization, which can reach 100% when the system reaches full load. 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 40 indicates that at most 40% of the CPU resources of the tenant are available for the resource group.

   • MIN_IOPS: specifies the minimum IOPS available to the resource group when I/O contention occurs. The sum of the values of this parameter for all resource groups must be less than or equal to 100. The default value is 0.

   • MAX_IOPS: specifies the maximum IOPS available to the resource group. The sum of the values of this parameter for all resource groups can exceed 100. The default value is 100.

   • WEIGHT_IOPS indicates the weight for IOPS resources. The sum of WEIGHT_IOPS values of 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 plan_a, the resource group to big_group, and the maximum CPU resources available to 60% of the total CPU resources of the tenant. When I/O contention occurs, set the minimum IOPS resources available to 20% of the total IOPS resources, the maximum IOPS resources available to 100% of the total IOPS resources, and the weight of IOPS resources to 20. Set the network bandwidth resources available to 50%, and the network bandwidth weight to 50.

      obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
      PLAN => 'plan_a',
      GROUP_OR_SUBPLAN => 'big_group',
      COMMENT  => 'TP  priority',
      UTILIZATION_LIMIT =>60,
      MIN_IOPS => 20,
      MAX_IOPS => 100,
      WEIGHT_IOPS => 20,
      MAX_NET_BANDWIDTH => 50,
      NET_BANDWIDTH_WEIGHT =>50);
      END; //
   

   • Create a plan directive as follows: Set the resource plan to plan_a, the resource group to small_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 10% of the total IOPS resources, the maximum IOPS resources available to 90% of the total IOPS resources, and the weight of IOPS resources to 30. Set the network bandwidth resources available to 60%, and the network bandwidth weight to 60.

     obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
     PLAN => 'plan_a',
     GROUP_OR_SUBPLAN => 'small_group' ,
     COMMENT  => 'AP  priority',
     UTILIZATION_LIMIT =>40,
     MIN_IOPS => 10,
     MAX_IOPS => 90,
     WEIGHT_IOPS => 30,
     MAX_NET_BANDWIDTH => 60,
     NET_BANDWIDTH_WEIGHT =>60);
     END; //
     

   After the directives are created, you can query the DBA_RSRC_PLAN_DIRECTIVES and DBA_OB_RSRC_IO_DIRECTIVES views to verify the creation.

   For more information about the DBA_RSRC_PLAN_DIRECTIVES view, see DBA_RSRC_PLAN_DIRECTIVES.

   For more information about the DBA_OB_RSRC_IO_DIRECTIVES view, see DBA_OB_RSRC_IO_DIRECTIVES.

5. Call the SET_CONSUMER_GROUP_MAPPING subprogram in the DBMS_RESOURCE_MANAGER package to create resource isolation matching rules in your scenario.

   The syntax is as follows:

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

   Parameters in the preceding statement are described as follows:

   • ATTRIBUTE specifies the attribute type. The attribute name is case-insensitive.

     • column indicates resource isolation at the SQL level.

     • user indicates resource isolation at the user level.

     • function indicates resource isolation at the function level.

   • VALUE specifies the attribute value.

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

       The following points apply:

       • The database name and username are optional. By default, the database name is the same as the username. If you do not specify a username, the rule applies to all users, including those created later in the current tenant.

       • The table name, column name, and constant value are mandatory. Each can have only one value. When you specify the constant value, you can specify only a number or a string.

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

       In OceanBase Database in Oracle mode, the database name, table name, column name, and username in the preceding statement will be automatically changed to uppercase upon successful execution. You can add double quotation marks to keep them lowercase. Example:

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE        =>  'column',
       VALUE            => '"test"."t1"."c3" = 3 for "user_big"',
       CONSUMER_GROUP   => 'big_group');
       END; //
       

     • If the attribute type is user, you must specify the username.

     • If the attribute type is function, you must specify one of the following background tasks for the DAG thread: compaction_high, ha_high, compaction_mid, ha_mid, compaction_low, ha_low, ddl, ddl_high, clog_high, or opt_stats. Currently, only one task can be specified. For more information about these tasks, see Overview of resource isolation.

   • CONSUMER_GROUP specifies the consumer group to which the statement must be bound. This means that after a statement matches the rule specified by VALUE, it is executed in the specified consumer group. Currently, only one consumer group can be specified.

     If the consumer group is not specified, the built-in OTHER_GROUPS consumer group is used by default. The resource limits of OTHER_GROUPS are as follows:

     • MIN_IOPS = 100 - SUM(total resource limits of other consumer groups in the tenant)

     • MAX_IOPS = 100

     • WEIGHT_IOPS = 100

   Here are examples:

   • Create a resource isolation rule at the SQL level.

     • Specify that when the tp_user user executes an SQL statement where the WHERE clause contains sys.t.c3 = 3, the statement is executed in the big_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       Note

       When you execute an SQL statement, the statement does not have to contain sys.t.. It is sufficient for c3 to be resolved as sys.t.c3 for the statement to be executed in the big_group consumer group. Example: SELECT * FROM sys.t WHERE c3 = 1;.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'column',
       VALUE => 'sys.t.c3=3 for tp_user',
       CONSUMER_GROUP => 'big_group');
       END; //
       

     • Specify that when an SQL statement with a WHERE clause containing t.c3=5 is executed, it is executed in the small_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'column',
       VALUE => 't.c3=5',
       CONSUMER_GROUP => 'small_group');
       END; //
       

     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 big_group resource group, bind the statement to the resource group as follows:

     obclient [SYS]> SELECT /*+resource_group('big_group')*/ * FROM T;
     

     Note

     If the resource group specified by Hint does not exist, the default consumer group OTHER_GROUPS is used when the statement is executed.

   • Create a resource isolation rule at the user level.

     • Specify that the SQL statements executed by the tp_user user are executed in the big_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'user',
       VALUE => 'tp_user',
       CONSUMER_GROUP => 'big_group');
       END; //
       

     • Specify that the SQL statements executed by the ap_user user are executed in the small_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'user',
       VALUE => 'ap_user',
       CONSUMER_GROUP => 'small_group');
       END; //
       

   • Create a resource isolation rule at the function level.

     • Specify that when the ha_high task is executed, it is executed in the big_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'function',
       VALUE => 'ha_high',
       CONSUMER_GROUP => 'big_group');
       END; //
       

     • Specify that when the ddl_high task is executed, it is executed in the small_group consumer group, using the CPU resources and IOPS resources limited for this consumer group.

       obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
       ATTRIBUTE => 'function',
       VALUE => 'ddl_high',
       CONSUMER_GROUP => 'small_group');
       END; //
       

   After the resource group is created, you can query the DBA_RSRC_GROUP_MAPPINGS view to confirm the creation. For more information about the DBA_RSRC_GROUP_MAPPINGS view, see DBA_RSRC_GROUP_MAPPINGS.

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

   The resources that are limited for the same resource group may vary in different resource management plans. You need to enable a suitable resource management plan for the resource group.

   obclient [SYS]>delimiter ;
   

   obclient [SYS]> ALTER SYSTEM SET resource_manager_plan = 'plan_a';
   

   Note

   If you do not want to limit resources, you can disable all resource management plans by using the ALTER SYSTEM SET resource_manager_plan = ''; statement.

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.

• After adding resource isolation matching rules, they may 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 are currently effective only in statements such as SELECT, INSERT, UPDATE, and DELETE, but not in DDL or DCL statements or in PL. However, they are effective in prepareStatement.

Performance impact after configuration

• User-level and function-level resource isolation can determine which resource group to use before parsing an SQL statement, so they have no performance impact.

• The performance impact of SQL-level resource isolation mainly comes from retries. Unlike user-level and function-level resource isolation, which determine the resource group to use before parsing an SQL statement, SQL-level resource isolation determines the resource group to use during SQL parsing or when a plan is hit in the plan cache. If the resource group to use 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 statement.

  The performance impact of SQL-level resource isolation can be divided into the following three scenarios:

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

  2. If an SQL statement matches a rule, and the resource group specified by the rule is big_group, the statement will be executed using the resources of big_group, and the next SQL statement will also be executed using the resources of big_group until it matches a rule that specifies a different resource group. At that point, the system will retry to use the resources of the new resource group. In scenarios where a batch of SQL statements is executed consecutively 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 performance impact.

  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 performance impact.

References

• Overview of resource isolation

• Calibrate the disk performance

• Resource isolation in background tasks