How MySQL Uses Resource Groups to Manage CPU

MySQL manages CPU resources for specific threads through a feature called Resource Groups. This article provides an overview of how MySQL administrators can create, configure, and assign resource groups to regulate the CPU affinity and thread priority of different database workloads, ensuring critical queries receive the necessary processing power while background tasks are constrained.

Understanding MySQL Resource Groups

Resource groups allow for the isolation and prioritization of workloads within a MySQL instance. By default, MySQL comes with two global resource groups: * SYS_default: Used for system threads (e.g., background page flushing, master threads). * USR_default: Used for user connection threads.

Administrators can define custom resource groups to segregate traffic, such as separating high-priority transactional queries from low-priority analytical reports.

Key Attributes of Resource Groups

Each resource group is defined by two primary attributes that dictate how the operating system schedules its threads:

  1. VCPU (Virtual CPU) Affinity: This determines which CPU cores the threads in the resource group can execute on. It is specified as a list or range of CPU IDs (e.g., VCPU = 0-3 or VCPU = 0,2).
  2. Thread Priority: This determines the execution priority of the threads. The priority range depends on the group type:
    • SYSTEM groups: Priority ranges from -20 (highest priority) to 0.
    • USER groups: Priority ranges from 0 to 19 (lowest priority).

Creating and Managing Resource Groups

You can manage resource groups using standard SQL statements. The database administrator must have the RESOURCE_GROUP_ADMIN privilege to execute these commands.

To create a resource group that restricts user queries to specific CPU cores with a specific priority, use the CREATE RESOURCE GROUP statement:

CREATE RESOURCE GROUP batch_processing
  TYPE = USER
  VCPU = 4-7
  THREAD_PRIORITY = 10;

To modify an existing resource group, use the ALTER RESOURCE GROUP statement:

ALTER RESOURCE GROUP batch_processing
  VCPU = 4-5
  THREAD_PRIORITY = 15;

To delete a group, use DROP RESOURCE GROUP:

DROP RESOURCE GROUP batch_processing;

Assigning Threads to Resource Groups

Once a resource group is created, MySQL threads can be assigned to it in three ways:

1. Assigning an Active Session Thread

You can assign a specific connection’s thread ID to a resource group using the SET RESOURCE GROUP statement:

SET RESOURCE GROUP batch_processing FOR 123;

(Where 123 is the thread ID obtained from the Performance Schema or SHOW PROCESSLIST tables).

2. Assigning the Current Session

To assign your current active database session to a resource group:

SET RESOURCE GROUP batch_processing;

3. Using Optimizer Hints for Specific Queries

For granular control, you can apply an optimizer hint to execute a single query within a specific resource group. This is highly effective for heavy reporting queries that should not impact the main application:

SELECT /*+ RESOURCE_GROUP(batch_processing) */ customer_id, SUM(amount)
FROM sales
GROUP BY customer_id;

How MySQL Implements Resource Allocation Under the Hood

MySQL relies on the underlying operating system’s thread-scheduling APIs to enforce resource group attributes.

On Linux platforms, MySQL maps the VCPU attribute to the thread CPU affinity mask using the sched_setaffinity() system call. The THREAD_PRIORITY attribute is mapped to the thread “nice” value using the setpriority() system call.

Because adjusting thread priorities and affinities requires specific operating system privileges, the MySQL system user must have the CAP_SYS_NICE capability enabled on Linux systems to fully utilize these scheduling features.