MySQL :: MySQL 8.0 Reference Manual :: 7.1.16 Resource Groups (original) (raw)

7.1.16 Resource Groups

MySQL supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads.

Currently, CPU time is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. The server determines at startup how many virtual CPUs are available, and database administrators with appropriate privileges can associate these CPUs with resource groups and assign threads to groups.

For example, to manage execution of batch jobs that need not execute with high priority, a DBA can create aBatch resource group, and adjust its priority up or down depending on how busy the server is. (Perhaps batch jobs assigned to the group should run at lower priority during the day and at higher priority during the night.) The DBA can also adjust the set of CPUs available to the group. Groups can be enabled or disabled to control whether threads are assignable to them.

The following sections describe aspects of resource group use in MySQL:

Important

On some platforms or MySQL server configurations, resource groups are unavailable or have limitations. In particular, Linux systems might require a manual step for some installation methods. For details, seeResource Group Restrictions.

Resource Group Elements

These capabilities provide the SQL interface for resource group management in MySQL:

Resource Group Attributes

Resource groups have attributes that define the group. All attributes can be set at group creation time. Some attributes are fixed at creation time; others can be modified any time thereafter.

These attributes are defined at resource group creation time and cannot be modified:

These attributes are defined at resource group creation time and can be modified any time thereafter:

Resource Group Management

By default, there is one system group and one user group, namedSYS_default andUSR_default, respectively. These default groups cannot be dropped and their attributes cannot be modified. Each default group has no CPU affinity and priority 0.

Newly created system and user threads are assigned to theSYS_default andUSR_default groups, respectively.

For user-defined resource groups, all attributes are assigned at group creation time. After a group has been created, its attributes can be modified, with the exception of the name and type attributes.

To create and manage user-defined resource groups, use these SQL statements:

Those statements require theRESOURCE_GROUP_ADMIN privilege.

To manage resource group assignments, use these capabilities:

Those operations require theRESOURCE_GROUP_ADMIN orRESOURCE_GROUP_USER privilege.

Resource group definitions are stored in theresource_groups data dictionary table so that groups persist across server restarts. Becauseresource_groups is part of the data dictionary, it is not directly accessible by users. Resource group information is available using the Information SchemaRESOURCE_GROUPS table, which is implemented as a view on the data dictionary table. SeeSection 28.3.26, “The INFORMATION_SCHEMA RESOURCE_GROUPS Table”.

Initially, the RESOURCE_GROUPS table has these rows describing the default groups:

mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\G
*************************** 1. row ***************************
   RESOURCE_GROUP_NAME: USR_default
   RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0-3
       THREAD_PRIORITY: 0
*************************** 2. row ***************************
   RESOURCE_GROUP_NAME: SYS_default
   RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0-3
       THREAD_PRIORITY: 0

The THREAD_PRIORITY values are 0, indicating the default priority. The VCPU_IDS values show a range comprising all available CPUs. For the default groups, the displayed value varies depending on the system on which the MySQL server runs.

Earlier discussion mentioned a scenario involving a resource group named Batch to manage execution of batch jobs that need not execute with high priority. To create such a group, use a statement similar to this:

CREATE RESOURCE GROUP Batch
  TYPE = USER
  VCPU = 2-3            -- assumes a system with at least 4 CPUs
  THREAD_PRIORITY = 10;

To verify that the resource group was created as expected, check the RESOURCE_GROUPS table:

mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS
       WHERE RESOURCE_GROUP_NAME = 'Batch'\G
*************************** 1. row ***************************
   RESOURCE_GROUP_NAME: Batch
   RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 2-3
       THREAD_PRIORITY: 10

If the THREAD_PRIORITY value is 0 rather than 10, check whether your platform or system configuration limits the resource group capability; seeResource Group Restrictions.

To assign a thread to the Batch group, do this:

SET RESOURCE GROUP Batch FOR thread_id;

Thereafter, statements in the named thread execute withBatch group resources.

If a session's own current thread should be in theBatch group, execute this statement within the session:

SET RESOURCE GROUP Batch;

Thereafter, statements in the session execute withBatch group resources.

To execute a single statement using the Batch group, use the RESOURCE_GROUP optimizer hint:

INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

Threads assigned to the Batch group execute with its resources, which can be modified as desired:

ALTER RESOURCE GROUP Batch  
  VCPU = 3  
  THREAD_PRIORITY = 19;  
ALTER RESOURCE GROUP Batch  
  VCPU = 0-3  
  THREAD_PRIORITY = 0;  

Resource Group Replication

Resource group management is local to the server on which it occurs. Resource group SQL statements and modifications to theresource_groups data dictionary table are not written to the binary log and are not replicated.

Resource Group Restrictions

On some platforms or MySQL server configurations, resource groups are unavailable or have limitations:

mysql> ALTER RESOURCE GROUP abc THREAD_PRIORITY = 10;  
Query OK, 0 rows affected, 1 warning (0.18 sec)  
mysql> SHOW WARNINGS;  
+---------+------+-------------------------------------------------------------+  
| Level   | Code | Message                                                     |  
+---------+------+-------------------------------------------------------------+  
| Warning | 4560 | Attribute thread_priority is ignored (using default value). |  
+---------+------+-------------------------------------------------------------+  
$> sudo setcap cap_sys_nice+ep /path/to/mysqld  
$> getcap /path/to/mysqld  
/path/to/mysqld = cap_sys_nice+ep  

As a safety measure, restrict execution of themysqld binary to theroot user and users withmysql group membership:

$> sudo chown root:mysql /path/to/mysqld  
$> sudo chmod 0750 /path/to/mysqld  

Important
If manual use of setcap is required, it must be performed after each reinstall.