MySQL 8.0 Resource Groups: Configuration, Usage, and Limitations

MySQL 8.0 introduces Resource Groups (RG), a feature that allows administrators to assign specific CPU cores and thread priorities to MySQL internal threads, thereby preventing a single heavy query from monopolizing CPU resources.

Why Resource Groups?

Long‑running or poorly written SQL statements can consume excessive CPU, causing other queries to stall or even crashing the server. Traditional mitigations include setting max_execution_time or periodically killing offending threads, both of which have drawbacks.

RG solves this by letting you place resource‑intensive tasks into dedicated groups with limited CPU access, ensuring they do not affect the overall service quality.

Test Sample – CPU Bomb Procedure

The following stored procedure creates a CPU‑intensive loop for testing RG behavior:

mysql> DELIMITER //
mysql> CREATE PROCEDURE bomb(OUT ot BIGINT)
    -> BEGIN
    ->   DECLARE cnt BIGINT DEFAULT 0;
    ->   SET @FUTURE = (SELECT NOW() + INTERVAL 120 SECOND);
    ->   WHILE NOW() < @FUTURE DO
    ->     SET cnt = (SELECT cnt + 1);
    ->   END WHILE;
    ->   SELECT cnt INTO ot;
    -> END //
mysql> DELIMITER ;

The procedure runs for two minutes, repeatedly executes SELECT +1 , and returns the total iteration count via an OUT parameter.

How to Use Resource Groups

Note: RG is not available when MySQL is compiled with the thread‑pool feature.

RG definitions are stored in information_schema.resource_groups . You can list existing groups with:

mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\G

Two default groups exist: URS_default (user threads) and SYS_default (system threads).

Creating a custom group and binding the current session:

CREATE RESOURCE GROUP rg TYPE = USER VCPU = 1;
SET RESOURCE GROUP rg;

Permissions required:

CREATE/ALTER/DROP RG – RESOURCE_GROUP_ADMIN

Assign a session to a group – RESOURCE_GROUP_USER

Environment Requirements & Configuration

On Linux, the MySQL binary must have the CAP_SYS_NICE capability to set thread priority. With systemd, add the following to the service file:

[Service]
AmbientCapabilities=CAP_SYS_NICE

After editing, restart MySQL with systemctl restart mysqld . Without this capability, attempts to set THREAD_PRIORITY succeed silently but generate warnings.

Implementation Details

RG uses the Linux sched_setaffinity system call to bind threads to CPUs, similar to the taskset utility. The core code resides in sql/resourcegroups/platform/thread_attrs_api_linux.cc :

cpu_set_t cpu_set;
CPU_ZERO(&cpu_set);
for (const auto &cpu_id : cpu_ids) CPU_SET(cpu_id, &cpu_set);
int rc = ::sched_setaffinity(thread_id, sizeof(cpu_set), &cpu_set);
if (rc != 0) {
  char errbuf[MYSQL_ERRMSG_SIZE];
  LogErr(ERROR_LEVEL, ER_RES_GRP_SET_THR_AFFINITY_FAILED, thread_id,
         my_errno(), my_strerror(errbuf, MYSQL_ERRMSG_SIZE, my_errno()));
  return true;
}
return false;

Known Limitations

RG currently controls only CPU affinity and thread priority; other resources (IO, memory) are not supported.

In containerized environments (e.g., LXC) where the host limits visible CPUs, RG may fail to bind because MySQL sees only the limited set of VCPU IDs.

Thread‑pool enabled MySQL cannot use RG at all.

Example of a failed bind inside a container:

CREATE RESOURCE GROUP rg TYPE = USER VCPU = 1;
SET RESOURCE GROUP rg;
-- ERROR 3661: Unable to bind resource group rg with thread id (308485).

The failure occurs because the container does not expose the requested CPU to MySQL.

Workaround for Containers

MySQL determines the number of usable CPUs via pthread_getaffinity_np . By recompiling MySQL after disabling the check in configure.cmake (comment out CHECK_FUNCTION_EXISTS(pthread_getaffinity_np HAVE_PTHREAD_GETAFFINITY_NP) ), you can force MySQL to recognize all host CPUs.

CREATE RESOURCE GROUP rg TYPE = USER VCPU = 10;  -- succeeds after recompilation
SET RESOURCE GROUP rg;                         -- may still fail if CPU 10 is not allocated
ALTER RESOURCE GROUP rg VCPU = 3,4,7,8;
SET RESOURCE GROUP rg;                         -- succeeds when IDs match host allocation

Use Cases

Implement circuit‑breaker mechanisms by giving system threads higher priority or dedicated CPUs to avoid crashes under load.

Assign higher priority to monitoring sessions and lower priority to batch jobs, ensuring critical health checks remain responsive.

Bind log writer/flush threads to specific CPUs with higher priority, which can noticeably improve QPS in certain workloads.

References

8.12.5 Resource Groups – https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html

Resource Groups Restrictions – https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html#resource-group-restrictions

sched_setaffinity man page – https://linux.die.net/man/2/sched_setaffinity

taskset source – https://github.com/karelzak/util-linux/blob/master/schedutils/taskset.c