Shulou(Shulou.com)06/01 Report--

This article mainly introduces the characteristics of MySQL8.0 resource management details, hoping to supplement and update some knowledge, if there are other issues you need to know, you can continue to follow my updated article in the industry information.

MySQL supports the creation and management of resource groups and allows threads running in the cloud server to be assigned to specific groups so that threads can execute according to the resources available to the group. Group properties can control their resources to enable or limit the resource consumption of threads in the group. DBA can modify these properties according to different workloads.

At present, CPU time is a manageable resource, which is expressed by the concept of "virtual CPU" to terms including CPU core, hyperthreading, hardware threading and so on. The server determines the number of virtual CPU available at startup, and database administrators with appropriate permissions can associate these CPU with resource groups and assign threads to the group.

For example, to manage the execution of batch jobs that do not need to be executed at a high priority, DBA can create a Batch resource group and adjust its priority up or down depending on how busy the server is. (perhaps batch jobs assigned to this group should run at lower priority during the day and higher priority at night. DBA can also adjust the set of CPU sets available for this group. You can enable or disable groups to control whether threads can be assigned to them.

1. Resource group components:

These features provide SQL interfaces for resource group management in MySQL:

The ①: SQL statement supports the creation, change, and deletion of resource groups, and allows threads to be assigned to resource groups. The optimizer prompts you to assign a single statement to a resource group.

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

③: this INFORMATION_SCHEMA.RESOURCE_GROUPS table exposes information about resource group definitions, while the Performance Schema threads table shows the resource group allocation for each thread.

④: the state variable provides the execution count for each administrative SQL statement.

two。 Resource Group Properties

Resource groups have properties that define groups. You can set all properties when you create a group. Some properties are fixed when they are created; others can modify them at any time thereafter.

The following attributes 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 that 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 the group, as described later. This attribute, along with the difference in priority allowed, enables system threads to be identified to protect them from contention for CPU resources against user threads.

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

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

①: CPU kinship is a set of virtual CPU that resource groups can use. The affinity can be any non-empty subset of the available CPU. If the group does not have an affinity, you can use all available CPU.

②: 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.

③: each group can be enabled or disabled, allowing administrators to control thread allocation. Threads can only be assigned to enabled groups.

Note:

The system group takes precedence over the user group, ensuring that the user thread does not take precedence over 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 association and has a priority of 0.

Note:

①: newly created system and user threads are assigned to the SYS_default and USR_default groups, respectively.

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

③: RESOURCE_GROUP_ADMIN permission is required to create and manage resource groups

-- View mysql default groups: a user group and a system group:

Mysql > SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\ gateway * 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, indicating the default priority; the VCPU_IDS value is shown to include all available CPU; and for default groups, the value displayed depends on the system running the MySQL server.

Example: create a user resource group: CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 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:

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

(after that, 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 prompt:

INSERT / * + RESOURCE_GROUP (Batch) * / INTO T2 VALUES (2)

Example 3: for the time of high system load, reduce the number of CPU assigned to the group, lower its priority, or (as shown in the figure):

ALTER RESOURCE GROUP Batch VCPU = 3 THREAD_PRIORITY = 19

Example 4: when the system load is light, increase the number of CPU assigned to the group, increase its priority, or (as shown in the figure):

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

4. Resource group replication

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

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 and does not provide an API for binding CPU to threads.

③: on FreeBSD and Solaris, ignore the resource group thread priority. (in fact, all threads run at priority 0. ) an attempt to change the priority results in a warning:

④: on Linux, the resource group thread priority is ignored unless the feature is set by CAP_SYS_NICE. The MySQL package installer for Linux systems should set this feature. For binary distribution using compressed tar files or installation from a source, CAP_SYS_NICE can set this feature manually using the setcap command to specify the pathname of the mysqld executable file (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 at one of five thread priorities. The resource group thread priority range of-20 to 19 is mapped to these levels.

Read the above about the characteristics of MySQL8.0 resource management detailed explanation, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.