MySQL - SET RESOURCE GROUP Statement

In MySQL you can create resource groups and assign threads to these groups. These threads execute based on the availability of the resources in the group i.e. the group attributes controls the resources and the threads in the group are restricted to the available resources.

MySQL provides various statements to create, alter, drop a group resource and assign threads to a group resource.

SET RESOURCE GROUP Statement

You can set/assign a thread to an existing MySQL resource group, using the SET RESOURCE GROUP Statement. You need RESOURCE_GROUP_ADMIN privileges to execute the statement.

Syntax

Following is the syntax of the MySQL SET RESOURCE GROUP Statement −

SET RESOURCE GROUP group_name
   [FOR threadID1, threadID2, threadID3, . . . . . . .]

Where, group_name is the name of the resource group to which you need to assign the specified threads and threadID1, threadID2, threadID3 are the threads to assign to the group.

If you haven’t specified the thread IDs using the FOR queries. The current thread is assigned to the specified resource group.

Example

Assume we have created a resource group using the CREATE RESOURCE GROUP statement as shown below −

CREATE RESOURCE GROUP test_group TYPE = USER 
VCPU = 0 THREAD_PRIORITY = 2 ENABLE;

Following query assigns the above created resource group to the current thread.

SET RESOURCE GROUP test_group;

If you try to assign a resource group that doesn’t exist an error will be generated as shown below −

SET RESOURCE GROUP none;
ERROR 3651 (HY000): Resource Group 'none' does not exist.

The For clause

You can specify the threads to be assigned to the given resource group using the ZFOR clause.

Assume we have created another resource group as shown below −

CREATE RESOURCE GROUP test TYPE = USER THREAD_PRIORITY = 15;

Following query sets specific threads to the above created resource group −

SET RESOURCE GROUP test FOR 21, 15, 6;