Resource Groups in MySQL 8
System and user threads running in a MySQL server often compete for resources such as CPU, RAM, and storage. System threads have a higher priority than user threads by default as these run the server itself. Resource groups are used to define a group for a certain type of threads (SYSTEM or USER), and allocate the resources that threads in a particular group may consume, as a result prioritizing the different groups of threads.
What Resources Can Be Managed?
Only the CPU resource is supported. The CPU resource is called, “virtual CPU” and includes hyperthreads and CPU cores. A DBA may associate the available CPUs with resource groups and allocate threads to these groups.
What Resource Attributes Can Be Set?
Resource group attributes determine what aspects of a resource group can be set. The following attributes can be set:
- Resource group name: Identifies a resource group and cannot be modified.
- Resource group type: Specifies a resource group of type SYSTEM or USER. SYSTEM type resource groups include system threads with higher priority that typically run in the background. USER type resource groups include user threads with lower than system priority that typically run in the foreground. The group type cannot be modified after creating a group.
- CPU Affinity: A subset of the available virtual CPUs that a particular resource group can use. If no CPU affinity is set, a resource group can use any available virtual CPU. CPU affinity can be modified after creating a group.
- Thread priority: A value in the range of -19 to 20 that specifies the thread run priority. The lower the value, the higher the priority; the default priority is 0 for both system and user threads. System threads can have thread priority in the range -19 to 0, and the user threads in the range 0 to 20. System threads always have higher priority than user threads. The thread priority can be modified after resource creation.
- Enabled/Disabled: A resource group can be enabled implying threads can be assigned to it, or disabled implying threads cannot be assigned to it. The ENABLED/DISABLED attribute can be modified after resource group creation.
Creating a Resource Group
Two resource groups are available by default: SYS_default for system threads and USR_default for user threads. These resource groups cannot be dropped, and have a fixed attribute value of 0 for thread priority and no CPU affinity. New threads that are not assigned to specific user-defined resource groups are assigned to one of these groups based on whether they are system or user threads.
A user can create a new resource group for user threads with the CREATE USER GROUP SQL statement, for example:
CREATE RESOURCE GROUP User_new TYPE = USER VCPU = 5-10 THREAD_PRIORITY = 1;
The SQL creates a new resource group called User_new of type USER with a thread priority of 1. The VCPU IDs 5-10 can be used by the group. The resource group is ENABLED by default. A user can create a new resource group for system threads with the CREATE USER GROUP SQL statement, for example:
CREATE RESOURCE GROUP System_new TYPE = SYSTEM VCPU = 1-4, 10 THREAD_PRIORITY = -19 DISABLE;
The SQL creates a new resource group called System_new of type SYSTEM with thread priority of -19. The VCPU IDs 1-4 and 10 can be used by the group. The resource group is disabled when created.
The information about resource groups is stored in the RESOURCE_GROUPS table in the INFORMATION_SCHEMA.
Setting a Resource Group
A thread, identified by a thread id, can be assigned to a resource group with the SET RESOURCE GROUP SQL statement. A user thread can be assigned only to a resource group of type USER, and a system thread to a group of type SYSTEM. Multiple threads of the same type can be assigned in a single statement, for example:
SET RESOURCE GROUP User_new 6,7,9;
Modifying and Dropping a Resource Group
The ALTER RESOURCE GROUP SQL statement can be used to alter a resource group. The attributes that can be modified include CPU affinity, thread priority and whether a resource group is ENABLED/DISABLED. For example:
ALTER RESOURCE GROUP System_new VCPU = 5,7,10 THREAD_PRIORITY = -10 ENABLE;
The DROP USER GROUP statement can be used to drop a resource group. A resource group that has running threads in it cannot be dropped unless the FORCE clause is included, for example:
DROP RESOURCE GROUP User_new FORCE.
Restrictions Based on OS
Resource groups have some restrictions based on the operating system used.
- On macOS, resource groups are not available
- On Solaris and FreeBSD, thread priority setting is ignored, and all threads run on the default priority of 0
- On Linux, thread priorities are ignored unless CAP_YSTEM_NICE capability is set
The concept of resources management is relatively new and presently supports only the CPU time as a manageable resource, and also with OS-specific restrictions. In the future, other resources that could likely be managed include memory (RAM) and disk storage.
Great article on MySQL 8's Resource Groups! This feature allows for better management of server resources and can improve application performance. Thanks for sharing this informative guide.