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

| Resource group introduction 1. Brief introduction

MySQL is a single-process multithreaded program. MySQL threads include background threads (Master Thread, IO Thread, Purge Thread, etc.), as well as user threads. Prior to 8.0, all threads had the same priority, and the resources of all threads were shared. But after MySQL8.0, due to the introduction of the Resource Group feature, we can modify the priority of threads and the resources that can be used by resource groups, and we can specify different threads to use specific resources.

In the current version, DBA can only manipulate CPU resources, and the minimum force of control is vCPU, that is, the number of operating system logical CPU cores (you can view the total number of controllable CPU through the lscpu command).

DBA often encounters the need to perform batch-running tasks. This kind of batch-running SQL is generally a complex, long-running, resource-consuming SQL. So many batch-running tasks are performed during the business trough and are executed on the slave library to minimize the impact on the business. However, for some run-batch tasks with high data consistency, they need to be executed on the main database, which can easily affect the running of other threads. So now that Resource Group is the gospel of DBA, we can specify the resource group to run the batch task, limit the resources used by the task, and reduce the impact on other threads.

two。 Resource group information view

INFORMATION_SCHEMA.RESOURCE_GROUPS

All defined resource groups are recorded in the RESOURCE_ groups table under the INFORMATION_SCHEMA library:

Mysql > select * from information_schema.resource_groups +-+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | | THREAD_PRIORITY | +-- + | USR_default | USER | 1 | 0-23 | 0 | | SYS_default | SYSTEM | 1 | 0-23 | 0 | +- -+ 2 rows in set (0.00 sec)

By default, MySQL8.0 creates two resource groups, one is USR_default and the other is SYS_default.

PERFORMANCE_SCHEMA.THREADS

In the THREADS table under the PERFORMANCE_SCHEMA library, you can see how the current thread is using resource groups:

Mysql > mysql > select * from performance_schema.threads limit 5 +- -+- -+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST _ INFO | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP | +-+-- + -+ | 1 | thread/sql/main | BACKGROUND | NULL | mysql | NULL | 96053 | NULL | YES | YES | NULL | 9130 | SYS_default | 3 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL | NULL | NULL | | NULL | YES | YES | NULL | 9135 | SYS_default | 4 | thread/innodb/io_log_thread | BACKGROUND | NULL | NULL | | | NULL | YES | YES | NULL | 9136 | SYS_default | | 5 | thread/innodb/io_read_thread | BACKGROUND | NULL | | NULL | YES | YES | NULL | 9138 | SYS_default | | 6 | thread/innodb/io_read_thread | BACKGROUND | NULL | NULL | | NULL | YES | YES | NULL | 9137 | SYS_default | +-+-- -+- -+-+ 5 rows in set (0.00 sec)

The RESOURCE_GROUP field shows which resource group the thread is using.

| | how to use resource groups |

Suppose we now need to create a resource group for the batch task.

1. Create a resource group mysql > CREATE RESOURCE GROUP Batch- > TYPE = USER- > VCPU = 2-3-> THREAD_PRIORITY = 10 sec OK, 0 rows affected (0.03 sec) mysql > select * group +-+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | | THREAD_PRIORITY | +-- + | USR_default | USER | 1 | 0-23 | 0 | SYS_default | SYSTEM | 1 | 0-23 | 0 | | Batch | USER | 1 | 2-3 | 10 | +- -+ 3 rows in set (0.00 sec)

two。 Specify the use of resource groups

There are two ways to bind the created Batch resource group to the thread of execution:

Mode one

Find the thread ID that needs to be bound to execute from the PERFORMANCE_ schema. Threads table (Note: THREAD_ID in the THREADS table is not equivalent to the ID of SHOW PROCESSLIST)

Check the THREAD_ID of the thread that needs to be bound:

