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 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 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 or function-level resource isolation, you do not need to configure cgroup if you do not want to control CPU resource isolation (you only need to perform IOPS resource isolation). When you configure SQL-level resource isolation, you must configure cgroup regardless of whether you want to control CPU resource isolation.

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

  Note

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

• Make sure that you have created the users to be isolated. For more information about how to create a user, see Create a user.

• If you want to configure SQL-level resource isolation, make sure that you have created the databases, tables, and columns to be isolated.

Background information

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

Step 1 (Optional): Configure the MAX_IOPS and MIN_IOPS values of a tenant to valid values

After you complete disk calibration, before you configure a resource management plan, make sure that the MAX_IOPS and MIN_IOPS values of the tenant are valid values. In this context, a valid value refers to the IOPS value corresponding to 16 KB reads as a reference 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 values of the tenant are both the default value INT64_MAX (9223372036854775807), you need to re-plan the IOPS resources available to the tenant.

3. Execute the following command to confirm 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 confirm the disk calibration values of the OBServer node on which the tenant to be isolated is deployed. Use the disk calibration value corresponding to 16 KB reads as the upper limit of the IOPS settings 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 IOPS available to the tenant. Since multiple tenants may be deployed on the same OBServer node in the cluster, you need to allocate the IOPS based on your business requirements.

   For example, if two tenants are deployed on the same OBServer node in a cluster, and the disk IOPS baseline value corresponding to 16 KB reads for each OBServer node is 20,000 IOPS, and the expected loads of the two tenants are similar, you can divide the 20,000 IOPS equally between the two tenants (the specific IOPS allocation can be adjusted based on your business requirements). In this case, you can configure the MAX_IOPS and MIN_IOPS values of each tenant to 10,000. You can also set the MIN_IOPS value to a value less than the MAX_IOPS value based on your business requirements.

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 before modifying 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

Suppose there are already 2 users, tp_user and ap_user, in the current tenant.

You can reference the following steps to configure a resource isolation policy to enable different users or background tasks to use different CPU or IOPS resources.

1. The tenant administrator logs in to the Oracle tenant of the cluster.

2. Call the CREATE_CONSUMER_GROUP procedure in the DBMS_RESOURCE_MANAGER package to create two resource groups for the resource isolation.

   The statement is as follows:

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

   The following describes the related parameters:

   • CONSUMER_GROUP: specify the name of the resource group.

   • COMMENT specifies a description of the resource group.

   For example, create the following two resource groups named big_group and small_group.

   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; //
   

   You can query the DBA_RSRC_CONSUMER_GROUPS view after a resource consumer group is created. For more information about the DBA_RSRC_CONSUMER_GROUPS view, see DBA_RSRC_CONSUMER_GROUPS.

3. Execute the CREATE_PLAN subprogram in the DBMS_RESOURCE_MANAGER package to create a resource plan.

   The following statement applies:

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

   The related parameter descriptions are as follows:

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

   • COMMENT : Specify the remarks for 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; //
   

   The plan is created successfully. You can query the DBA_RSRC_PLANS view to confirm this. For more information about the DBA_RSRC_PLANS view, see DBA_RSRC_PLANS.

