MySQL 8.0 Resource Groups: Overview and Implementation

MySQL 8.0 adds a Resource Group feature that allows DBAs to create, configure, and assign MySQL threads to independent resource groups for finer‑grained control.

Each resource group has attributes such as Name, CPU affinity (VCPU list), OS thread priority (range [-19,20]), Group type (FOREGROUND or BACKGROUND), and an Enabled flag.

Two new privileges are introduced: RESOURCE_GROUP_ADMIN for creating, altering, and dropping groups, and RESOURCE_GROUP_USER for assigning threads to groups. By default, two groups are created at startup: USR_Default (user threads) and SYS_Default (system threads). The following SQL commands manage groups:

CREATE RESOURCE GROUP 'name' TYPE=SYSTEM|USER [VCPU=num|start-end[,num|start-end]*] [THREAD_PRIORITY=num] [ENABLE|DISABLE];

ALTER RESOURCE GROUP 'name' [VCPU=num|start-end[,num|start-end]*] [THREAD_PRIORITY=num] [ENABLE|DISABLE] [FORCE];

DROP RESOURCE GROUP 'name' [FORCE];

Groups can be inspected with SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS . Threads are assigned using SET RESOURCE GROUP 'name' FOR thread_id1, thread_id2, ... . Thread IDs are obtained from SELECT * FROM performance_schema.threads . Query hints also allow temporary assignment, e.g., /*+ RESOURCE_GROUP(resource_group_name) */ in SELECT/UPDATE/INSERT/REPLACE/DELETE statements.

Resource‑group operations are not recorded in the binary log, so they must be configured separately on master and replica.

Implementation Details

MySQL 8.0 refactors the command execution path by introducing new parser classes. The resource‑group related parser classes are:

PT_create_resource_group

PT_alter_resource_group

PT_drop_resource_group

PT_set_resource_group

These classes inherit from Parse_tree_root and perform basic syntax checks. Corresponding execution classes inherit from Sql_cmd :

Sql_cmd_create_resource_group

Sql_cmd_alter_resource_group

Sql_cmd_drop_resource_group

Sql_cmd_set_resource_group

Example of PT_create_resource_group :

class PT_create_resource_group final : public Parse_tree_root {
  resourcegroups::Sql_cmd_create_resource_group sql_cmd;
  const bool has_priority;
public:
  PT_create_resource_group(const LEX_CSTRING &name,
                           const resourcegroups::Type type,
                           const Mem_root_array<resourcegroups::Range> *cpu_list,
                           const Value_or_default<int> &opt_priority,
                           bool enabled)
    : sql_cmd(name, type, cpu_list,
              opt_priority.is_default ? 0 : opt_priority.value, enabled),
      has_priority(!opt_priority.is_default) {}
  Sql_cmd *make_cmd(THD *thd) override;
};

The make_cmd method validates the group name length, priority range, and sets thd->lex->sql_command to SQLCOM_CREATE_RESOURCE_GROUP .

Implementation of Sql_cmd_create_resource_group :

class Sql_cmd_create_resource_group : public Sql_cmd {
  friend class ::PT_create_resource_group;
public:
  Sql_cmd_create_resource_group(const LEX_CSTRING &name, const Type type,
                               const Mem_root_array<Range> *cpu_list,
                               int priority, bool enabled)
    : m_name(name), m_type(type), m_cpu_list(cpu_list),
      m_priority(priority), m_enabled(enabled) {}
  enum_sql_command sql_command_code() const override { return SQLCOM_CREATE_RESOURCE_GROUP; }
  bool execute(THD *thd) override;
private:
  const LEX_CSTRING m_name;
  const Type m_type;
  const Mem_root_array<Range> *m_cpu_list;
  int m_priority;
  bool m_enabled;
};

The execute method checks read‑only mode, verifies RESOURCE_GROUP_ADMIN privilege, validates the name and VCPU list, acquires necessary locks, ensures the group does not already exist, adds it to the in‑memory hash, and persists it via the data‑dictionary API.

Platform‑specific APIs

CPU affinity and thread‑priority operations are abstracted in sql/resourcegroups/platform . Relevant functions include:

// Bind current thread to a specific CPU
bind_to_cpu(cpu_id_t cpu_id);
// Bind a specific OS thread to a CPU
bind_to_cpu(cpu_id_t cpu_id, my_thread_os_id_t thread_id);
// Restrict current thread to a list of CPUs
bind_to_cpus(const std::vector<cpu_id_t> &cpu_ids);
// Unbind current thread from specific CPUs
unbind_thread();
// Get/Set thread priority
int thread_priority();
void set_thread_priority(int priority);
// Query number of VCPUs
int get_num_vcpus();
// Validate priority range
bool is_valid_thread_priority(int priority);

Runtime Components

The runtime resides in the resourcegroups namespace and consists of three main classes:

Resource_group_mgr : a singleton that manages all in‑memory resource groups, provides lookup, creation, deletion, and movement of threads between groups.

Resource_group : represents a single in‑memory group, holding its name, type, enabled flag, a Thread_resource_control object, and the set of associated PFS thread IDs.

Thread_resource_control : stores the CPU list and thread priority for a group and implements the actual binding and priority‑setting operations.

Performance Schema (PFS) Integration

PFS offers two services for resource groups:

pfs_resource_group : assigns threads to groups and retrieves system attributes. Functions include set_thread_resource_group , set_thread_resource_group_by_id , get_thread_system_attrs , and get_thread_system_attrs_by_id . The pfs_thread_attrs_t struct describes thread metadata.

pfs_notification : registers callbacks for events such as thread creation, destruction, session connect/disconnect, and user change.

Persistence Layer

Resource‑group metadata is persisted in the data‑dictionary ( dd ) namespace. Key APIs are resource_group_exists , create_resource_group , update_resource_group , and drop_resource_group . The dd::tables::Resource_groups table defines columns like ID, name, type, enabled flag, CPU mask, thread priority, and options. The concrete implementation class Resource_group_impl validates attributes and stores them in the dictionary.

Conclusion

The article provides both usage instructions and a deep dive into the internal implementation of MySQL's resource‑group feature, covering parser extensions, platform APIs, runtime management, PFS integration, and persistence. It notes that the current implementation only supports CPU affinity and thread priority, and hints at future extensions to control IOPS and memory for finer‑grained resource management.