MySQL8.0资源组

MySQL8.0资源组
MySQL8中引入了资源组（ResourceGroups）的概念，它可以设定某一类SQL语句所允许使用的资源（目前只包括CPU）。在高并发的系统中，资源组可以保证关键交易的性能，例如可以设定市场统计类的交易在白天使用较少的资源，以免影响客户的交易，在晚上可以使用较多的资源。

1.1查询资源组

在information_schema.resource_groups视图中可以查询资源中的信息，默认有两个资源组：
root@db 12:00: [(none)]> select * from information_schema.resource_groups\G
*************************** 1. row ***************************
RESOURCE_GROUP_NAME: USR_default
RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1
VCPU_IDS: 0x302D37
THREAD_PRIORITY: 0
*************************** 2. row ***************************
RESOURCE_GROUP_NAME: SYS_default
RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
VCPU_IDS: 0x302D37
THREAD_PRIORITY: 0
2 rows in set (0.00 sec)

1.2创建资源组

使用create resource group语句可以创建资源组，创建一个batch用户资源组的例子如下：
create resource group Batch type = user vcpu = 2-3 thread_priority = 10;

创建完成后查看这个用户资源组的信息如下：
root@db 12:07: [(none)]> create resource group Batch type = user vcpu = 2-3 thread_priority = 10;
Query OK, 0 rows affected (0.00 sec)

root@db 12:07: [(none)]> 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: 0x322D33
THREAD_PRIORITY: 10
1 row in set (0.00 sec)

1.3修改资源组的属性

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

root@db 12:07: [(none)]> alter resource group Batch vcpu = 3 thread_priority = 19;
Query OK, 0 rows affected (0.00 sec)

root@db 12:08: [(none)]> 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: 0x33
THREAD_PRIORITY: 19
1 row in set (0.00 sec)

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

root@db 12:08: [(none)]> alter resource group Batch vcpu = 0-3 thread_priority = 0;
Query OK, 0 rows affected (0.01 sec)

root@db 12:09: [(none)]> 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: 0x302D33
THREAD_PRIORITY: 0
1 row in set (0.00 sec)

注意，用户线程的优先级不能小于0：

mysql> alter resource group Batch vcpu = 3 thread_priority = -9;
root@db 12:09: [(none)]> 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].

1.4使用资源组

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

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

root@db 12:10: [(none)]> drop resource group Batch;
Query OK, 0 rows affected (0.00 sec)

root@db 12:10: [(none)]> select * from information_schema.resource_groups where resource_group_name = 'Batch'\G
Empty set (0.00 sec)

要将线程分配给Batch资源组，执行以下操作：
set resource group Batch for thread_id;
当thread_id有多个时，中间用逗号隔开。

如果要把当前线程设定到 Batch资源组中，在会话中执行以下语句：
mysql> set resource group batch;
此后，会话中的语句将使用Batch资源组的资源进行执行。

要使用Batch组执行单个语句，请使用 resource_group优化程序提示：

root@db 12:19: [testdb]> desc t2;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| a | int | YES | MUL | NULL | |
| b | int | YES | | NULL | |
+-------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert /*+ resource_group(Batch) */ into t2 values(2);

root@db 12:21: [testdb]> insert /*+ resource_group(Batch) */ into t2 values(200,3,211);
Query OK, 1 row affected (0.01 sec)

root@db 12:21: [testdb]> select * from t2 where id=200;
+-----+------+------+
| id | a | b |
+-----+------+------+
| 200 | 3 | 211 |
+-----+------+------+
1 row in set (0.00 sec)

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

查询线程使用的资源组
可以在performance_schema.threads视图中的resource_group字段查询线程使用的资源组，相应的命令和输出结果如下：
root@db 12:21: [testdb]> select thread_id, resource_group from performance_schema.threads;
+-----------+----------------+
| thread_id | resource_group |
+-----------+----------------+
| 1 | SYS_default |
| 3 | SYS_default |
| 4 | SYS_default |
| 5 | SYS_default |
| 6 | SYS_default |
| 7 | SYS_default |
| 8 | SYS_default |
| 9 | SYS_default |
| 10 | SYS_default |
| 11 | SYS_default |
| 12 | SYS_default |
| 13 | SYS_default |
| 14 | SYS_default |
| 15 | SYS_default |
| 16 | SYS_default |
| 17 | SYS_default |
| 18 | SYS_default |
| 19 | SYS_default |
| 24 | SYS_default |
| 25 | SYS_default |
| 26 | SYS_default |
| 27 | SYS_default |
| 28 | SYS_default |
| 29 | SYS_default |
| 30 | SYS_default |
| 31 | USR_default |
| 32 | USR_default |
| 33 | SYS_default |
| 37 | SYS_default |
| 38 | SYS_default |
| 39 | SYS_default |
| 40 | SYS_default |
| 41 | SYS_default |
| 42 | SYS_default |
| 43 | SYS_default |
| 44 | SYS_default |
| 45 | SYS_default |
| 47 | SYS_default |
| 52 | Batch |
+-----------+----------------+
39 rows in set (0.01 sec)

1.5资源组的限制

资源组目前在使用中还是一些限制：
1). 如果安装了线程池插件，则资源组不可用。
2). 资源组在macOS上不可用，因为它不提供用于将CPU绑定到线程的API。
3). 在FreeBSD和Solaris上，忽略资源组线程优先级，尝试更改优先级会导致警告。实际上，所有线程都以优先级0运行。
4). 在Linux上，需要对mysqld进程设置CAP_SYS_NICE功能，否则将忽略资源组线程优先级。

1.6在Linux上设置CAP_SYS_NICE功能

CAP_SYS_NICE可以使用setcap命令手动设置该功能，使用getcap检查功能。相应命令和输出结果如下：
# setcap cap_sys_nice+ep /usr/sbin/mysqld
# getcap /usr/sbin/mysqld
/usr/sbin/mysqld = cap_sys_nice+ep

[root@node1 ~]# ps -ef|grep mysqld
root 72607 1 0 11:16 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysql.pid
mysql 73202 72607 0 11:16 ? 00:00:12 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/error.log --open-files-limit=65535 --pid-file=/data/mysql/tmp/mysql.pid --socket=/data/mysql/tmp/mysql.sock --port=3306

[root@node1 ~]# setcap cap_sys_nice+ep /usr/local/mysql/bin/mysqld
[root@node1 ~]# getcap /usr/local/mysql/bin/mysqld
/usr/local/mysql/bin/mysqld = cap_sys_nice+ep

或者使用sudo systemctl edit mysql在MySQL服务里增加下面的内容：
[Service] AmbientCapabilities=CAP_SYS_NICE
然后重新启动MySQL服务，设置线程优先级才能生效。

[root@node1 ~]# sudo systemctl edit mysql

GNU nano 2.3.1 File: /etc/systemd/system/mysql.service.d/.#override.confa11e0d165ad68f99
##输入
[Service] AmbientCapabilities=CAP_SYS_NICE

保存：ctrl+o

1.7Windows平台上的线程的优先级

线程优先级范围 Windows优先级
-20到-10 THREAD_PRIORITY_HIGHEST
-9到-1 THREAD_PRIORITY_ABOVE_NORMAL
0 THREAD_PRIORITY_NORMAL
1到10 THREAD_PRIORITY_BELOW_NORMAL
10到19 THREAD_PRIORITY_LOWEST