MySQL8.0新特性~资源组的应用

进击的CJR 2024-08-01

215

MySQL的一个问题

相信经常使用 MySQL 的同学都碰到过让自己头痛的问题：某个「烂」 SQL 占用了大量的 CPU 资源，导致其它正常查询不能被响应，甚至导致 MySQL 直接挂掉，为了解决这个问题，通过我们会用下面两个办法来解决：

1、设置 max_execution_time 来阻止长时间运行的 SQL。当然，后果就是当你确实有个 SQL 就是要跑这么久的时候，会被一视同仁的干掉；
2、自己通过外部工具或脚本，周期性检查，并杀掉相应 thread。且不说在负载高的时候，可能这个脚本都无法正常连接 MySQL Server，周期性检查这种明显具备滞后性的操作，对业务的影响是不可避免的。

RG 的引入可以在很大程度上解决这个问题，对于复杂、执行时间长、消耗资源多，但又不希望它们影响业务的任务（比如一些计算、统计性质的批处理），我们可以对这部份任务设置特定的资源组，限制任务的使用资源，避免对其它业务线程的影响，保证服务质量。

什么是资源组

MySQL-8.0中新增了resource group资源组的功能。MySQL资源组的想法来源很简单：每个资源组是一个资源独立的单位，每个资源组能够容纳一个或者多个MySQL线程。拥有设置资源组权限的DBA们能够创建、配置资源组以及指定、切换MySQL线程从属的资源组，从而更加精准地管控MySQL。

每个MySQL资源组的属性包括：

RESOURCE_GROUP_NAME: 资源组名

RESOURCE_GROUP_TYPE: 资源组类型，MySQL所有的线程分为两类：system（background） 和 user（foreground），前者包括Master Thread、IO Thread， Purge Thread等，后者则为用户连接线程。因此资源组的类型也只有两类：FOREGROUND和BACKGROUND。后台线程只能放入BACKGROUND类型的资源组中。

RESOURCE_GROUP_ENABLED: 是否启用，1表示启用， 0表示未启用。

VCPU_IDS: 可以使用的VCPU编号，系统可用的VCPU编号可以通过 cat /proc/cpuinfo命令查看，processor字段就是对应的VCPU的编号。

THREAD_PRIORITY: 线程优先级，范围[-19,20]，数字越低优先级越高。默认优先级为0。系统线程允许设置优先级低于0，用户线程不允许设置优先级低于0。

复制

资源组功能引入了两个新的权限：
RESOURCE_GROUP_ADMIN（用于资源组创建、修改、删除的权限）
RESOURCE_GROUP_USER（用于指定MySQL线程到资源组的权限）。

系统启动之后，会创建两个默认的资源组：
用户资源组 (USR_Default) 和系统资源组 (SYS_Default)。

默认的资源组的CPU优先级为0，并且不绑定CPU。所有用线程将被归为USR_Default中，所有系统后台线程被归为SYS_Default中。

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: 0x302D31
       THREAD_PRIORITY: 0
*************************** 2. row ***************************
   RESOURCE_GROUP_NAME: SYS_default
   RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0x302D31
       THREAD_PRIORITY: 0
2 rows in set (0.03 sec)
复制

资源组的使用

拥有RESOURCE_GROUP_ADMIN权限的用户可以使用以下命令操作资源组

创建新的资源组： 
CREATE RESOURCE GROUP 'name' TYPE=SYSTEM|USER [VCPU=num|start-end[,num|start-end]*] [THREAD_PRIORITY=num] [ENABLE|DISABLE] ;


修改资源组:
ALTER RESOURCE GROUP 'name' [VCPU=num|start-end[,num|start-end]*] [THREAD_PRIORITY=num] [ENABLE|DISABLE] [FORCE] ;


删除资源组：DROP RESOURCE GROUP 'name' [FORCE];

配置好的资源组可以使用SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS命令查看。
在创建和配置好资源组后，可以通过SET RESOURCE GROUP 'name' FOR thread_id1, thread_id2,thread_id3, ...;命令将线程指定到目标的资源组中。

值得注意的是show processlist显示的session id，并不是thread id。

thread id的查询方法是：SELECT * FROM performance_schema.threads;

复制

一些示例

使用create resource group语句可以创建资源组，创建一个batch用户资源组的例子如下：
mysql> 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

在系统高负载的时间段，减少分配给资源组的CPU数量，并降低其优先级：
mysql> alter resource group Batch vcpu = 3 thread_priority = 19;

在系统负载较轻的情况下，增加分配给组的CPU数量，并提高其优先级：
mysql> alter resource group Batch vcpu = 0-3 thread_priority = 0;

