Configure user-level resource isolation

You can bind the SQL statements executed by a specific user to a specified resource group to implement user-level resource isolation. This topic describes how to configure user-level resource isolation in MySQL mode.

Prerequisites

• Before you configure user-level resource isolation, we recommend that you learn about basic concepts such as resource group, resource management plan, and resource management plan config. For more information, see Overview.

• If you need to implement isolation of CPU resources, make sure that the control group (cgroup) directory is configured and the cgroup feature is enabled. This is because the isolation of CPU resources depends on cgroups. For more information, see Configure cgroups.

  If you need to implement only isolation of input/output operations per second (IOPS) resources, you do not need to configure cgroups.

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

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

• The user for which resource isolation is to be implemented has been created. For more information, see Create a user.

Background information

User-level resource isolation is further categorized into user-based resource isolation and function-based resource isolation. For more information, see Overview.

Procedure

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

Before you configure a resource isolation plan after disk calibration, you must make sure that the MAX_IOPS and MIN_IOPS parameters in the unit config of the tenant are set to valid values that are not greater than the baseline IOPS value for 16 KB reads.

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

2. Execute the following statement to query the unit config of the tenant for which resource isolation is to be performed:

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

   The 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 default value INT64_MAX (which is 9223372036854775807) is used for the MAX_IOPS and MIN_IOPS parameters of the tenant, you must replan the IOPS resources for 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 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 baseline IOPS value of the disk on each OBServer node where the tenant is deployed. If the queried baseline IOPS value is less the baseline IOPS value for 16 KB reads, set the queried baseline IOPS value as the upper IOPS limit of the node; otherwise, set the baseline IOPS value for 16 KB reads as the upper IOPS limit of the node.

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

   The 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
   

   Plan the IOPS resources available for the tenant by using the queried disk calibration value of each node as the upper IOPS limit. Multiple tenants in a cluster may be deployed on the same OBServer nodes. You can allocate the IOPS resources based on the actual situation.

   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 business needs.

5. Execute the following statements to modify the values of MAX_IOPS and MIN_IOPS.

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

   ALTER RESOURCE UNIT unit_name MIN_IOPS = xxx;
   

   ALTER RESOURCE UNIT unit_name MAX_IOPS = xxx;
   

Step 2: Configure a resource isolation plan

The following example shows how to respectively bind the tp_user and ap_user users or the compaction_high and ddl_high tasks to the interactive_group and batch_group resource groups, and control the CPU and IOPS resources available for the users or backend tasks by using the resource management plan daytime.

1. Log on to a MySQL tenant of the cluster as the administrator of the tenant.

2. Call the CREATE_CONSUMER_GROUP subprogram in the DBMS_RESOURCE_MANAGER package to create two resource groups.

   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'
   );
   

   where

   • CONSUMER_GROUP specifies the name of the resource group.

   • COMMENT specifies the comments on the resource group.

   You can query the oceanbase.DBA_RSRC_CONSUMER_GROUPS view to verify whether the resource groups are created. 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 package to create a resource management plan.

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

   where

   • PLAN specifies the name of the resource management plan.

   • COMMENT specifies the comments on the resource management plan.

   You can query the oceanbase.DBA_RSRC_PLANS view to verify whether the resource management plan is created. 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 package to create a resource management plan config. to limit the CPU and IOPS resources available for the resource group when the resource management plan is enabled.

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

   where

   • PLAN specifies the name of the resource management plan.

   • GROUP_OR_SUBPLAN specifies the resource group.

   • COMMENT specifies the comments on the resource management plan config. Default value: NULL.

   • MGMT_P1 specifies the percentage of CPU resources available for the resource group when the system runs at full load. Default value: 100.

   • UTILIZATION_LIMIT specifies 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 specifies 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. Default value: 0.

   • MAX_IOPS specifies the maximum IOPS resources available for the resource group. The sum of MAX_IOPS values of all resource groups can exceed 100. Default value: 100.

   • WEIGHT_IOPS specifies the weight for IOPS resources. The sum of WEIGHT_IOPS values of all resource groups can exceed 100. Default value: 0.

   You can query the oceanbase.DBA_RSRC_PLAN_DIRECTIVES or oceanbase.DBA_OB_RSRC_IO_DIRECTIVES view to verify whether the resource management plan config is created.

   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 package to create a matching rule for user-level resource isolation based on the actual scenario.

   • Create a matching rule for user-level user-based resource isolation.

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

     where

     • ATTRIBUTE specifies the attribute type. The value column indicates SQL statement-level resource isolation. The value user indicates user-level user-based resource isolation. The value function indicates user-level function-based resource isolation. The attribute name is case-insensitive.

     • VALUE specifies the attribute value, which is a username. At present, you can specify only one username.

     • CONSUMER_GROUP specifies the resource group to bind. When an SQL statement hits the matching rule specified by the VALUE parameter, this statement is bound to the specified resource group for execution. At present, an SQL statement can be bound to only one resource group.

       If no resource group is specified, the built-in resource group other group is bound by default. The resources of the built-in resource group other group are as follows:

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

       • MAX_IOPS = 100

       • WEIGHT_IOPS = 100

   • Create a matching rule for user-level function-based resource isolation.

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

     where

     • ATTRIBUTE specifies the attribute type. The value column indicates SQL statement-level resource isolation. The value user indicates user-level user-based resource isolation. The value function indicates user-level function-based resource isolation. The attribute name is case-insensitive.

     • VALUE specifies the attribute value, which can be any of the following eight types of backend tasks of the directed acyclic graph (DAG) thread: compaction_high, ha_high, compaction_mid, ha_mid, compaction_low, ha_low, ddl, and ddl_high. At present, you can specify only one task.

     • CONSUMER_GROUP specifies the resource group to bind. When an SQL statement hits the matching rule specified by the VALUE parameter, this statement is bound to the specified resource group for execution. At present, an SQL statement can be bound to only one resource group.

       If no resource group is specified, the built-in resource group other group is bound by default. The resources of the built-in resource group other group are as follows:

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

       • MAX_IOPS = 100

       • WEIGHT_IOPS = 100

   You can query the oceanbase.DBA_RSRC_GROUP_MAPPINGS view to verify whether the matching rule is created. For more information about oceanbase.DBA_RSRC_GROUP_MAPPINGS, see oceanbase.DBA_RSRC_GROUP_MAPPINGS.

6. Enable a proper resource management plan for a resource group.

   The resources available for a resource group vary based on the resource management plan. Therefore, you must enable a proper resource management plan for a resource group.

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

   Note

   If resource usage does not need to be limited, you can execute the SET GLOBAL resource_manager_plan = ''; statement to disable all resource management plans.

Considerations

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

• A matching rule for resource isolation does not take effect immediately after being added, but is expected to take effect within 10 seconds. The time may vary based on the actual environment.

• User-level resource isolation has a lower priority than SQL statement-level resource isolation.

• After a matching rule for resource isolation is added, it takes effect only in the SELECT, INSERT, UPDATE, and DELETE statements, and does not take effect in data definition language (DDL), data control language (DCL), or procedural language (PL) statements. It can take effect in prepared statements.

Impact on performance

After user-level resource isolation is enabled, the resource group used for executing an SQL statement is determined before the SQL statement is parsed. Therefore, the performance is not affected after user-level resource isolation is configured.

References

Configure SQL statement-level resource isolation