Mysql > select * from performance_schema.threads where TYPE='FOREGROUND' +- -+- -+-+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_TIME | PROCESSLIST_STATE | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP | +-+- -- + -+- +-+ | 61 | thread/sql/compress_gtid_table | FOREGROUND | | 5 | NULL | Daemon | 96613 | Suspending | NULL | 1 | NULL | YES | YES | NULL | 9211 | SYS_default | | 65 | Thread/sql/one_connection | FOREGROUND | 10 | root | localhost | NULL | Query | 0 | Sending data | select * from performance_schema.threads where TYPE='FOREGROUND' | NULL | NULL | YES | YES | Socket | 9741 | USR_default | +-+- -- + -+- -+-+ 2 rows in set (0.00 sec)

Bind the thread to the Batch resource group:

SET RESOURCE GROUP Batch FOR 65

View the binding results:

Mysql > select THREAD_ID,NAME,TYPE,PROCESSLIST_ID,RESOURCE_GROUP from performance_schema.threads where TYPE='FOREGROUND' +-+-+ | THREAD_ID | NAME | TYPE | PROCESSLIST_ID | RESOURCE _ GROUP | +-+-+ | 61 | thread/sql/compress_gtid_table | FOREGROUND | 5 | SYS_default | | 65 | thread/sql/one_connection | FOREGROUND | 10 | Batch | +-+-- -+ 2 rows in set (0.00 sec)

Mode two

Specify the resource group used by SQL as Optimizer Hints:

SELECT / * + RESOURCE_GROUP (Batch) * / * FROM T2

3. Modify resource group configuration

There may be not enough CPU resources for batch tasks, so you need to modify the configuration of the resource group.

ALTER RESOURCE GROUP Batch VCPU = 10-20

Modify the resource group priority:

ALTER RESOURCE GROUP Batch THREAD_PRIORITY = 5

Prohibit the use of resource groups:

ALTER RESOURCE GROUP Batch DISABLE FORCE;4. Delete a resource group

You can delete resource groups that are not in use

DROP RESOURCE GROUP Batch; | use precautions

SQL statements such as CREATE RESOURCE GROUP, ALTER RESOURCE GROUP, DROP RESOURCE GROUP, etc., are not recorded in binlog and are not copied to the slave library.

If MySQL has the thread pool plug-in installed, you cannot use the RESOURCE GROUP feature

RESOURCE GROUP feature is not supported on macOS platform

Resource group priority (priorities) cannot be specified on FreeBSD and Solaris platforms, and all threads are running in a state of 0.

You need to enable the CAP_SYS_NICE feature on the Linux platform to use RESOURCE GROUP

# check whether mysqld process enables CAP_SYS_NICE feature [root@qdata-sto3 / root] # getcap / home/mysql/program/mysql8.0/bin/mysqld# enable CAP_SYS_NICE feature for mysqld process [root@qdata-sto3 / root] # setcap cap_sys_nice+ep / home/mysql/program/mysql8.0/bin/mysqld# check whether [root@qdata-sto3 / root] # getcap / home/mysql/program/mysql8.0/ is enabled successfully Bin/mysqld/home/mysql/program/mysql8.0/bin/mysqld = cap_sys_nice+ep

There are only five levels of thread priority on the Windows platform, which are THREAD_PRIORITY_HIGHEST, THREAD_PRIORITY_ABOVE_NORMAL, THREAD_PRIORITY_NORMAL, THREAD_PRIORITY_BELOW_NORMAL, and THREAD_PRIORITY_LOWEST.

| | Summary |

Resource Group is a resource management feature added by MySQL in 8.0. from a DBA point of view, it can be more flexible and convenient to manage the resources used by the database. Oracle has already introduced the feature of Resource Manager at 10g. At present, MySQL's Resource Group can only control the resources of CPU, but it is believed that more and more types of resources can be controlled by Resource Group in the future. Look forward to MySQL8.0 becoming a mature and stable version as soon as possible.

| | author profile |

Shen Gang Walk technology database technology experts are familiar with MySQL database operation mechanism, rich experience in database and replication architecture fault diagnosis, performance tuning, database backup recovery and migration.

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.