WL#9467: Resource Groups

Affects: Server-8.0   —   Status: Complete

GENERAL IDEA

The idea itself is pretty simple here :

  1. Introduce global Resource Groups feature within MySQL Server

  2. Each Resource Group has its dedicated "Attributes"

  3. All the data/info/configs related to Resource Group are managed by MySQL Server (DBA actions)

  4. The only user command or "external" API is allowed : assign / switch a given thread to a given Resource Group (no direct way to apply resource related action, only via Resource Group assignment)..

For this WL we're selecting the "minimal" required functionality to allow a user manually or semi-automatically (by external tools) to assign dedicated resources to a given query / session / thread. All configuration operations should be made dynamically via SQL commands and saved in internal tables (in "mysql" database, similar to users creation/delete/etc.), so all the applied changes will remain persistent over MySQL Server restart.

So we'll need to introduce and implement the following :

  1. MySQL Resource Groups

  2. Extend the current THREADS table in PFS schema with RESOURCE_GROUP column

  3. Extend SQL with new "RESOURCE GROUP" related commands (allowing a manual action, external tool action, etc.)

  4. Add a new Query HINT (could be added by user app, Query Rewrite Plugin, external tool like ProxySQL, etc.)

RESOURCE GROUP ATTRIBUTES :

  • Name

  • CPU Affinity

  • OS thread priority

  • Group type ("system" or "user")

  • Enabled flag (1 or 0)

THREADS IN MYSQL :

  1. Currently we have all threads already referenced in PFS THREADS table

  2. Threads can be of 2 types :

    • FOREGROUND (FG) - "user" threads
    • BACKGROUND (BG) - "system" threads (internal Engine threads, e.g. "purge"in InnoDB, etc.)

  3. Each thread has its corresponding functional name in the table (e.g. "thread/innodb/page_cleaner_thread", etc. ) as well the thread_os_id.

  4. We just need to add a new "resource_group" column to complete the whole picture

NOTE: If a thread is not referenced in PFS it cannot be addressed, so all MySQL/SE threads must be registered in PFS on creation!

"SYSTEM" AND "USER" RESOURCE GROUPS :

  • Each Resource Group must have one of attributed types: "system" or "user"

  • Once the Group is created, the type cannot be changed anymore

  • The goal of having this type attribute is to protect MySQL "system" threads from fighting for CPU resources with "user" threads

  • The idea here is the following :

    • Only "system" Resource Groups can have priority higher than 0.

    • Only "system" (BG) threads can be assigned to "system" Resource Groups.

    • "User" Resource Groups are allowed to have 0 (normal) priority or lower only.

    • This approach will guarantees that never by mistake a user thread could run on a higher priority than than Storage Engine itself.

CONFIGURING RESOURCE GROUPS :

  1. There will be one "user" group (USR_Default) and "system" group (SYS_Default)

  2. Both default groups are having 0 priority and no CPU affinity

  3. These default groups attributes cannot be modified (and these groups cannot be dropped either)

  4. Any newly created user threads is automatically assigned to USR_Default.

  5. Any "system" thread is assigned by default to SYS_Default.

  6. Later we may decide to have more pre-defined groups created by default (according different Storage Engines needs, etc. - for ex. to match NDB threads CPU affinity requirements, as well Replication threads too, etc.)

  7. However, by default MySQL Server is starting with just default groups, and once online, the validation process of all Resource Groups is done and then user and system threads are re-assigned to defined Resource Groups if needed

  8. If one of Resource Groups did not pass "validation" check, it should be flagged as disabled and warning message sent to MySQL log output, no threads can be assigned to such a Resource Group until its attributes is adjusted (and the enabled flag can be changed then to "true")

  9. All Resource Groups configuration actions are done via SQL :

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

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

       mysql> DROP RESOURCE GROUP 'name' [FORCE];

       mysql> SELECT * from INFORMATION_SCHEMA.RESOURCE_GROUPS;

Where : 
    VCPU : a list of CPUs (vcpu) bind to the group, ex: 0,2,4-7,12,14 
    THREAD_PRIORITY : a "renice" value to apply to Group threads 
                      (range in [-19,19], 0 is default)
Example :
    mysql> CREATE RESOURCE GROUP 'slow_batch' type='user' vcpu='10-12,22-24' ;

Note about FORCE option:

There may be an urgent situation to DISABLE a group. Hence a DBA may prefer to not wait until no thread will use this group, the following semantics apply:

  1. DISABLE : will just set DISABLE flag on a group, so all newly coming
              threads will not be able to use it anymore.. -- while
              already running threads will continue to run as it
              till disconnect..

  2. DISABLE FORCE : set DISABLE flag to the group and move all threads
                    using it to Default group..

  3. DROP : returns error if there are some threads in group

  4. DROP FORCE : move group threads to Default group, drop the group

