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.

Functional Requirements:

  1. Provide functionality to create, drop and alter resource groups. The resource group associate a set of controllers which can be applied to resources of the system. This is to manage and throttle the resource usage consumption for different workloads. In the current WL, resource groups contain specification of CPU affinity and thread priority which can be applied to threads (which are representatives of workloads).

  2. Resource group shall have a type associated with it and the type of the resource group shall be either SYSTEM or USER.

    • SYSTEM resource group allow for assignment of system threads to it as well have a higher control setting. Thread priority associated with a system resource group can be 0 or higher (negative value of thread priority).
    • USER resource group allow for assignment of user threads and the thread priority can be 0 or lower (positive value of thread priority).

  3. Resource group can be either in ENABLED OR DISABLED state. A resource group can be disabled explicitly by SQL command (CREATE or ALTER) or it can become disabled as some of controllers that make up the resource group are in invalid state (eg: invalid value of CPU ID due to hardware change etc.). No control operations can be done on a disabled resource group.

  4. Resource group should allow to specify the control properties. The control properties are CPU list and thread priority value. The CPU list is a comma separated list of cpu ids and the cpu ids can specify a range (using the hyphen separator) in addition to a single cpu id value. The range of priority values for a system resource group is -20 to 0 and for a user resource group is 0 to 19.

  5. A resource group shall be associated with a thread, a session thread, list of thread and a hint to run a particular query using a resource group. The hint is applicable to data change statements. These statements include SELECT, UPDATE, INSERT, REPLACE and DELETE. The threads specification shall be via the PFS thread id value. This value can be queried from the performance_schema.threads table.

  6. By default there will be two resource groups - the system default resource resource group (SYS_default) and the user default resource group (USR_default). Both of these resource groups will have a priority of 0 and no specific cpu affinity. All threads unless otherwise explicitly changed, shall be associated with SYS_default or USR_default resource group depending on it being a system or user thread.

  7. The alter and drop operation of a resource group shall have an option FORCE.

    • FORCE allows a resource group to be disabled even when some threads are using the resource group (as part of an alter operation). It should move the threads into their respective default resource groups.

    • FORCE allows a resource group to be dropped while some threads are associated with the resource group. The threads associated with the resource group shall be moved to the respective default resource groups.

  8. Two new privileges shall be introduced:

    • 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.

    • 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.

  9. The performance_schema.threads shall be augmented with a new column RESOURCE_GROUP which shall indicate the resource group the thread is associated with.

  10. The resource groups shall be persisted in the data dictionary table (mysql. resource_groups) and be made available across server reboots/future upgrades. The resource groups defined shall be exposed to the users ( who has appropriate privileges) via the INFORMATION_SCHEMA.RESOURCE_GROUPS table. The Data Dictionary table mysql.resource_groups is local to the node and this table shall not be replicated.

  11. Resource groups shall not be available on Mac OS. Also resource group feature shall not be available if server is using thread pool plugin for connection handling. On these, all resource group operation shall return the error ER_FEATURE_UNSUPPORTED. Thread priority specification shall be ignored on FreeBSD and Solaris platforms. On Linux systems, the mysqld process should have CAP_SYS_NICE capability for setting thread priority. Packaging changes shall be done on linux to ensure that mysqld has CAP_SYS_NICE capability. An warning ER_ATTRIBUTE_IGNORED shall be indicated to the user and the default priority of zero shall be used on these platforms.

  12. There shall be new error messages and error codes introduced as result of this worklog.

  13. The DDL for CREATING, DROPPING and ALTERING a resource group configuration has only local scope and these statements are not replicated. This is because different replicas may have different hardware profiles. Hence the resource group configurations are not replicated.

  14. No Resource group management APIs shall be exposed to the any subsystems or as services in the service registry (for use by other components/plugins). This can be considered in future extensions.

  15. This feature doesn't impact any upgrade & cross replication scenarios. It is to be noted that an extra column is added in pfs threads table.

Non-Functional Requirements:

  1. In the default configuration (where in all threads are bound to default resource groups), the server throughput and transaction latency shall not decrease for sysbench RW & RO benchmark tests.

Contents

  1. Parser Component
  2. Platform API Component
  3. Core Runtime Component
  4. Persistence (DD) Component
  5. Performance Schema (PFS) Component
  6. Privilege Component
  7. Resource Group Hint Component

Parser Component

The parser component consists of required changes in parser related components to implement SQL interfaces and query hint described in the introduction section. The component provides interaction with DBAs and end-users for resource group implementation. It gets input from user and does syntax validation and produces parsed output and hands control to core runtime component. It consists of modifying bison parser files , lexer and the parser related source files to provide grammar rules, new tokens and defining the relevant semantic actions. New symbols are introduced in the lexer. New classes are introduced to represent the parsed object output .

