MySQL8.0——Resource Group（资源组）

原创嗨あおぇ云和恩墨 2023-05-29

673

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

MySQL 支持资源组的创建和管理，并且允许将服务器中运行的线程分配给特定的组，以便线程根据资源执行可供组使用。组属性启用对其的控制资源，用于启用或限制线程的资源消耗在组中。DBA 可以根据需要修改这些属性不同的工作负载。

目前，CPU 是一种可管理的资源，由 “虚拟 CPU”的概念是一个包含 CPU 的术语内核、超线程、硬件线程等。服务器在启动时确定有多少虚拟 CPU 可用，以及具有适当权限的数据库管理员可以关联这些 CPU 具有资源组并将线程分配给组。

ySQL 支持资源组的创建和管理，并且允许将服务器中运行的线程分配给特定的组，以便线程根据资源执行可供组使用。组属性启用对其的控制资源，用于启用或限制线程的资源消耗在组中。DBA 可以根据需要修改这些属性不同的工作负载。
	目前，CPU 是一种可管理的资源，由 “虚拟 CPU”的概念是一个包含 CPU 的术语 内核、超线程、硬件线程等。服务器 在启动时确定有多少虚拟 CPU 可用，以及 具有适当权限的数据库管理员可以关联 这些 CPU 具有资源组并将线程分配给组。

	例如，管理不需要的批处理作业的执行 以高优先级执行，DBA 可以创建资源组并调整其优先级 向上或向下取决于服务器的繁忙程度。（也许是批次 分配给组的作业应在 白天，夜间优先级更高。DBA还可以 调整组可用的 CPU 集。组可以是 启用或禁用以控制线程是否可分配给 他们
	MySQL是单进程多线程的程序，MySQL线程包括后台线程（Master Thread、IO Thread、Purge Thread等），以及用户线程。在8.0之前，所有线程的优先级都是一样的，并且所有的线程的资源都是共享的。但是在MySQL8.0之后，由于Resource Group特性的引入，我们可以来通过资源组的方式修改线程的优先级以及所能使用的资源，可以指定不同的线程使用特定的资源。
	在目前版本中DBA只能操控CPU资源，并且控制的最小力度为vCPU，即操作系统逻辑CPU核数（可以通过lscpu命令查看可控制CPU总数）。 
DBA经常会遇到需要执行跑批任务的需求，这种跑批的SQL一般都是很复杂、运行时间长、消耗资源多的SQL。所以很多跑批任务都是在业务低峰期的时候执行，并且在从库上执行，尽可能降低对业务产生影响。但是对于一些数据一致性比较高的跑批任务，需要在主库上执行，在跑批任务运行的过程中很容易影响到其他线程的运行。那么现在Resource Group就是DBA的福音了，我们可以对跑批任务指定运行的资源组，限制任务使用的资源，减少对其他线程的影响。例如，管理不需要的批处理作业的执行以高优先级执行，DBA 可以创建资源组并调整其优先级向上或向下取决于服务器的繁忙程度。（也许是批次分配给组的作业应在白天，夜间优先级更高。DBA还可以调整组可用的 CPU 集。组可以是启用或禁用以控制线程是否可分配给他们
MySQL是单进程多线程的程序，MySQL线程包括后台线程（Master Thread、IO Thread、Purge Thread等），以及用户线程。在8.0之前，所有线程的优先级都是一样的，并且所有的线程的资源都是共享的。但是在MySQL8.0之后，由于Resource Group特性的引入，我们可以来通过资源组的方式修改线程的优先级以及所能使用的资源，可以指定不同的线程使用特定的资源。
在目前版本中DBA只能操控CPU资源，并且控制的最小力度为vCPU，即操作系统逻辑CPU核数（可以通过lscpu命令查看可控制CPU总数）。
DBA经常会遇到需要执行跑批任务的需求，这种跑批的SQL一般都是很复杂、运行时间长、消耗资源多的SQL。所以很多跑批任务都是在业务低峰期的时候执行，并且在从库上执行，尽可能降低对业务产生影响。但是对于一些数据一致性比较高的跑批任务，需要在主库上执行，在跑批任务运行的过程中很容易影响到其他线程的运行。那么现在Resource Group就是DBA的福音了，我们可以对跑批任务指定运行的资源组，限制任务使用的资源，减少对其他线程的影响。

资源组元素