SQL COMMANDS :

Allow to change the Resource Group for a given connection by its thread_id or to the user session itself :

  mysql> SET RESOURCE GROUP 'name' FOR thread_id ;
  mysql> SET RESOURCE GROUP 'name' FOR thread_id1, thread_id2,thread_id3, ...;
  mysql> SET RESOURCE GROUP 'name' ;

The thread id in the above resource group commands should be obtained from the performance schema threads table.

QUERY HINTS :

  1. /*+ RESOURCE_GROUP(resource_group_name) */ -- for executing this SQL query switch the given thread to Resource Group 'name', then switch it back once the query execution is finished.

  2. The above query hint shall be applicable only to data manipulation statements and should be placed after the initial keyword of SELECT, UPDATE, INSERT, REPLACE and DELETE.

  3. Having a hints feature is very important as it'll allow users to fix problematic queries generated by "black box" apps (when changing apps code or generated query is not possible, while a query can be rewritten on fly by plugin (like Query Rewrite Plugin) or external tools (like ProxySQL))..

PRIVILEGES :

The following privilege levels are associated with resource groups and their meanings are explained below:

  1. RESOURCE_GROUP_ADMIN:- RESOURCE_GROUP_ADMIN privilege allows resource group management (creation, deletion and modification) as well as assignment of the resource groups(both system/user) to various threads. It is maximum privilege allowing for any operation relating to resource groups.

  2. RESOURCE_GROUP_USER: RESOURCE_GROUP_USER privilege bestows a user to assign other users session or queries or threads as well his own to user resource groups defined.

NOTE :

Performance Schema shall provide an API for providing the security context
info required for privilege checks associated with a given thread os id. In addition, if the privilege context associated with a thread( identified by thread os id) changes during it's lifetime, Performance schema shall provide a notification service.

INTERACTION WITH PFS

  1. The PFS threads feature should be available to make use of the Resource group feature.

  2. PFS shall provide API to obtain thread os id and security context information associated with thread id.

  3. PFS shall provide API to update the resource group name associated with a given thread id.

  4. PFS shall provide notification to the resource group component on the following events:

    • Thread create event.
    • Thread destruct event
    • Session connect event
    • Session disconnect event.
    • Security context change event.

The API shall be provided by Performance Schema, Resource Control**.

RESOURCE GROUP CONFIGURATION PERSISTENCE:

The resource group configs shall be persisted in the Data Dictionary (DD) table mysql.resource_groups. This shall be a system table and the persistence shall be done via use of Data Dictionary APIs. The schema definitions for resource_groups:

 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 resource_group_name VARCHAR(64) NOT NULL COLLATE utf8_general_ci,
 resource_group_type enum('SYSTEM', 'USER') NOT NULL,
 resoruce_group_enabled  boolean NOT NULL,
 cpu_id_mask VARCHAR(1024) NOT NULL,
 thread_priority int NOT NULL,
 PRIMARY KEY(id),
 UNIQUE KEY (resource_group_name)

NOTE:

The Resource group feature shall not be available when the thread pool plugin is enabled. This is to keep the scope to a minimum.

PLATFORM RELATED NOTES:

  1. Mac OS doesn't provide an explicit API for binding a set of CPUs to a thread. [thread affinity mac os ](https://developer.apple.com/library/content/releasenotes/Performance/RN- AffinityAPI/#//apple_ref/doc/uid/TP40006635-CH1-DontLinkElementID_2). Thus resource groups shall not be available on Mac OSX.

  2. On FreeBSD and Solaris, thread priority would not be ignored. This platform different implementations of thread library and assignment of LWPs thread priority is prohibited by native syscalls. FreeBSD thread priority issues. Thus Thread priority values are ignored are unsupported and ignored on these platforms.

  3. Windows has the thread priority classes:

    • THREAD_PRIORITY_BELOW_NORMAL
    • THREAD_PRIORITY_NORMAL
    • THREAD_PRIORITY_ABOVE_NORMAL
    • THREAD_PRIORITY_HIGHEST

The thread priority values shall be mapped in to the 4 classes by the simple linear mapping as below:

Priority Range Windows Priority Level
-20 to -10 THREAD_PRIORITY_HIGHEST
-9 to -1 THREAD_PRIORITY_ABOVE_NORMAL
0 THREAD_PRIORITY_NORMAL
1 to 10 THREAD_PRIORITY_BELOW_NORMAL
11 t0 20 THREAD_PRIORITY_LOWEST

REPLICATION SEMANTICS

CREATE, ALTER and DROP shall not be binlogged. Resource groups are managed locally at each node.



Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.