Platform API Component

The platform component provides API in a platform agnostic-way to Core Runtime Component. It specifies API implementation to bind & unbind threads to CPU cores (identified by CPU ID) , get and set thread priorities, validate thread priority and get the total number of virtual CPUs. The APIs are implemented across the platforms (Linux, FreeBSD, Solaris & Windows).

Core Runtime Component

The core runtime component interacts with all other components to implement resource group functionality. The class Resource_group contains the attributes of the resource group such as name, type and whether resource is active or inactive. It is composed of the Thread_resource_control class. The Thread_resource_control class consists of control methods which apply cpu affinity and thread priority to the thread resource. A singleton Resource_group_mgr class is introduced and this class is responsible for maintaining the mapping of the resource group names and the corresponding resource group objects. It also provides API methods to apply the resource controls onto the thread, get default resource groups, abstracts services to the PFS component and move resource group functionality (for Resource group Hint feature.)

Persistence (DD) Component

The persistence or dictionary component is responsible for persisting the resource group configurations defined by DBA user in the relational tables and make it available across server reboots. The system table mysql.resource_groups is introduced and the schema definitions of this table are:

sql 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 resource_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)

mysql.resource_groups shall be a hidden system table and the information pertaining to the resource groups shall be exposed to the end-user as an information schema view INFORMATION_SCHEMA.RESOURCE_GROUPS.

Performance Schema (PFS) Component

The performance schema provides threads table. It lists out the threads which are in existence in mysql server. It maintains information relating to thread such as it's operating system thread identifier, security context information and whether thread is a system or user thread. The performance schema threads table (performance_schema.threads) table shall be augmented with an additional column by name resource_group_name which shall be a varchar attribute. It identifies resource group name which the thread was associated with. The performance_schema.threads provides the thread_id value which is required for usage in SET RESOURCE GROUP command. In addition, the resource group component shall register with the performance schema to listen to certain events which are of interest. These events include:

  1. thread create event: On notification of thread create event from Performance Schema, the resource group component associates the default resource group. The resource shall be user or system default depending on the thread being a user or system thread.

  2. thread destruct event: Currently there is no action performed on thread destruct event by the resource group.

  3. session connect event: - When a thread is binded to a new session (either from thread cache or thread pool), we get notification of this event. Upon this event, we associate the resource group the session was associated with earlier.

  4. session disconnect event:- On a session disconnect event, we revert the thread to the default user resource group.

  5. security context change event: When a security context associated with a thread undergoes a change, we get notification of this event. Upon received, reevaluation of the association of the thread with it's resource group. If security credentials are validated for that resource group, the thread shall remain in the same resource group else it shall reverted to the appropriate default resource group.

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

Privilege Component

Two dynamic privileges are introduced:

  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.

Resource Group Hint Component

Resource group hint component consists of the changes necessary to implement the hint functionality of the resource group. Changes are made to introduce the token RESOURCE_GROUP in the hints yy file. The necessary parser classes are introduced. A new data member to the THD class which consists of context info necessary for switching the resource group during execution of the query is added. Before execution of data change query begins, the context info is checked and if necessary the switch is done. The context info is protected LOCK_thd_data mutex to synchronization with modification from a concurrent ALTER, DROP and SET resource group commands. We do not hold the MDL lock on resource group names as part of the query execution for faster switch.

Contents

  1. Parser Component
  2. Platform API Component
  3. Core Runtime Component
  4. Persistence (DD) Component
  5. Performance Schema (PFS) Component
  6. Privilege Component
  7. Resource Group Hint Component

Parser Component

New Token Introduced

