MySQL8.0 new Features--resource management

mysql8.0--Resource Management:

Refer to the official documentation:

Https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html

MySQL supports the creation and management of resource groups and allows threads running within the server to be assigned to specific groups so that threads are executed according to the resources available to the group. Group properties can control their resources to enable or limit resource consumption for threads in the group. DBAs can modify these properties based on different workloads.

Currently, CPU time is a manageable resource, represented by the concept of "virtual CPU" as a term that includes CPU cores, hyper-threading, hardware threads, and so on. When the server determines the number of virtual CPUs available at startup, a database administrator with the appropriate permissions can associate the CPUs with the resource group and assign the threads to the group.

For example, to manage the execution of batch jobs that do not require high priority, DBAs can create batch resource groups and prioritize them up or down based on the server's busy level. (Perhaps the batch job assigned to the group should run at a lower priority during the day and run at a higher priority at night.) The DBA can also adjust the set of CPUs available for that group. You can enable or disable groups to control whether threads can be assigned to them.

1. Resource Group components:

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

The ①:SQL statement supports creating, changing, and deleting resource groups and allowing threads to be assigned to resource groups. The optimizer prompts you to assign a single statement to a resource group.

②: Resource group permissions can control which users can perform resource group operations.

③: The INFORMATION_SCHEMA. The Resource_groups table exposes information about resource group definitions, and the Performance Schema threads table shows the resource group assignments for each thread.

④: The state variable provides an execution count for each management SQL statement.

2. Resource Group Properties

A resource group has properties that define a group. You can set all properties when you create a group. Some properties are fixed when they are created; Other people can modify it at any time thereafter.

The following properties are defined when the resource group is created and cannot be modified:

①: Each group has a name. Resource group names are identifiers such as table and column names, and do not need to be referenced in SQL statements unless they contain special characters or reserved words. Group names are case-insensitive and can be up to 64 characters long.

②: Each group has a type, either system or user. The resource group type affects the range of priority values that can be assigned to a group, as described later. This property, along with the difference in the allowed precedence, makes it possible to identify system threads so that they are protected from contention for CPU resources against user threads.

Note: The system and user threads correspond to the background and foreground threads that are listed in the Performance Schema threads table.

These properties are defined when the resource group is created and can be modified at any later time:

①:CPU Affinity is a set of virtual CPUs that a resource group can use. Affinity relationships can be any non-empty subset of available CPUs. If the group has no affinity, then all available CPUs can be used.

②: The thread priority is the execution priority of the thread assigned to the resource group. Priority values range from 20 (highest priority) to 19 (lowest priority). The default priority for both system groups and user groups is 0.

③: You can enable or disable each group so that administrators can control thread assignment. Threads can only be assigned to groups that are enabled.

Attention:

The system group takes precedence over the user group, ensuring that the user thread does not have a higher priority than the system thread:

①: For system resource groups, the allowed priority range is 20 to 0.

②: For user resource groups, the allowed priority range is 0 to 19.

3. Resource Group Management:

By default, there is a system group and a user group, named Sys_default and Usr_default, respectively. These default groups cannot be deleted and their properties cannot be modified. Each default group has no CPU affinity, with a priority of 0.

Attention:

①: The newly created system and user threads are assigned to the Sys_default and Usr_default groups, respectively.

②: For user-defined resource groups, all properties are assigned when the group is created. After you create a group, you can modify its properties, except for the name and type properties.

③: Creating and Managing Resource Groups requires: Resource_group_admin permissions

--View the default MySQL group: A user group and a system group:

Mysql> SELECT * from INFORMATION_SCHEMA. resource_groups\g*************************** 1.              Row *************************** Resource_group_name:usr_default resource_group_type:userresource_group_enabled:1 vcpu_ids:0-0 thread_priority:0*************************** 2. Row *************************** Resource_group_name:sys_default resource_group_type:systemresource_group_enabled: 1 vcpu_ids:0-0 thread_priority:02 rows in Set (0.01 sec)

Where: The thread_priority value is 0, which indicates the default priority, the Vcpu_ids value shows the range that includes all available CPUs, and for the default group, the value that is displayed depends on the system running the MySQL server.

Example: Creating a User Resource group: Create RESOURCE group Batch TYPE = user Vcpus = 2-3 thread_priority = 10; Mysql> SELECT * from INFORMATION_SCHEMA. resource_groups WHERE resource_group_name = ' Batch ' \g---View this User resource group *************************** 1.              Row *************************** Resource_group_name:batch resource_group_type:userresource_group_enabled:1 Vcpu_ids:2-3 thread_priority:10

--: To assign a thread to a batch group, do the following:

SET RESOURCE GROUP Batch for thread_id;

--: If your current thread should be in the batch group, execute the following statement in the session:

SET RESOURCE GROUP Batch;

(Thereafter, the statements in the session will be executed using the batch group resource.) ）

Example 2: To execute a single statement using the batch group, use the Resource_group optimizer hint:

INSERT/*+ Resource_group (Batch) */into T2 VALUES (2);

Example 3: Reduce the number of CPUs assigned to a group, reduce its priority, or () for a high-load time of the system:

ALTER RESOURCE GROUP Batch vcpus = 3 thread_priority = 19;

Example 4: Increase the number of CPUs assigned to a group, increase its priority, or () if the system is under less load:

ALTER RESOURCE GROUP Batch vcpus = 0-3 thread_priority = 0;

4. Resource group replication

Resource group management is the local administration of the server where it occurs. Resource Group SQL statements and modifications to resource_groups data dictionary tables are not written to the binary log and are not copied.

5. Restrictions on resource groups:

①: If the thread pool plug-in is installed, the resource group is not available.

②: The resource group is not available on MacOS, it does not provide an API to bind the CPU to the thread.

③: Ignores resource group thread priority on FreeBSD and Solaris. (In fact, all threads run at priority 0.) Attempting to change the priority will cause a warning:

④: On Linux, the resource group thread priority is ignored unless cap_sys_nice is set to function. The MySQL package installer for Linux systems should set this feature. For binary distribution with compressed tar files or for installation from sources, Cap_sys_nice can manually set this feature by using the SETCAP command, specifying the path name of the MYSQLD executable (which requires sudo access). You can use the Getcap check feature. For example:

shell> sudo setcap cap_sys_nice+ep./bin/mysqld

Shell> Getcap./bin/mysqld

./bin/mysqld = Cap_sys_nice+ep

⑤: On Windows, threads run with one of five thread priorities. The resource group thread priority range-20 to 19-maps to these levels.

MySQL8.0 new Features--resource management