4. Call the CREATE_PLAN_DIRECTIVE procedure in the DBMS_RESOURCE_MANAGER system package to create a resource directive for the resource management plan. This directive will restrict the CPU and IOPS resources used by the resource group when the resource management plan is enabled.

   The statement 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 following table describes the parameters:

   • PLAN: Name of the resource management plan to which this resource management plan content is associated.

   • GROUP_OR_SUBPLAN: Specifies a resource group.

   • COMMENT : Remarks about the resource management plan. Default value is NULL.

   • MGMG_P1 - This option specifies the maximum percentage of CPU utilization when the system is under full load. The default value is 100. * MGMT_P1 - This option specifies the maximum percentage of CPU utilization 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 a resource group. The default value is 100. The value of this parameter ranges from 0 to 100, excluding 0. 100 indicates that the maximum CPU resources that a tenant can use. If the value is 40, the maximum CPU resources that a tenant can use is 40%.

   • MIN_IOPS: The number of IOPS resources reserved for the resource group to deal with I/O contention, with a total sum not exceeding 100. The default value is 0.

   • MAX_IOPS is an optional parameter that specifies the maximum IOPS resources that can be used for the resource group. The total value can exceed 100, and the default is 100.

   • WEIGHT_IOPS: A value that specifies the weight of IOPS. The total can exceed 100, and the default value is 0.

   • MAX_NET_BANDWIDTH: specifies the maximum bandwidth resources that can be used. The total bandwidth can exceed 100, with the default value being 100.

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

   For example:

   • ResourcePlan is set to plan_a, resource group big_group is bound, and the upper limit of CPU resources is set to 60% of the total CPU resources of the tenant. At the same time, when there is I/O contention, the minimum available IOPS resources are set to 20%, the upper limit of available IOPS resources is set to 100% of the total IOPS resources, the IOPS resource weight is 20, the available network bandwidth resources are set to 50%, and the network bandwidth weight is 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; //
     

   • Specify the resource plan as plan_a, bind it to the small_group resource group, and set the maximum number of CPU cores available to the tenant to 40% of the total CPU resources. During I/O contention, specify the minimum available IOPS resources to be 10%, the maximum available IOPS resources to 90% of the total IOPS resources, the IOPS resource weight to 30, the available bandwidth to 60%, and the bandwidth weight to 60.

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

   Create successful, you can query the DBA_RSRC_PLAN_DIRECTIVES view and DBA_OB_RSRC_IO_DIRECTIVES view to verify.

   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, based on actual scenarios.

   The syntax is as follows:

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

   The parameters are described as follows:

   • ATTRIBUTE: specifies the type of attribute. The name of an attribute 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 value of the attribute.

     • If the attribute type is column, specify the database name, table name, column name, literal value, and username in this parameter.

       The following points are noteworthy:

       • The database name and username are optional. If you do not specify the database name, the default database name is the current one. If you do not specify the username, the rule takes effect for all users, including those created after the current tenant is created.

       • The table name, column name, and literal value are required. You can specify only one value for each. A literal value can be a number or a string.

       • If you specify the table name, column name, or username, the specified table, column, and user must exist in the database.

       In OceanBase Database's Oracle mode, after the statement is successfully executed, the database name, table name, column name, and username are automatically converted to uppercase. If you want to keep them in lowercase, you can enclose them in double quotation marks to prevent them from being converted to uppercase. Here is an 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, specify the username here. You can specify only one user.

     • If the attribute type is function, specify one of the following types of background tasks: compaction_high, ha_high, compaction_mid, ha_mid, compaction_low, ha_low, ddl, ddl_high, clog_high, and opt_stats. You can specify only one task. For more information about these tasks, see Overview.

   • CONSUMER_GROUP: specifies the resource group to which you want to bind the SQL statement. The value of this parameter must be a valid resource group. You can bind the SQL statement to only one resource group.

     If you do not specify a resource group to bind the SQL statement, the system automatically binds it to the built-in OTHER_GROUPS resource group. The resources of the built-in OTHER_GROUPS resource group are as follows:

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

     • MAX_IOPS = 100

     • WEIGHT_IOPS = 100

   Here are some examples:

   • Create a matching rule for SQL-level resource isolation.

     • Specify that the SQL statements executed by user tp_user with a WHERE clause that includes sys.t.c3 = 3 be executed in the big_group resource group and use the CPU and IOPS resources limited for this resource group.

       Notice

       When executing an SQL statement, it is not necessary for the statement to contain sys.t. in it. The statement will be bound to the big_group resource group as long as c3 is resolved to sys.t.c3. For 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 the SQL statements executed with a WHERE clause that includes t.c3=5 be executed in the small_group resource group and use the CPU and IOPS resources limited for this resource group.

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

     OceanBase Database also supports the use of hints to bind an SQL statement to a resource group. You can use hints to submit the SQL statement to the specified resource group for execution. For example, if you want to bind the SQL statement SELECT * FROM T to the big_group resource group, you can use the following hint:

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

     Note

     If you specify a resource group by using a hint and the specified resource group does not exist, the statement will be automatically executed in the default resource group OTHER_GROUPS.

   • Create a matching rule for User-level resource isolation.

     • Specify that the SQL statements executed by user tp_user be executed in the big_group resource group and use the CPU and IOPS resources limited for this resource 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 user ap_user be executed in the small_group resource group and use the CPU and IOPS resources limited for this resource group.

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

   • Create a matching rule for Function-level resource isolation.

     • Specify that the task of ha_high be executed in the big_group resource group and use the CPU and IOPS resources limited for this resource group.

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

     • Specify that the task of ddl_high be executed in the small_group resource group and use the CPU and IOPS resources limited for this resource 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 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 need to limit resources, you can use the ALTER SYSTEM SET 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. They are expected 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 only take effect in statements such as SELECT, INSERT, UPDATE, and DELETE. They do not take effect in DDL and DCL statements or 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 SQL parsing, 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 determine the resource group to use before SQL parsing, SQL-level resource isolation can only determine the resource group to use after SQL parsing or when a plan cache hit occurs. If the resource group determined for the SQL 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 divided 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, assuming the rule specifies the big_group resource group, the SQL will be executed using the big_group resources. The next SQL statement will also be executed using the big_group resources until it matches a rule that specifies a different resource group, at which point the system will retry. For a batch of continuously executed SQL statements all bound to the same resource group, using this strategy ensures that only the first SQL statement in the batch requires a retry, while subsequent SQL statements do not. This minimizes retries, resulting in minimal performance impact.

  3. If each SQL statement is expected to use a different resource group from the previous one, each SQL statement will require a retry, leading to significant performance impact.

References

• Overview of resource isolation

• Disk performance calibration

• Resource isolation for background tasks