The following new tokens are introduced:

  1. THREAD_PRIORITY_SYM:- This is a new token introduced in mysql 8.0 for converting the string THREAD_PRIORITY to the token value THREAD_PRIORITY_SYM.
  2. RESOURCE_SYM: This is a new token introduced in mysql 8.0 for converting the string RESOURCE to the token value RESOURCE_SYM.
  3. VCPU_SYM: This is a new token introduced in mysql 8.0 for converting the VCPU to token value VCPU_SYM.
  4. SYSTEM_SYM: This is a new token introduced in mysql 8.0 for converting the string SYSTEM to token value SYSTEM_SYM. SYSTEM is a reserved SQL token (meaning it can't be used as identifiers in SQL statements.) All of the above strings that make up tokens are not considered reserved keywords and they are mysql related tokens except SYSTEM_SYM

Grammar Rules introduced in file sql_yacc.yy

The various grammar rules for RESOURCE GROUP management and assignment are introduced in the file sql_yacc.yy are:

CREATE RESOURCE GROUP rule:

 CREATE RESOURCE_SYM GROUP_SYM ident TYPE_SYM opt_equal  resource_group_types 
    opt_resource_group_cpu_list opt_resource_group_priority
    opt_resource_group_enable_disable 

    { 
     Lex->sql_command= SQLCOM_CREATE_RESOURCE_GROUP; 
     PT_statement *tmp= NEW_PTN PT_create_resource_group( 
                        to_lex_cstring($4), $7, $8, $9, $10); 
     MAKE_CMD(tmp); 
     }

ALTER RESOURCE GROUP rule

ALTER RESOURCE_SYM GROUP_SYM ident opt_resource_group_cpu_list
          opt_resource_group_priority opt_resource_group_enable_disable
          opt_force
          {
            Lex->sql_command= SQLCOM_ALTER_RESOURCE_GROUP;
            PT_statement *tmp= NEW_PTN PT_alter_resource_group(
                                       to_lex_cstring($4), $5, $6, $7, $8);
            MAKE_CMD(tmp);
          }

DROP RESOURCE GROUP rule

DROP_RESOURCE_SYM GROUP_SYM opt_force ident 
    { 
    Lex->sql_command= SQLCOM_DROP_RESOURCE_GROUP; 
      PT_statement *tmp= NEW_PTN PT_drop_resource_group($4, 
                            to_lex_cstring($5)); 
        MAKE_CMD(tmp); 
    }

SET RESOURCE GROUP rule

set_resource_group_stmt:
SET_SYM RESOURCE_SYM GROUP_SYM ident 
    { 
        $$= NEW_PTN PT_set_resource_group(to_lex_cstring($4), nullptr); 
       Lex->sql_command= SQLCOM_SET_RESOURCE_GROUP; 
    }
 |  SET_SYM RESOURCE_SYM GROUP_SYM ident FOR_SYM thread_id_list_options
    {
       $$= NEW_PTN PT_set_resource_group(to_lex_cstring($4), $6);
       Lex->sql_command= SQLCOM_SET_RESOURCE_GROUP;
    }

NON-TERMINAL AND OTHER TERMINAL rules

resource_group_types:
        USER {  $$= resourcegroups::Type::USER_RESOURCE_GROUP;  }
      | SYSTEM_SYM { $$= resourcegroups::Type::SYSTEM_RESOURCE_GROUP;  }
     ;

opt_resource_group_cpu_list: 
    VCPU_SYM opt_equal cpu_range_spec { $$= $3; } 
  ;

cpu_range_spec: 
    cpu_num_or_range 
    { 
        resourcegroups::Range *r= new resourcegroups::Range($1.start, $1.end); 
       if (r == nullptr) 
        MYSQL_YYABORT; 
      $$= new List; 
      if ($$->push_back(r)) 
        MYSQL_YYABORT; 
    } 
 | cpu_range_spec opt_comma cpu_num_or_range 
   { 
    resourcegroups::Range *r= new resourcegroups::Range($3.start, $3.end); 
       if (r == nullptr) 
        MYSQL_YYABORT; 
      $$= $1; 
      if ($$->push_back(r)) 
        MYSQL_YYABORT; 
   } 
 ; 

cpu_num_or_range: 
 NUM 
 { 
   using namespace resourcegroups; 
   using namespace resourcegroups::platform; 
   cpu_id_t cpu_id= static_cast(my_strtoull($1.str, nullptr, 10)); 
   $$.start= cpu_id; $$.end= cpu_id; 

 } 
    | NUM '-' NUM 
    { 
        using namespace resourcegroups; 
        using namespace resourcegroups::platform; 
        $$.start= static_cast(my_strtoull($1.str, nullptr, 10)); 
        $$.end= static_cast(my_strtoull($3.str, nullptr, 10)); 
   } 
  ;


opt_resource_group_priority:
        /* empty */ { $$= 0; }
      | THREAD_PRIORITY_SYM opt_equal NUM
        {
           $$= static_cast(my_strtoll($3.str, nullptr, 10));
        }
      | THREAD_PRIORITY_SYM opt_equal '-' NUM
        {
           $$= static_cast(-my_strtoll($4.str, nullptr, 10));
        }
      ;

opt_resource_group_enable_disable:
        /* empty */ { $$= true; }
      | ENABLE_SYM  { $$= true; }
      | DISABLE_SYM { $$= false; }
     ;

opt_force:
        /* empty */ { $$= false; }
      | FORCE_SYM   { $$= true; }
    ;
thread_id_list_single:
        ulonglong_num
        {
          ulonglong *thread_id_value_ptr= YYTHD->alloc_typed();
          if (thread_id_value_ptr == nullptr)
            MYSQL_YYABORT;
          *thread_id_value_ptr= $1;
          $$= new List;
          if ($$->push_back(thread_id_value_ptr))
            MYSQL_YYABORT;
        }
thread_id_list_single: 
ulonglong_num 
{ 
    ulonglong *thread_id_value_ptr= YYTHD->alloc_typed(); 
    if (thread_id_value_ptr == nullptr) 
        MYSQL_YYABORT; 
    *thread_id_value_ptr= $1; 
    $$= new List; 
    if ($$->push_back(thread_id_value_ptr)) 
        MYSQL_YYABORT; 
} 

thread_id_list: 
      thread_id_list_single { $$= $1; } 
    | thread_id_list opt_comma thread_id_list_single 
      {          $1->concat($3); 
        $$= $1; 
      } 
    ; 

thread_id_list_options: 
     thread_id_list_single  { $$= $1; } 
  |      thread_id_list  { $$= $1; } 
 ;

Parser Classes Introduced

New classes are introduced in files parse_tree_nodes.h/.cc. All classes inherit from super class PT_statement and implement the virtual methods make_cmd and contextualize. The class is composed of the abstract interface class Sql_cmd. The Sql_cmd has the virtual execute method which contains implementations of respective sql commands. The class diagram of parse tree node classes is given below:

The definition of various parser classes and description of the methods and attributes in it are provided below:

PT_create_resource_group: The definition of class is given below:-

class PT_create_resource_group : public PT_statement
{
typedef PT_statement super;
 resourcegroups::Sql_cmd_create_resource_group sql_cmd; 
public: 
 PT_create_resource_group(const LEX_CSTRING &name, 
                          const resourcegroups::Type type, 
                          const Trivial_array *cpu_list, 
                          int priority);
 virtual Sql_cmd *make_cmd(THD *thd);
 virtual bool contextualize(Parse_context *pc);
};

On execution of create resource group grammar rule, PT_create_resource_group object is instantiated. Then it's virtual make_cmd method is called. This methods calls the contextualize method and finally creates a concrete instance of the Sql_cmd_create_resource_group. This class implements the interface Sql_cmd. The virtual method execute contains the implementation of this statement. This method is called once parse stage has been completed.

PT_alter_resource_group

class PT_alter_resource_group : public PT_statement
{
   typedef PT_statement super; 
 resourcegroups::Sql_cmd_alter_resource_group sql_cmd; 
public: 
 PT_alter_resource_group(const LEX_CSTRING &name, 
 const Trivial_array *cpu_list, 
   int priority, bool enable, bool force); 
 virtual Sql_cmd *make_cmd(THD *thd);
 virtual bool contextualize(Parse_context *pc);
};

On execution of alter resource group grammar rule, PT_alter_resource_group object is instantiated. Then it's virtual make_cmd method is called. This methods calls the contextualize method and finally creates a concrete instance of the Sql_cmd_alter_resource_group. This class implements the interface Sql_cmd. The virtual method execute contains implementation of this statement. This method is called once the parse stage has been completed.

PT_drop_resource_group

class PT_drop_resource_group : public PT_statement
{
  typedef PT_statement super;
  resourcegroups::Sql_cmd_drop_resource_group sql_cmd;
public:
  PT_drop_resource_group(bool force,
                         const LEX_CSTRING &resource_group_name);
  virtual Sql_cmd *make_cmd(THD *thd);
  virtual bool contextualize(Parse_context *pc);
};

On execution of drop resource group grammar rule, PT_drop_resource_group object is instantiated. Then it's virtual make_cmd method is called. This methods calls the contextualize method and finally creates a concrete instance of the Sql_cmd_drop_resource_group. This class implements the interface Sql_cmd. The virtual method execute contains implementation of this statement. This method is called once parse stage has been completed.

PT_set_resource_group

class PT_set_resource_group : public PT_statement
{
  typedef PT_statement super;
  resourcegroups::Sql_cmd_set_resource_group sql_cmd;
public:
  PT_set_resource_group(const LEX_CSTRING &name,
                        Trivial_array *thread_id_list);
  virtual Sql_cmd *make_cmd(THD *thd);
  virtual bool contextualize(Parse_context *pc);
};

On execution of set resource group grammar rule, PT_set_resource_group object is instantiated. Then it's virtual make_cmd method is called. This methods calls the contextualize method and finally creates a concrete instance of the Sql_cmd_set_resource_group. This class implements the interface Sql_cmd. The virtual method execute contains implementation of this statement. This method is called once the parse stage has been completed.

Platform API Component

The platform component provides API in a platform agnostic-way to Core Runtime Component. It specifies the API implementation to bind & unbind threads to CPU cores (identified by CPU ID) , thread priority, valid thread priority, number of virtual CPUs across various platforms. The platform APIs available are summarized as below:

  1. bind_to_cpu(cpu_id_t cpu_id): The API binds the current thread to the CPU specified cpu_id.

  2. bind_to_cpu(cpu_id_t cpu_id, my_thread_os_id_t thread_id): The API binds the thread specified by thread_id to the cpu specified by CPU_ID.

  3. bind_to_cpus(const std::vector &cpu_ids): The API restricts the current thread to run on any of the CPU list specified by the cpu_id argument.

  4. bind_to_cpus(const std::vector &cpu_ids, my_thread_os_id_t thread_id): The API restricts the thread specified by thread id to run on any of the CPU list specified by the CPU_IDs argument.

  5. unbind_thread(): The API associates the current thread to run on all available cores in the system.

  6. unbind_thread(my_thread_os_id_t thread_id): The API allows thread specified by thread id to run on all available cores in the system.

  7. thread_priority(): The API returns the thread priority of the current thread.

  8. thread_priority(my_thread_os_id_t thread_id): The API returns the thread priority of the thread specified by thread_id.

  9. set_thread_priority(int priority): The API sets priority of the current thread to the priority value specified.

  10. set_thread_priority(int priority, my_thread_os_id_t thread_id): The API sets the priority of thread specified by thread id to the priority value specified.

  11. get_num_vcpus(): The API provides the number of CPUs available on the system.

  12. is_valid_thread_priority(int priority): The API validates the priority value. Currently the priority values range from -19 to 20 (typical of unix platforms). Platforms that do not support this values shall be normalized in this range.

Core Runtime Component

The class hierarchy depicting the relations of the various core runtime components is shown below:

Resource_group_mgr class: This is a singleton class which provides management functionalities related to Resource group. The class definition is as follows:

class Resource_group_mgr
{
public:
  static Resource_group_mgr *instance();
  static void destroy_instance();

  bool init();
  bool post_init();
  Resource_group *get_resource_group(const std::string &resource_group_name);
  bool add_resource_group(std::unique_ptr resource_group_ptr);
  void remove_resource_group(const std::string &name);
  bool get_thread_attributes(ulonglong thread_id, PSI_thread_attrs *pfs_thread_attr)
  Resource_group *create_and_add_in_resource_group_map(const LEX_CSTRING &, Type, bool, int);
  bool move_resource_group(Resource_group *from_res_mgr,  Resource_group *to_res_mgr);
  Resource_group *deserialize_resource_group(const dd::Resource_group *);
  void set_resource_group_name_in_pfs(ulonglong thread_id, const char *name,
                                      int length);
  Resource_group *sys_default_resource_group();
  Resource_group *usr_default_resource_group();
  bool is_resource_group_default(Resource_group *res_grp);


private:
  static Resource_group_mgr *m_instance;
  my_service m_resource_group_svc;
  my_service m_notify_svc;
  std::unique_ptr m_usr_default_resource_group;
  std::unique_ptr m_sys_default_resource_group;
  std::unordered_map>
    m_resource_group_map;
  mysql_rwlock_t m_map_rwlock;
  Resource_group_mgr() : m_resource_group_svc("pfs_resource_group",
                                              mysql_plugin_registry_acquire()),
                         m_notify_svc("pfs_notify",
                         m_usr_default_resource_group(nullptr),
                         m_sys_default_resource_group(nullptr);
  ~Resource_group_mgr();
  Resource_group_mgr(const Resource_group_mgr&)= delete;
  void operator=(const Resource_group_mgr&)= delete;
};

The attributes of the Resource_group_mgr class are:

  • m_instance: It stores the singleton pointer to the Resource_group_mgr class.
  • m_resource_group_svc: This represents the handle to the PFS resource group service.
  • m_user_default_resource_group: This member variable is a pointer to USR_default resource group.
  • m_sys_default_resource_group: This member variable is a pointer to the SYS_default resource group.
  • m_resource_group_map: This member maintains the mapping of the resource group name and the associated Resource_group pointers.
  • m_map_rwlock: This is a read write lock that protects the resource group member above.

The methods of the resource group mgr class are:

  • instance: This method obtains the singleton instance of the Resource_group_mgr class
  • destroy_instance: This method destroys the singleton instance.
  • get_resource_group: This method obtains the in-memory Resource_group object corresponding to the resource group name.
  • add_resource_group: This method adds the resource group to the in-memory map of resource group name and the resource group object.
  • remove_resource_group: This method removes the resource group from the in-memory map.
  • move_resource_group: The method moves the resource group associated with the current thread to the specified destination resource group.
  • usr_default_resource_group: This method returns the handle to the USR_default resource group
  • sys_default_resource_group: This method returns the handle to the SYS_default resource group.
  • create_and_add_in_resource_group_map: This method creates a resource group object and adds it to the resource group map.
  • deserialize_resource_group: The method deserializes a DD resource group object and creates the in-memory Resource_group object.
  • set_pfs_resource_group_name:- This method sets the resource group name in the pfs table performance_schema.threads.

    Resource_group class : This abstracts the resource group entity in memory. It contains attributes that describe the resource group. It is composed of the Thread_resource_control class. This control class defines the set of CPUs a thread can be restricted to as well as the priority a thread can have. The definition of Resource_group class is as follows:

    class Resource_group
{
public:
  Resource_group(const std::string &name, const Type type, bool enabled,
                 std::unique_ptr control)
    : m_name(name), m_type(type), m_enabled(enabled),
      m_control(std::move(control))
  { }
  const std::string &name() const;
  Type type() const;
  bool enabled() const;
  void set_type(Type type);
  void set_enabled(bool enabled);
  void set_controller(Resource_control *control);
  Resource_control *controller() const;
private:
  std::string m_name;
  Type m_type;
  bool m_enabled;
  Thread_resource_control m_thread_control;
  std::set pfs_thread_set;
  mysql_mutex_t m_set_mutex;
  Resource_group(const Resource_group&) = delete;
  void operator=(const Resource_group&) = delete;
};

    The attributes of the Resource_group class are: 1. m_name:- This attribute represents the name of the resource group.

    1. m_type: This attribute indicates the type of the resource group SYSTEM or USER resource group.

    2. m_thread_control: This member indicates the Thread_resource_control the resource group is bound to.

    3. m_pfs_thread_id_set: This contain set of threads that are associated with the resource group.

    4. m_set_mutex: This mutex protects the thread id set above.

    The methods of the Resource_group class are: 1. name: This method returns the name of the resource group.

    1. type: This method returns the type of the resource group.

    2. enabled: This method returns true if the resource group is active else false.

    3. controller: This method returns the controller associated with the resource group.

    4. set_type: This method sets the type of the resource group.

    5. set_enabled: This method set active a resource group.

    6. set_controller: This method sets the controller of the resource group.

    Thread_resource_control class: The Thread_resource_control holds the CPU id list and thread priority and defines the control methods that apply necessary attributes on the thread resource.

    class Thread_resource_control 
{
public:
  Thread_resource_control(std::unique_ptr> cpu_vector,
                          int priority);
  int  priority() const;
  bool apply_control(my_thread_os_id_t thread_os_id);
  bool validate() override;
  bool store_to_dd_obj(dd::Resource_group *resource_group);
private:
  std::unique_ptr> m_cpu_vector;
  int m_priority;
};

    The attributes of Thread_resource_control class are:

    • m_cpu_vector:- It represents the list of CPU IDs that threads which belong to the resource group having this controller shall be bound to.
    • m_priority:- It represents priority threads will have for the resource group containing this controller.

    The methods of the Thread_resource_control class are:

    1. apply_control: The control related configuration relevant to the control are exercised by this method. In case of the CPU Control , CPU affinity and thread affinity to the thread are applied by this method.
    2. validate: The method validates the control related data. In case of CPU related control, it could be validation of the CPU id list matches with available CPUs on the system.
    3. store_to_dd_obj: This method copies the control related information to the Data Dictionary Resource Group object.

    Persistence (DD) Component

    DD API interfaces

    The DD API interfaces provide to the core runtime component the APIs necessary for persisting the resource group configuration in the data dictionary. The API provided are summarized below:

    1. resource_group_exists: The API checks for existence of a given resource group in the new data dictionary.
    2. create_resource_group: The API create a new resource group in the data dictionary.
    3. update_resource_group: The API modifies an existing data dictionary.
    4. drop_resource_group: The API drops the resource group from the data dictionary.

    Resource_groups class

    The Resource_groups class provides methods for adding and manipulating the SQL definition of the table. It has meta information of the column type and column names that constitute mysql.resource_groups table. It provides methods for instantiation of the resource group Dictionary Object. It's definition is as follows:

    class Resource_groups: virtual public Dictionary_object_table_impl
{
public:
  Resource_groups();
static const Resource_groups &instance();
  static const String_type &table_name();
  enum enum_fields
  {
    FIELD_ID,
    FIELD_RESOURCE_GROUP_NAME,
    FIELD_RESOURCE_GROUP_TYPE,
    FIELD_RESOURCE_GROUP_ENABLED,
    FIELD_CPU_ID_MASK,
    FIELD_THREAD_PRIORITY
  };
public:
  virtual const String_type &name() const;
  virtual Dictionary_object *create_dictionary_object(const Raw_record &) const;
  static bool update_object_key(Global_name_key *key,
                                const String_type &resource_group_name);
};

    dd::Resource_group Dictionary object and it's implementation class.

    The dd::Resource_group class is the interface class representing resource group dictionary object. Each instance of this object is uniquely identified by an Object ID and it has interface methods to set and get values of the columns that make up mysql.resource_groups innodb table. The Resource_group_type represent the object type class associated with resource group. It is a singleton class and is used as a meta class to create an instance of resource group. The class hierarchy of the various object classes and their definition is show below: 

    class Resource_group : public Dictionary_object
{
public:
  static const Object_type &TYPE();
  static const Dictionary_object_table &OBJECT_TABLE();
  typedef Resource_group cache_partition_type;
  typedef tables::Resource_groups cache_partition_table_type;
  typedef Primary_id_key id_key_type;
  typedef Global_name_key name_key_type;
  typedef Void_key aux_key_type;
public:
  ~Resource_group() override {}
  virtual bool update_id_key(id_key_type *key) const
  { return update_id_key(key, id()); }
  static bool update_id_key(id_key_type *key, Object_id id);
  virtual bool update_name_key(name_key_type *key) const
  { return update_name_key(key, name()); }
  static bool update_name_key(name_key_type *key,
                              const String_type &name);
  virtual bool update_aux_key(aux_key_type *key) const
  { return true; }
  virtual const resourcegroups::Type &resource_group_type() const = 0;
  virtual void set_resource_group_type(const resourcegroups::Type &type) = 0;
  virtual bool resource_group_enabled() const = 0;
  virtual void set_resource_group_enabled(bool enabled) = 0;
  virtual const std::bitset &cpu_id_mask() const = 0;
  virtual void  set_cpu_id_mask(std::unique_ptr<
                                std::bitset > cpu_id_mask) = 0;
  virtual int thread_priority() const = 0;
  virtual void set_thread_priority(int priority) = 0;
  virtual Resource_group *clone() const = 0;
};

    Resource group implementation class

    class Resource_group_impl : public Entity_object_impl,
                                               public Resource_group
{
public:
  Resource_group_impl();
  Resource_group_impl(const Resource_group_impl &);
  virtual ~Resource_group_impl()
  {}
public:
  const Dictionary_object_table &object_table() const override
  { return Resource_group::OBJECT_TABLE(); }
  bool validate() const override;
  bool restore_attributes(const Raw_record &r) override;
  bool store_attributes(Raw_record *r) override;
  void debug_print(String_type &outb) const override;
  const resourcegroups::Type &resource_group_type() const override
  { return m_type; }
  void set_resource_group_type(const resourcegroups::Type &type) override
  { m_type= type; }
  bool resource_group_enabled() const override
  { return m_enabled; }
  void set_resource_group_enabled(bool enabled) override
  { m_enabled= enabled; }
  const std::bitset &cpu_id_mask() const override
  { return *(m_cpu_id_mask.get()); }
  void set_cpu_id_mask(std::unique_ptr<
                       std::bitset > cpu_id_mask) override
  { m_cpu_id_mask= std::move(cpu_id_mask); }
  int thread_priority() const override
  { return m_thread_priority; }
  void set_thread_priority(int priority) override
  { m_thread_priority= priority; }
private:
  String_type m_resource_group_name;
  resourcegroups::Type m_type;
  bool m_enabled;
  std::unique_ptr >  m_cpu_id_mask;
  int m_thread_priority;
  Resource_group *clone() const
  {
    return new Resource_group_impl(*this);
  }
};

    Performance Schema (PFS) Component

    Please refer to LLD section of WL#8881 - PERFORMANCE_SCHEMA, RESOURCE CONTROL.

    Privilege Component

    The dynamic privileges RESOURCEGROUP_ADMIN and RESOURCEGROUP_USER are introduced. The are registered during system startup in the dynamic_privilege_init methods as:

          ret |=
        service->register_privilege(STRING_WITH_LEN("RESOURCEGROUP_ADMIN"));
      ret |=
        service->register_privilege(STRING_WITH_LEN("RESOURCEGROUP_USER"));

    The RESOURCE_GROUP_SUPER privilege grant are checked in the execute methods of Sql_cmd_create_resource_group, Sql_cmd_alter_resource_group, Sql_cmd_drop_resource_group and Sql_cmd_set_... methods. The error ER_SPECIFIC_ACCESS_DENIED_ERROR is raised if the user doesn't have the appropriate privilege required for execution of these SQL commands.

      Security_context *sctx= thd->security_context();
  if (!(sctx->check_access(SUPER_ACL) ||
        sctx->has_global_grant(STRING_WITH_LEN("RESOURCEGROUP_ADMIN")).first))
  {
    my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0),
             "SUPER OR RESOURCEGROUP_ADMIN");
    DBUG_RETURN(true);
  }

    Similar grant checking is done in the SET resource group statements with respect to the RESOURCEGROUP_USER privilege.

    Resource Group Hint Component

    A new RESOURCE_GROUP_HINT token is introduced in the file sql_hints.yy and the corresponding symbol for this is added in the file sql_lex.h. The rule for parsing the resource group hint is also added in the file sql_hints.yy.

    Addition in file sql_hints.yy
   %token RESOURCE_GROUP_HINT

   resource_group_hint:
         RESOURCE_GROUP_HINT '(' HINT_ARG_IDENT ')'
         {
           $$= NEW_PTN PT_hint_resource_group($3);
           if ($$ == nullptr)
              YYABORT; // OOM
         }


