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 Oracle 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
The following example shows how to respectively bind the tp_user and ap_user users or the ha_high and ddl_high tasks to the big_group and small_group resource groups, and control the CPU and IOPS resources available for the users or backend tasks by using the plan_a resource management plan.
Log on to an Oracle tenant in the cluster as the administrator of the tenant.
Call the
CREATE_CONSUMER_GROUPsubprogram in theDBMS_RESOURCE_MANAGERpackage to create two resource groups.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; //The parameters are described as follows:
CONSUMER_GROUP: the name of the resource group.COMMENT: the comments on the resource group.
You can query the
DBA_RSRC_CONSUMER_GROUPSview to verify whether the resource groups are created. For more information about theDBA_RSRC_CONSUMER_GROUPSview, see DBA_RSRC_CONSUMER_GROUPS.Call the
CREATE_PLANsubprogram in theDBMS_RESOURCE_MANAGERpackage to create a resource management plan.obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN( PLAN => 'plan_a'); END; //The parameters are described as follows:
PLAN: the name of the resource management plan.COMMENT: the comments on the resource management plan.
You can query the
DBA_RSRC_PLANSview to verify whether the resource management plan is created. For more information about theDBA_RSRC_PLANSview, see DBA_RSRC_PLANS.Call the
CREATE_PLAN_DIRECTIVEsubprogram in theDBMS_RESOURCE_MANAGERpackage to create a resource management plan config for the resource management plan. When the resource management plan is enabled, the resource management plan config limits the CPU and IOPS resources available for the resource group.obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'plan_a', GROUP_OR_SUBPLAN => 'big_group', COMMENT => 'TP preferred', MGMT_P1 => 90, UTILIZATION_LIMIT =>60, MIN_IOPS => 20, MAX_IOPS => 100, WEIGHT_IOPS => 20); END; // obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'plan_a', GROUP_OR_SUBPLAN => 'small_group' , COMMENT => 'AP preferred', MGMT_P1 => 100, UTILIZATION_LIMIT =>40, MIN_IOPS => 10, MAX_IOPS => 90, WEIGHT_IOPS => 30); END; //The parameters are described as follows:
PLAN: the name of the resource management plan with which the resource management plan config is associated.GROUP_OR_SUBPLAN: the resource group.COMMENT: the comments on the resource management plan config. Default value:NULL.MGMT_P1: the percentage of CPU resources available for the resource group when the system runs at full load. Default value:100.UTILIZATION_LIMIT: the upper limit on the CPU resources available for the resource group. The default value is100. The value range is [0, 100]. The value100indicates that all CPU resources of the tenant are available for the resource group. The value40indicates that at most 40% of the CPU resources of the tenant are available for the resource group.MIN_IOPS: the IOPS resources reserved for the resource group in the case of I/O resource contention. The sum ofMIN_IOPSvalues of all resource groups cannot exceed 100. Default value:0.MAX_IOPS: the maximum IOPS resources available for the resource group. The sum ofMAX_IOPSvalues of all resource groups can exceed 100. Default value:100.WEIGHT_IOPS: the weight for IOPS resources. The sum ofWEIGHT_IOPSvalues of all resource groups can exceed 100. Default value:0.
You can query the
DBA_RSRC_PLAN_DIRECTIVESorDBA_OB_RSRC_IO_DIRECTIVESview to verify whether the resource management plan config is created.For more information about the
DBA_RSRC_PLAN_DIRECTIVESview, see DBA_RSRC_PLAN_DIRECTIVES.For more information about the
DBA_OB_RSRC_IO_DIRECTIVESview, see DBA_OB_RSRC_IO_DIRECTIVES.Call the
SET_CONSUMER_GROUP_MAPPINGsubprogram in theDBMS_RESOURCE_MANAGERpackage 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 [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'user', VALUE => 'tp_user', CONSUMER_GROUP => 'big_group'); END; // obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'user', VALUE => 'ap_user', CONSUMER_GROUP => 'small_group'); END; // obclient [SYS]>delimiter ;The parameters are described as follows:
ATTRIBUTE: the attribute type. The valuecolumnindicates SQL statement-level resource isolation. The valueuserindicates user-level user-based resource isolation. The valuefunctionindicates user-level function-based resource isolation. The attribute name is case-insensitive.VALUE: the attribute value, which is a username. At present, you can specify only one username.In the Oracle mode of OceanBase Database, the username will be automatically converted to uppercase after a statement is executed. You can enclose the username with double quotation marks to prevent it from being converted to uppercase. Here is an example:
obclient [SYS]> DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'user', VALUE => '"user1"' CONSUMER_GROUP => 'big_group');CONSUMER_GROUP: the resource group to bind. When an SQL statement hits the matching rule specified by theVALUEparameter, this statement is bound to the specified resource group for execution. At present, an SQL statement can be bound to only one resource group.
Create a matching rule for user-level function-based resource isolation.
obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'function', VALUE => 'ha_high', CONSUMER_GROUP => 'big_group'); END; // obclient [SYS]> BEGIN DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'function', VALUE => 'ddl_high', CONSUMER_GROUP => 'small_group'); END; // obclient [SYS]>delimiter ;The parameters are described as follows:
ATTRIBUTE: the attribute type. The valuecolumnindicates SQL statement-level resource isolation. The valueuserindicates user-level user-based resource isolation. The valuefunctionindicates user-level function-based resource isolation. The attribute name is case-insensitive.VALUE: 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: the resource group to bind. When an SQL statement hits the matching rule specified by theVALUEparameter, this statement is bound to the specified resource group for execution. At present, an SQL statement can be bound to only one resource group.
You can query the
DBA_RSRC_GROUP_MAPPINGSview to verify whether the matching rule is created. For more information aboutDBA_RSRC_GROUP_MAPPINGS, see DBA_RSRC_GROUP_MAPPINGS.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 [SYS]> ALTER SYSTEM SET resource_manager_plan = 'plan_a';Note
If resource usage does not need to be limited, you can execute the
ALTER SYSTEM SET resource_manager_plan = '';statement to disable all resource management plans.
Considerations
After a matching rule for 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, andDELETEstatements, 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.