MySQL 8.4 Release Notes
MySQL 支持资源组的创建和管理，并允许将服务器中运行的线程分配到特定的组，以便线程根据组可用的资源执行。组属性可以控制其资源，从而允许或限制组中线程的资源消耗。DBA 可以根据不同的工作负载适当修改这些属性。MySQL supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads.
目前，CPU 时间是一种可管理的资源，用“虚拟 CPU”这一概念表示，包括 CPU 核心、超线程、硬件线程等术语。服务器在启动时确定可用的虚拟 CPU 数量，并且具有相应权限的数据库管理员可以将这些 CPU 关联到资源组，并将线程分配给各个组。Currently, CPU time is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. The server determines at startup how many virtual CPUs are available, and database administrators with appropriate privileges can associate these CPUs with resource groups and assign threads to groups.
例如，为了管理不需要高优先级执行的批处理作业的执行，DBA 可以创建一个 Batch 资源组，并根据服务器的繁忙程度上调或下调其优先级。（也许分配给该组的批处理作业在白天应以较低优先级运行，而在夜间以较高优先级运行。）DBA 还可以调整可分配给该组的 CPU 集合。可以启用或禁用资源组，以控制线程是否可以分配给它们。For example, to manage execution of batch jobs that need not
execute with high priority, a 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 the group should run at lower priority during the
day and at higher priority during the night.) The DBA can also
adjust the set of CPUs available to the group. Groups can be
enabled or disabled to control whether threads are assignable to
them.
以下部分描述了在 MySQL 中使用资源组的各个方面：The following sections describe aspects of resource group use in MySQL:
Important
在某些平台或 MySQL 服务器配置中，资源组不可用或存在限制。特别是，对于某些安装方法，Linux 系统可能需要手动操作。详情请参阅 资源组限制。On some platforms or MySQL server configurations, resource groups are unavailable or have limitations. In particular, Linux systems might require a manual step for some installation methods. For details, see Resource Group Restrictions.
这些功能为 MySQL 中的资源组管理提供了 SQL 接口：These capabilities provide the SQL interface for resource group management in MySQL:
SQL 语句可以用于创建、修改和删除资源组，并可以将线程分配到资源组。优化器提示可以用于将单独的语句分配到资源组。SQL statements enable creating, altering, and dropping resource groups, and enable assigning threads to resource groups. An optimizer hint enables assigning individual statements to resource groups.
资源组权限可以控制哪些用户可以执行资源组操作。Resource group privileges provide control over which users can perform resource group operations.
信息模式
RESOURCE_GROUPS表显示有关资源组定义的信息，性能模式threads表显示每个线程的资源组分配情况。The Information SchemaRESOURCE_GROUPStable exposes information about resource group definitions and the Performance Schemathreadstable shows the resource group assignment for each thread.状态变量提供每个管理 SQL 语句的执行计数。Status variables provide execution counts for each management SQL statement.
资源组具有定义该组的属性。所有属性都可以在创建组时设置。有些属性在创建时是固定的；其他属性可以在此之后随时修改。Resource groups have attributes that define the group. All attributes can be set at group creation time. Some attributes are fixed at creation time; others can be modified any time thereafter.
这些属性在资源组创建时定义，不能修改：These attributes are defined at resource group creation time and cannot be modified:
每个组都有一个名称。资源组名称是类似于表和列名称的标识符，除非包含特殊字符或是保留字，否则在 SQL 语句中无需加引号。组名称不区分大小写，最多可以有 64 个字符。Each group has a name. Resource group names are identifiers like table and column names, and need not be quoted in SQL statements unless they contain special characters or are reserved words. Group names are not case-sensitive and may be up to 64 characters long.
每个组都有一个类型，该类型可以是
SYSTEM或USER。资源组类型会影响可分配给该组的优先级范围，如后文所述。该属性与允许的优先级差异一起，使系统线程能够被识别，从而在与用户线程争用 CPU 资源时保护它们。Each group has a type, which is eitherSYSTEMorUSER. The resource group type affects the range of priority values assignable to the group, as described later. This attribute together with the differences in permitted priorities enables system threads to be identified so as to protect them from contention for CPU resources against user threads.系统线程和用户线程对应于性能模式
threads表中列出的后台和前台线程。System and user threads correspond to background and foreground threads as listed in the Performance Schemathreadstable.
这些属性在资源组创建时定义，并且此后可以随时修改：These attributes are defined at resource group creation time and can be modified any time thereafter:
CPU 亲和性是资源组可以使用的虚拟 CPU 的集合。亲和性可以是可用 CPU 的任何非空子集。如果某个组没有亲和性，它可以使用所有可用的 CPU。The CPU affinity is the set of virtual CPUs the resource group can use. An affinity can be any nonempty subset of the available CPUs. If a group has no affinity, it can use all available CPUs.
线程优先级是分配给资源组的线程的执行优先级。优先级值的范围从 -20（最高优先级）到 19（最低优先级）。系统组和用户组的默认优先级均为 0。The thread priority is the execution priority for threads assigned to the resource group. Priority values range from -20 (highest priority) to 19 (lowest priority). The default priority is 0, for both system and user groups.
系统组允许的优先级高于用户组，确保用户线程的优先级永远不会高于系统线程：System groups are permitted a higher priority than user groups, ensuring that user threads never have a higher priority than system threads:
对于系统资源组，允许的优先级范围是 -20 到 0。For system resource groups, the permitted priority range is -20 to 0.
对于用户资源组，允许的优先级范围是 0 到 19。For user resource groups, the permitted priority range is 0 to 19.
每个组可以启用或禁用，使管理员能够控制线程分配。线程只能分配给已启用的组。Each group can be enabled or disabled, affording administrators control over thread assignment. Threads can be assigned only to enabled groups.
默认情况下，有一个系统组和一个用户组，分别命名为SYS_default和USR_default。这些默认组不能被删除，且其属性不能被修改。每个默认组没有 CPU 亲和性，优先级为 0。By default, there is one system group and one user group, named
SYS_default and
USR_default, respectively. These default
groups cannot be dropped and their attributes cannot be
modified. Each default group has no CPU affinity and priority 0.
新创建的系统和用户线程会分别分配到SYS_default和USR_default组。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 at group creation time. After a group has been created, its attributes can be modified, with the exception of the name and type attributes.
要创建和管理用户定义的资源组，请使用以下 SQL 语句：To create and manage user-defined resource groups, use these SQL statements:
CREATE RESOURCE GROUP创建一个新的组。参见 第 15.7.2.2 节，“CREATE RESOURCE GROUP 语句”。CREATE RESOURCE GROUPcreates a new group. See Section 15.7.2.2, “CREATE RESOURCE GROUP Statement”.ALTER RESOURCE GROUP修改一个现有的组。参见 第 15.7.2.1 节，“ALTER RESOURCE GROUP 语句”。ALTER RESOURCE GROUPmodifies an existing group. See Section 15.7.2.1, “ALTER RESOURCE GROUP Statement”.DROP RESOURCE GROUP删除现有的资源组。参见 第 15.7.2.3 节，“DROP RESOURCE GROUP 语句”。DROP RESOURCE GROUPdrops an existing group. See Section 15.7.2.3, “DROP RESOURCE GROUP Statement”.
这些语句需要 RESOURCE_GROUP_ADMIN 权限。Those statements require the
RESOURCE_GROUP_ADMIN privilege.
要管理资源组分配，请使用以下功能：To manage resource group assignments, use these capabilities:
SET RESOURCE GROUP将线程分配到一个组。参见 第 15.7.2.4 节，“SET RESOURCE GROUP 语句”。SET RESOURCE GROUPassigns threads to a group. See Section 15.7.2.4, “SET RESOURCE GROUP Statement”.RESOURCE_GROUP优化器提示将单个语句分配到一个组。参见 第 10.9.3 节，“优化器提示”。TheRESOURCE_GROUPoptimizer hint assigns individual statements to a group. See Section 10.9.3, “Optimizer Hints”.
这些操作需要 RESOURCE_GROUP_ADMIN 或 RESOURCE_GROUP_USER 权限。Those operations require the
RESOURCE_GROUP_ADMIN or
RESOURCE_GROUP_USER privilege.
资源组定义存储在 resource_groups 数据字典表中，以便在服务器重启后组能够保持。因为 resource_groups 是数据字典的一部分，用户无法直接访问。可以通过信息架构 RESOURCE_GROUPS 表来获取资源组信息，该表是数据字典表的一个视图。参见 第 28.3.26 节，“INFORMATION_SCHEMA RESOURCE_GROUPS 表”。Resource group definitions are stored in the
resource_groups data dictionary table so that
groups persist across server restarts. Because
resource_groups is part of the data
dictionary, it is not directly accessible by users. Resource
group information is available using the Information Schema
RESOURCE_GROUPS table, which is
implemented as a view on the data dictionary table. See
Section 28.3.26, “The INFORMATION_SCHEMA RESOURCE_GROUPS Table”.
最初，RESOURCE_GROUPS 表具有这些描述默认组的行：Initially, the RESOURCE_GROUPS
table has these rows describing the default groups:
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: 0-3
THREAD_PRIORITY: 0
*************************** 2. row ***************************
RESOURCE_GROUP_NAME: SYS_default
RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
VCPU_IDS: 0-3
THREAD_PRIORITY: 0
THREAD_PRIORITY 的值为 0，表示默认优先级。VCPU_IDS 的值显示一个包含所有可用 CPU 的范围。对于默认组，显示的值取决于 MySQL 服务器运行的系统。The THREAD_PRIORITY values are 0, indicating
the default priority. The VCPU_IDS values
show a range comprising all available CPUs. For the default
groups, the displayed value varies depending on the system on
which the MySQL server runs.
之前讨论中提到过一个场景，涉及一个名为 Batch 的资源组，用于管理不需要高优先级执行的批处理作业。要创建这样的组，可使用类似以下的语句：Earlier discussion mentioned a scenario involving a resource
group named Batch to manage execution of
batch jobs that need not execute with high priority. To create
such a group, use a statement similar to this:
CREATE RESOURCE GROUP Batch TYPE = USER VCPU = 2-3 -- assumes a system with at least 4 CPUs THREAD_PRIORITY = 10;
要验证资源组是否按预期创建，请检查 RESOURCE_GROUPS 表：To verify that the resource group was created as expected, check
the RESOURCE_GROUPS table:
mysql>SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPSWHERE RESOURCE_GROUP_NAME = 'Batch'\G*************************** 1. row *************************** RESOURCE_GROUP_NAME: Batch RESOURCE_GROUP_TYPE: USER RESOURCE_GROUP_ENABLED: 1 VCPU_IDS: 2-3 THREAD_PRIORITY: 10
如果 THREAD_PRIORITY 的值是 0 而不是 10，请检查您的平台或系统配置是否限制了资源组功能；请参阅 资源组限制。If the THREAD_PRIORITY value is 0 rather than
10, check whether your platform or system configuration limits
the resource group capability; see
Resource Group Restrictions.
要将线程分配到 Batch 组，请执行以下操作：To assign a thread to the Batch group, do
this:
SET RESOURCE GROUP Batch FOR thread_id;
此后，命名线程中的语句将使用 Batch 组资源执行。Thereafter, statements in the named thread execute with
Batch group resources.
如果会话自己的当前线程应在 Batch 组中，请在会话中执行此语句：If a session's own current thread should be in the
Batch group, execute this statement within
the session:
SET RESOURCE GROUP Batch;
此后，会话中的语句将使用 Batch 组资源执行。Thereafter, statements in the session execute with
Batch group resources.
要使用 Batch 组执行单条语句，请使用 RESOURCE_GROUP 优化器提示：To execute a single statement using the Batch
group, use the RESOURCE_GROUP
optimizer hint:
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);
分配给 Batch 组的线程将使用其资源执行，这些资源可以根据需要进行修改：Threads assigned to the Batch group execute
with its resources, which can be modified as desired:
当系统负载较高时，减少分配给该组的 CPU 数量，降低其优先级，或者（如图所示）两者同时进行：For times when the system is highly loaded, decrease the number of CPUs assigned to the group, lower its priority, or (as shown) both:
ALTER RESOURCE GROUP Batch VCPU = 3 THREAD_PRIORITY = 19;
当系统负载较轻时，增加分配给该组的 CPU 数量，提高其优先级，或者（如图所示）两者同时进行：For times when the system is lightly loaded, increase the number of CPUs assigned to the group, raise its priority, or (as shown) both:
ALTER RESOURCE GROUP Batch VCPU = 0-3 THREAD_PRIORITY = 0;
资源组管理仅限于发生管理的本地服务器。资源组的 SQL 语句和对 resource_groups 数据字典表的修改不会写入二进制日志，也不会被复制。Resource group management is local to 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.
在某些平台或 MySQL 服务器配置中，资源组不可用或存在限制：On some platforms or MySQL server configurations, resource groups are unavailable or have limitations:
如果安装了线程池插件，则资源组不可用。Resource groups are unavailable if the thread pool plugin is installed.
在 macOS 上，资源组不可用，因为 macOS 不提供将 CPU 绑定到线程的 API。Resource groups are unavailable on macOS, which provides no API for binding CPUs to a thread.
在 FreeBSD 和 Solaris 上，资源组线程优先级会被忽略。（实际上，所有线程都以优先级 0 运行。）尝试更改优先级会导致警告：On FreeBSD and Solaris, resource group thread priorities are ignored. (Effectively, all threads run at priority 0.) Attempts to change priorities result in a warning:
mysql>
ALTER RESOURCE GROUP abc THREAD_PRIORITY = 10;Query OK, 0 rows affected, 1 warning (0.18 sec) mysql>SHOW WARNINGS;+---------+------+-------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------+ | Warning | 4560 | Attribute thread_priority is ignored (using default value). | +---------+------+-------------------------------------------------------------+在 Linux 上，除非设置了
CAP_SYS_NICE权限，否则资源组线程优先级将被忽略。授予进程CAP_SYS_NICE权限会启用一系列特权；请查阅 http://man7.org/linux/man-pages/man7/capabilities.7.html 获取完整列表。启用此权限时请小心。On Linux, resource groups thread priorities are ignored unless theCAP_SYS_NICEcapability is set. GrantingCAP_SYS_NICEcapability to a process enables a range of privileges; consult http://man7.org/linux/man-pages/man7/capabilities.7.html for the full list. Please be careful when enabling this capability.在使用 systemd 且内核支持 Ambient Capabilities（Linux 4.3 或更高版本）的 Linux 平台上，启用
CAP_SYS_NICE权限的推荐方法是修改 MySQL 服务文件，同时保持 mysqld 二进制文件不变。要调整 MySQL 的服务文件，请使用以下步骤：On Linux platforms using systemd and kernel support for Ambient Capabilities (Linux 4.3 or newer), the recommended way to enableCAP_SYS_NICEcapability is to modify the MySQL service file and leave the mysqld binary unmodified. To adjust the service file for MySQL, use this procedure:为你的平台运行相应的命令：Run the appropriate command for your platform:
Oracle Linux、Red Hat 和 Fedora 系统：Oracle Linux, Red Hat, and Fedora systems:
$>
sudo systemctl edit mysqldSUSE、Ubuntu 和 Debian 系统：SUSE, Ubuntu, and Debian systems:
$>
sudo systemctl edit mysql
使用编辑器，将以下文本添加到服务文件中：Using an editor, add the following text to the service file:
[Service] AmbientCapabilities=CAP_SYS_NICE
重启 MySQL 服务。Restart the MySQL service.
如果无法如上所述启用
CAP_SYS_NICE功能，也可以使用setcap命令手动设置，指定mysqld可执行文件的路径（这需要sudo权限）。你可以使用getcap检查功能。例如：If you cannot enable theCAP_SYS_NICEcapability as just described, it can be set manually using the setcap command, specifying the path name to the mysqld executable (this requires sudo access). You can check the capabilities using getcap. For example:$>
sudo setcap cap_sys_nice+ep$>/path/to/mysqldgetcap/path/to/mysqld/path/to/mysqld= cap_sys_nice+ep作为安全措施，将mysqld二进制文件的执行权限限制为
root用户和拥有mysql组成员身份的用户：As a safety measure, restrict execution of the mysqld binary to therootuser and users withmysqlgroup membership:$>
sudo chown root:mysql$>/path/to/mysqldsudo chmod 0750/path/to/mysqldImportant如果需要手动使用 setcap，则必须在每次重新安装后进行。If manual use of setcap is required, it must be performed after each reinstall.
在 Windows 上，线程以五个线程优先级级别之一运行。资源组线程优先级范围为 -20 到 19，如下表所示映射到这些级别。On Windows, threads run at one of five thread priority levels. The resource group thread priority range of -20 to 19 maps onto those levels as indicated in the following table.
表 7.6 Windows 上的资源组线程优先级Table 7.6 Resource Group Thread Priority on Windows
优先级范围Priority Range Windows 优先级等级Windows Priority Level -20 到 -10-20 to -10 THREAD_PRIORITY_HIGHESTTHREAD_PRIORITY_HIGHEST-9 到 -1-9 to -1 THREAD_PRIORITY_ABOVE_NORMALTHREAD_PRIORITY_ABOVE_NORMAL0 THREAD_PRIORITY_NORMALTHREAD_PRIORITY_NORMAL1 到 101 to 10 THREAD_PRIORITY_BELOW_NORMALTHREAD_PRIORITY_BELOW_NORMAL11 到 1911 to 19 THREAD_PRIORITY_LOWESTTHREAD_PRIORITY_LOWEST