MySQL - CREATE 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.

CREATE RESOURCE GROUP Statement

You can create a resource group using the CREATE RESOURCE GROUP Statement, it also assigns the initial attribute values of the created resource group. You need RESOURCE_GROUP_ADMIN privileges to execute the statement.

Syntax

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

CREATE RESOURCE GROUP group_name
   TYPE = {SYSTEM|USER}
   [VCPU [=] vcpu_spec [, vcpu_spec] ...]
   [THREAD_PRIORITY [=] N]
   [ENABLE|DISABLE]

Where, group_name is the name of the group you need to create. And type is the is used to specify whether the resource group should be system resource group or user resource group.

Example

Following query creates a resource group with name sample_group −

CREATE RESOURCE GROUP sample_group TYPE = USER;

If you try to create a resource group with a name that already exists it will generate an error

CREATE RESOURCE GROUP sample_group TYPE = USER;
ERROR 3650 (HY000): Resource Group 'sample_group' exists

The VPU attribute

The VPU attribute of the CREATE RESOURCE GROUP Statement is used to set the CPU affinity for the resource group. If you haven’t passed this value then the group uses all available CPUs.

CREATE RESOURCE GROUP sample_group1 TYPE = USER VCPU = 0,1,2,3;

The thread priority attribute

The thread priority attribute is used to set the priority of the thread to be created, if you haven’t passed this value, the default value is set to 0. The priority range for a thread in a resource group is -20 to 19.

CREATE RESOURCE GROUP sample_group2 TYPE = USER 
VCPU = 0 THREAD_PRIORITY = 2;

The ENABLE attribute

If you use the ENABLE (attribute value) in the query the resource group is initially enabled in the same way if you specify DISABLE in the statement the resource group is initially disabled.

CREATE RESOURCE GROUP sample_group2 TYPE = USER 
VCPU = 0 THREAD_PRIORITY = 2 DISABLE;