这些功能为资源组提供 SQL 接口 MySQL 中的管理：

SQL 语句支持创建、更改和删除资源组，并启用将线程分配给资源组。优化器提示允许分配单个对资源组的语句。
资源组权限提供对哪些用户的控制可以执行资源组操作。
信息架构RESOURCE_GROUPS表公开有关资源组定义和性能架构线程表显示每个线程的资源组分配。
状态变量为每个变量提供执行计数管理 SQL 语句。

root@db 20:56:  [(none)]> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
root@db 20:57:  [(none)]> select * from information_schema.resource_groups;
+---------------------+---------------------+------------------------+--------------------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS           | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+--------------------+-----------------+
| USR_default         | USER                |                      1 | 0x302D31           |               0 |
| SYS_default         | SYSTEM              |                      1 | 0x302D31           |               0 |
+---------------------+---------------------+------------------------+--------------------+-----------------+
2 rows in set (0.00 sec)
root@db 20:57:  [(none)]> 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 | NULL             | NULL             | mysql          | NULL                |           210531 | NULL              | NULL             |             NULL | NULL | YES          | YES     | NULL            |       114442 | SYS_default    |
|         3 | thread/innodb/io_ibuf_thread | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL              | NULL             |             NULL | NULL | YES          | YES     | NULL            |       114451 | SYS_default    |
|         4 | thread/innodb/io_log_thread  | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL              | NULL             |             NULL | NULL | YES          | YES     | NULL            |       114452 | SYS_default    |
|         5 | thread/innodb/io_read_thread | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL              | NULL             |             NULL | NULL | YES          | YES     | NULL            |       114453 | SYS_default    |
|         6 | thread/innodb/io_read_thread | BACKGROUND |           NULL | NULL             | NULL             | NULL           | NULL                |             NULL | NULL              | NULL             |             NULL | NULL | YES          | YES     | NULL            |       114454 | SYS_default    |
+-----------+------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
5 rows in set (0.01 sec)


#说明：
INFORMATION_SCHEMA库下的RESOURCE_GROUPS表中记录了所有定义的资源组的情况:(MySQL8.0默认会创建两个资源组，一个是USR_default另一个是SYS_default。)
select * from information_schema.resource_groups;

PERFORMANCE_SCHEMA库下的THREADS表中，可以查看当前线程使用资源组的情况:（其中RESOURCE_GROUP字段显示线程使用的是哪个资源组。）
select * from performance_schema.threads limit 5;

#创建资源组
root@db 20:57:  [(none)]> CREATE RESOURCE GROUP yunhe
 -> TYPE = USER
 -> VCPU = 0
 -> THREAD_PRIORITY = 10;
Query OK, 0 rows affected (0.03 sec)
说明：
yunhe 为资源组名字（根据自己的需求来取名字）
type=user来源是用户端的慢SQL
vcpu=0 给它分配到哪个CPU核上（你可以用cat /proc/cpuinfo | grep processor查看CPU有多少核），或者使用top查看哪个核心较为空闲
thread_priority为优先级别，范围是0到19，19是最低优先级，0是最高优先级。

#查看创建的资源组：
root@db 21:01:  [(none)]> select * from information_schema.resource_groups;
+---------------------+---------------------+------------------------+--------------------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS           | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+--------------------+-----------------+
| USR_default         | USER                |                      1 | 0x302D31           |               0 |
| SYS_default         | SYSTEM              |                      1 | 0x302D31           |               0 |
| yunhe               | USER                |                      1 | 0x30               |              10 |
+---------------------+---------------------+------------------------+--------------------+-----------------+
3 rows in set (0.00 sec)

资源组的应用（两种方式）

第一种

资源组属性

资源组具有定义组的属性。都可以在创建组时设置属性。一些属性在创建时是固定的;其他的可以随时修改此后。

这些属性在资源组创建时定义，并且无法修改：

每个组都有一个名称。资源组名称是标识符如表名和列名，无需在 SQL 中引号语句，除非它们包含特殊字符或保留字。组名称不区分大小写，可能长度不超过 64 个字符。
每个组都有一个类型，即或。这资源组类型影响优先级值的范围可分配给组，如下所述。此属性以及允许的优先级的差异使系统线程能够被识别，从而保护它们来自对用户线程的 CPU 资源争用。SYSTEM``USER
系统和用户线程对应于后台和性能架构线程表中列出的前台线程。