注意，用户线程的优先级不能小于0：
mysql> alter resource group Batch vcpu = 3 thread_priority = -9;
ERROR 3654 (HY000): Invalid thread priority value -9 for User resource group Batch. Allowed range is [0, 19]


激活Batch资源组的命令如下：
my sql> alter resource group Batch enable;

删除Batch资源组的命令如下：
my sql> drop resource group Batch;

要将线程分配给Batch资源组，执行以下操作：
my sql> set resource group Batch for thread_id;

当thread_id有多个时，中间用逗号隔开。
如果要把当前线程设定到 Batch资源组中，在会话中执行以下语句：
my sql> set resource group Batch;

此后，会话中的语句将使用Batch资源组的资源进行执行 。
要使用Batch组执行单个语句 ，请使用 resource_group优化程序提示：
my sql> insert /*+ resource_group(Batch) */ into t2 values (2);

在SQL语句里设置提示的方法可以和MySQL的中间件结合起来使用，例如ProxySQL支持在SQL语句中增加提示。


查询线程使用的资源组
可以在performance_schema.threads视图中的resource_group字段查询线程使用的资源组，相应的命令和输出结果如下：
mysql> select thread_id, resource_group from performance_schema.threads where thread_id=10054;
复制

Linux 系统上为了使用线程优先级（THREAD PRIORITY）功能，需要给予 MySQLD 二进制文件 CAP_SYS_NICE 能力。由于我们使用的是 systemd，配置也相对简单：配置文件添加

[Service]
AmbientCapabilities=CAP_SYS_NICE

保存退出，并通过 systemctl restart mysql 重启 MySQL Server 即可。

资源组测试实验

(1)创建一个能打爆CPU的存储过程如下

delimiter $$
CREATE PROCEDURE bomb(OUT ot BIGINT)
BEGIN
DECLARE cnt BIGINT DEFAULT 0;
SET @FUTURE = (SELECT NOW() + INTERVAL 120 SECOND);
WHILE NOW() < @FUTURE DO
SET cnt = (SELECT cnt + 1);
END WHILE;
SELECT cnt INTO ot;
END
$$
delimiter ;
复制

这是一个MySQL存储过程，名为bomb。它接受一个输出参数ot，类型为BIGINT。

声明一个局部变量cnt，类型为BIGINT，并初始化为0。
设置一个名为@FUTURE的会话变量，其值为当前时间加上120秒。
使用WHILE循环，当当前时间小于@FUTURE时，执行循环体。
在循环体中，将cnt的值加1。
循环结束后，将cnt的值赋给输出参数ot。

这个存储过程的主要作用是计算从调用开始到120秒后的时间间隔内，循环执行了多少次。最后将这个计数值作为输出参数返回。

(2)创建两个资源组，创建一个低优选级 RG low，线程优先级为 15，创建一个高优先级 RG high，线程优先级为 5（请注意优先级数字越低，表示优先级越高），两个 RG 绑定了同一个 CPU：CPU0

mysql> create resource group low type = user vcpu = 0 thread_priority = 15;


mysql> create resource group high type = user vcpu = 0 thread_priority = 5;

复制

(3)创建一个额外的资源组

mysql> create resource group rg type = user vcpu = 1;

mysql> set resource group rg;

复制

（4）开启三个会话测试
第一个会话
要将线程分配给rg资源组，执行以下操作：
set resource group rg;
call bomb(@output);

观察cpu
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14779 mysql 20 0 1795384 383776 16896 R 99.7 18.8 6:47.66 mysqld

第二个会话
要将线程分配给low资源组，执行以下操作：
set resource group low;
call bomb(@output);

观察cpu
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7386 mysql 20 0 1795384 383776 16896 R 98.3 18.8 2:40.12 mysqld
14779 mysql 20 0 1795384 383776 16896 R 98.0 18.8 7:20.07 mysqld

第三个会话
要将线程分配给high资源组，执行以下操作：
set resource group high;
call bomb(@output);

观察cpu
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14779 mysql 20 0 1795384 383776 16896 R 95.7 18.8 8:45.54 mysqld
11152 mysql 20 0 1795384 383776 16896 R 53.2 18.8 3:10.61 mysqld
7386 mysql 20 0 1795384 383776 16896 R 46.2 18.8 3:57.16 mysqld

可以看到cpu 0被各占用50%左右。

再查看被调用次数

mysql> select @output;
+----------+
| @output  |
+----------+
| 18815447 |
+----------+

low资源组
mysql> select @output;
+----------+
| @output  |
+----------+
| 20334995 |
+----------+

high资源组

mysql> select @output;
+----------+
| @output  |
+----------+
| 29447877 |
+----------+

复制

可以看到session1只占用自己的cpu,session 2和session 3 共用一个cpu。实现了资源的分割使用，另外，从output返回结果，看出优先级高的session3，被调用的次数更多。

墨力计划