Addition in file sql_lex.h
  { SYM_H("RESOURCE_GROUP",         RESOURCE_GROUP_HINT)}

    The token is added for hint digest calculation by modifying the method Hint_scanner::add_hint_token_digest. A new enum value is added in opt_hints_enum.

    RESOURCE_GROUP_HINT_ENUM

    A resource context data member shall be introduced as part of the class THD to support the resource hint feature. It consists of two data members:

    struct Resource_group_ctx
{
  Resource_group *m_cur_resource_group;
  Resource_group *m_switch_resource_group;
};

    m_cur_resource_group point to null if the session is associated with a default resource group or the session current resource group (if it is other than default). For background or SYSTEM threads m_cur_resource_group shall always be null. m_switch_resource_group shall always contain that is to be switched during a execution of a data change statement.

    LOCK_thd_data protect the member variable from concurrent modification with SET, DROP and ALTER resource group commands.

    The PT_hint_resource_group class definition is as follows:

    class PT_hint_resource_group : public PT_hint
{
  const LEX_CSTRING m_resource_group_name;

  typedef PT_hint super;
public:
  PT_hint_resource_group(const LEX_CSTRING &name)
    : PT_hint(RESOURCE_GROUP_HINT_ENUM, true), m_resource_group_name(name)
  {}