这些属性在资源组创建时定义，并且此后可以随时修改：

CPU 关联性是资源的虚拟 CPU 集组可以使用。相关性可以是可用的中央处理器。如果组没有关联，则可以使用 all 可用的中央处理器。
线程优先级是线程的执行优先级分配给资源组。优先级值范围从 -20（最高优先级）到 19（最低优先级）。默认对于系统和用户组，优先级均为 0。
允许系统组的优先级高于用户组，确保用户线程永远不会有更高的优先级高于系统线程：
- 对于系统资源组，允许的优先级范围为 -20 到 0。
- 对于用户资源组，允许的优先级范围为 0 到 19。
每个组都可以启用或禁用，提供管理员控制线程分配。线程可以仅分配给已启用的组。

#查看线程：
root@db 21:03:  [(none)]> select thread_id from  performance_schema.threads;
root@db 21:18:  [(none)]>select *  from  performance_schema.threads\G
#绑定将线程与Batch资源组绑定：
root@db 21:23:  [(none)]> SET RESOURCE GROUP yunhe FOR 64;
Query OK, 0 rows affected (0.01 sec)

查看绑定的结果：

root@db 21:24:  [(none)]> select THREAD_ID,NAME,TYPE,PROCESSLIST_ID,RESOURCE_GROUP from performance_schema.threads where TYPE='FOREGROUND';
+-----------+--------------------------------+------------+----------------+----------------+
| THREAD_ID | NAME                           | TYPE       | PROCESSLIST_ID | RESOURCE_GROUP |
+-----------+--------------------------------+------------+----------------+----------------+
|        42 | thread/sql/event_scheduler     | FOREGROUND |              5 | SYS_default    |
|        45 | thread/sql/compress_gtid_table | FOREGROUND |              7 | SYS_default    |
|        64 | thread/sql/one_connection      | FOREGROUND |             26 | yunhe          |
+-----------+--------------------------------+------------+----------------+----------------+
3 rows in set (0.00 sec)

第二种

采用Optimizer Hints的方式指定SQL使用的资源组：

root@db 21:34:  [qiang]> SELECT /*+ RESOURCE_GROUP(Batch) */ * FROM qiang.user_profile;
root@db 21:34:  [qiang]> select THREAD_ID,NAME,TYPE,PROCESSLIST_ID,RESOURCE_GROUP from performance_schema.threads where TYPE='FOREGROUND';
+-----------+--------------------------------+------------+----------------+----------------+
| THREAD_ID | NAME                           | TYPE       | PROCESSLIST_ID | RESOURCE_GROUP |
+-----------+--------------------------------+------------+----------------+----------------+
|        42 | thread/sql/event_scheduler     | FOREGROUND |              5 | SYS_default    |
|        45 | thread/sql/compress_gtid_table | FOREGROUND |              7 | SYS_default    |
|        65 | thread/sql/one_connection      | FOREGROUND |             27 | USR_default    |
+-----------+--------------------------------+------------+----------------+----------------+
3 rows in set (0.00 sec)

修改资源组及删除:

修改资源组配置
可能跑批任务使用CPU资源不够，那就需要修改资源组的配置。
ALTER RESOURCE GROUP yunhe VCPU = 10-20;
修改资源组优先级：
ALTER RESOURCE GROUP yunhe THREAD_PRIORITY = 5;
禁止使用资源组：
ALTER RESOURCE GROUP yunhe DISABLE FORCE;
删除资源组
对于不用的资源组可以删除
DROP RESOURCE GROUP yunhe;

资源组使用限制

Linux 平台上需要开启 CAP_SYS_NICE 特性才能使用RESOURCE GROUP
检查mysqld进程是否开启CAP_SYS_NICE特性
getcap /usr/local/mysql/bin/mysqld
给mysqld进程开启CAP_SYS_NICE特性
setcap cap_sys_nice+ep /usr/local/mysql/bin/mysqld
或者：：
systemctl edit mysqld
[Service]
AmbientCapabilities=CAP_SYS_NICE
另外：
mysql 线程池开启后RG失效。
freebsd,solaris 平台thread_priority 失效。

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL8.0——Resource Group（资源组）

资源组元素

资源组的应用（两种方式）

第一种

资源组属性

查看绑定的结果：

第二种

修改资源组及删除:

资源组使用限制

评论