Using MySQL 8.0 Resource Groups to Isolate Long‑Running Queries

Before MySQL 8.0, a poorly written query like select * from t1 order by rand(); can spawn many threads, saturate the CPU and block other requests. The article first mentions two generic mitigation methods: setting max_execution_time (which may kill legitimate long queries) and writing a custom script that kills queries exceeding a time threshold.

MySQL 8.0 introduces resource groups (RG) , which allow administrators to bind a query or a set of threads to specific CPU cores, thereby isolating heavy queries without affecting other traffic.

Creating a resource group

The command syntax is demonstrated as follows:

create resource group user_ytt type = user vcpu = 0-1 thread_priority=19 enable;

Explanation of parameters:

type = user creates a user‑level thread group (foreground requests). Use type = system for internal MySQL threads.

vcpu = 0-1 binds the group to logical CPUs 0 and 1.

thread_priority sets the scheduling priority; values greater than 0 give higher priority to user threads.

After creation, the group can be inspected via the information_schema.resource_groups table:

select * from information_schema.resource_groups;

To assign a running query to the group, first locate its thread ID:

show processlist;

select thread_id from performance_schema.threads where processlist_id = 240;

Then bind the thread to the group:

set resource group user_ytt for 278;

If the operation succeeds, the query continues running under the CPU limits of the group.

Developers can also use the MySQL hint syntax to bind a single statement to a resource group without manual thread handling:

select /*+ resource_group(user_ytt) */ guid from t1 group by left(guid,8) order by rand();

Limitations of resource groups

On Linux, the CAP_SYS_NICE capability must be granted to the MySQL service (e.g., via AmbientCapabilities=CAP_SYS_NICE in a systemd drop‑in).

Resource groups become ineffective when MySQL thread pool is enabled.

Thread priority is not supported on FreeBSD and Solaris.

Currently only CPU binding is supported; other resources cannot be limited.

The article concludes with a brief community notice and a reminder to like or share the post.