  /**                                                                                                                                                    
     Function initializes resource group name.                                                                                                           

     @param pc Pointer to Parse_context object                                                                                                           

     @return true in case of error,                                                                                                                      
             false otherwise                                                                                                                             
  */
  virtual bool contextualize(Parse_context *pc);
  virtual void append_args(THD *thd, String *str) const
  {
    append_identifier(thd, str, m_resource_group_name.str,
                      m_resource_group_name.length);
  }
};

    The contextualize method definition is as follows:

    bool PT_hint_resource_group::contextualize(Parse_context *pc)
{
  if (super::contextualize(pc))
    return true;

  if (pc->thd->lex->sphead ||
      pc->select != pc->thd->lex->select_lex)
  {
    push_warning_printf(pc->thd, Sql_condition::SL_WARNING,
                 ER_WARN_UNSUPPORTED_HINT,
                 ER_THD(pc->thd, ER_WARN_UNSUPPORTED_HINT),
                 "Subquery or Stored procedure or Trigger");
    return false;
  }

  resourcegroups::Resource_group *resource_group=
    resourcegroups::Resource_group_mgr::instance()->get_resource_group(
      m_resource_group_name.str);
  if (resource_group == nullptr)
  {
    push_warning_printf(pc->thd, Sql_condition::SL_WARNING,
                        ER_RESOURCE_GROUP_NOT_EXISTS,
                        ER_THD(pc->thd, ER_WARN_UNSUPPORTED_HINT),
                        "m_resource_group_name.str");
    return false;
  }
  mysql_mutex_lock(&pc->thd->LOCK_thd_data);
  pc->thd->resource_group_ctx()->m_switch_resource_group= resource_group;
  mysql_mutex_unlock(&pc->thd->LOCK_thd_data);

  return false;
}

    During execution of data manipulation queries, the resource groups are switched as below:

    
           auto mgr_instance= resourcegroups::Resource_group_mgr::instance();
            mysql_mutex_lock(&thd->LOCK_thd_data);
            resourcegroups::Resource_group *src_res_grp=
              thd->resource_group_ctx()->m_cur_resource_group;
            resourcegroups::Resource_group *dest_res_grp=
              thd->resource_group_ctx()->m_switch_resource_group;
            bool switched= false;
            if (dest_res_grp != nullptr)
              switched= mgr_instance->move_resource_group(src_res_grp,
                                                          dest_res_grp);
            mysql_mutex_unlock(&thd->LOCK_thd_data);
            error= mysql_execute_command(thd, true);
            if (switched)
            {
              mysql_mutex_lock(&thd->LOCK_thd_data);
              if (thd->resource_group_ctx()->m_cur_resource_group != nullptr &&
                  thd->resource_group_ctx()->m_cur_resource_group == src_res_grp)
              {
                mgr_instance->move_resource_group(dest_res_grp,
                  thd->resource_group_ctx()->m_cur_resource_group);
              }
              thd->resource_group_ctx()->m_switch_resource_group= nullptr;
              mysql_mutex_unlock(&thd->LOCK_thd_data);

    The move_resource_group is a helper method in the Resource_group_mgr class which aids in switching the resource groups (of the current session).



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