You can bind the SQL statements that meet a specified condition to a specified resource group to implement SQL statement-level resource isolation. This topic describes how to configure SQL statement-level resource isolation in MySQL mode.
Prerequisites
Before you configure SQL statement-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, database, table, and column for which resource isolation is to be implemented have been created.
Procedure
The following example shows how to bind different SQL conditions to the interactive_group and batch_group resource groups, and control the CPU and IOPS resources available for different SQL statements by using the daytime resource management plan.
Log on to a MySQL tenant of the cluster as the administrator of the tenant.
Call the
CREATE_CONSUMER_GROUPsubprogram in theDBMS_RESOURCE_MANAGERpackage 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' );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
oceanbase.DBA_RSRC_CONSUMER_GROUPSview to verify whether the resource groups are created. For more information about theoceanbase.DBA_RSRC_CONSUMER_GROUPSview, see oceanbase.DBA_RSRC_CONSUMER_GROUPS.Call the
CREATE_PLANsubprogram in theDBMS_RESOURCE_MANAGERpackage to create a resource management plan.obclient [test]> CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN( PLAN => 'daytime', COMMENT => 'TP preferred');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
oceanbase.DBA_RSRC_PLANSview to verify whether the resource management plan is created. For more information about theoceanbase.DBA_RSRC_PLANSview, see oceanbase.DBA_RSRC_PLANS.Call the
CREATE_PLAN_DIRECTIVEsubprogram in theDBMS_RESOURCE_MANAGERpackage 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);The parameters are described as follows:
PLAN: the name of the resource management plan.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 value70indicates that at most 70% 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
oceanbase.DBA_RSRC_PLAN_DIRECTIVESoroceanbase.DBA_OB_RSRC_IO_DIRECTIVESview to verify whether the resource management plan config is created.For more information about the
oceanbase.DBA_RSRC_PLAN_DIRECTIVESview, see oceanbase.DBA_RSRC_PLAN_DIRECTIVES.For more information about the
oceanbase.DBA_OB_RSRC_IO_DIRECTIVESview, see oceanbase.DBA_OB_RSRC_IO_DIRECTIVES.Call the
SET_CONSUMER_GROUP_MAPPINGsubprogram in theDBMS_RESOURCE_MANAGERpackage to create a matching rule for resource isolation.obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'column', VALUE => 'test.t.c3=3 for tp_user', CONSUMER_GROUP => 'batch_group'); obclient [test]> CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( ATTRIBUTE => 'column', VALUE => 't.c3=5', CONSUMER_GROUP => 'interactive_group');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 includes the database name, table name, column name, constant value, and username.Take note of the following items:
The database name and username are optional. The default database name is the name of the current database. If no username is specified, the settings take effect for all users, including those created later in the current tenant.
The table name, column name, and constant value are required, and each of them can have only one value. The constant value must be a number or string.
When you specify the table name, column name, and username, the specified table, column, and user must exist.
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.
In this example, after the preceding matching rule for resource isolation is added, when the
tp_useruser executes an SQL statement with aWHEREclause that containstest.t.c3 = 3, this SQL statement will be bound to the resource group namedbatch_groupfor execution.Notice
An SQL statement can be bound to the
batch_groupresource group provided thatc3is parsed intotest.t.c3but the statement does not necessarily need to containtest.t.. For example, the statementSELECT * FROM test.t WHERE c3 = 1;will be bound to the `batch_group` resource group.You can query the
oceanbase.DBA_RSRC_GROUP_MAPPINGSview to verify whether the matching rule is created. For more information aboutoceanbase.DBA_RSRC_GROUP_MAPPINGS, see oceanbase.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 [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.
SQL statement-level resource isolation has a higher priority than user-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
The impact of SQL statement-level resource isolation on performance is caused by retries. In user-level resource isolation, the resource group used for executing an SQL statement is determined before the SQL statement is parsed. However, in SQL statement-level resource isolation, the resource group used for executing an SQL statement is determined when the SQL statement is parsed or hits the plan cache. If the system detects that the resource group being used is not the determined resource group, the system will perform a retry to use the resources in the resource group determined based on the matching rule to execute this SQL statement.
The impact of SQL statement-level resource isolation falls into three cases:
If an SQL statement does not hit any matching rule, SQL statement-level resource isolation has no impact on the performance.
If an SQL statement hits a matching rule that specifies to use the
batch_groupresource group, this SQL statement is executed by using resources in thebatch_groupresource group. The next SQL statement is also preferentially executed by using resources in this resource group. When the system detects that the matching rule hit by another SQL statement is bound to a different resource group, it will perform a retry to use resources in the new resource group to execute this statement. To continuously execute a batch of SQL statements that are bound to the same resource group, you can use this strategy so that the system needs to retry only the first SQL statement. This reduces the number of retries and causes slight impacts on the performance.If the expected resource group of each SQL statement is different from that of the previous statement, the system must retry for each SQL statement. This greatly affects the performance.
