MySQL8.0——Resource Group（資源組）

|  資源組介紹

1.簡介

MySQL是單進(jìn)程多線程的程序，MySQL線程包括后臺(tái)線程（Master Thread、IO Thread、Purge Thread等），以及用戶線程。在8.0之前，所有線程的優(yōu)先級(jí)都是一樣的，并且所有的線程的資源都是共享的。但是在MySQL8.0之后，由于Resource Group特性的引入，我們可以來通過資源組的方式修改線程的優(yōu)先級(jí)以及所能使用的資源，可以指定不同的線程使用特定的資源。 

在目前版本中DBA只能操控CPU資源，并且控制的最小力度為vCPU，即操作系統(tǒng)邏輯CPU核數(shù)（可以通過 lscpu 命令查看可控制CPU總數(shù)）。 

DBA經(jīng)常會(huì)遇到需要執(zhí)行跑批任務(wù)的需求，這種跑批的SQL一般都是很復(fù)雜、運(yùn)行時(shí)間長、消耗資源多的SQL。所以很多跑批任務(wù)都是在業(yè)務(wù)低峰期的時(shí)候執(zhí)行，并且在從庫上執(zhí)行，盡可能降低對(duì)業(yè)務(wù)產(chǎn)生影響。但是對(duì)于一些數(shù)據(jù)一致性比較高的跑批任務(wù)，需要在主庫上執(zhí)行，在跑批任務(wù)運(yùn)行的過程中很容易影響到其他線程的運(yùn)行。那么現(xiàn)在Resource Group就是DBA的福音了，我們可以對(duì)跑批任務(wù)指定運(yùn)行的資源組，限制任務(wù)使用的資源，減少對(duì)其他線程的影響。

2.資源組信息查看

• INFORMATION_SCHEMA.RESOURCE_GROUPS

INFORMATION_SCHEMA庫下的RESOURCE_GROUPS表中記錄了所有定義的資源組的情況:

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)

MySQL8.0默認(rèn)會(huì)創(chuàng)建兩個(gè)資源組，一個(gè)是 USR_default 另一個(gè)是 SYS_default 。

• PERFORMANCE_SCHEMA.THREADS

PERFORMANCE_SCHEMA庫下的THREADS表中，可以查看當(dāng)前線程使用資源組的情況:

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 | NULL            | NULL            | mysql          | NULL                |            96053 | NULL              | NULL            |            NULL | NULL | YES          | YES    | NULL            |        9130 | SYS_default    |
|        3 | thread/innodb/io_ibuf_thread | BACKGROUND |          NULL | NULL            | NULL            | NULL          | NULL                |            NULL | NULL              | NULL            |            NULL | NULL | YES          | YES    | NULL            |        9135 | SYS_default    |
|        4 | thread/innodb/io_log_thread  | BACKGROUND |          NULL | NULL            | NULL            | NULL          | NULL                |            NULL | NULL              | NULL            |            NULL | NULL | YES          | YES    | NULL            |        9136 | SYS_default    |
|        5 | thread/innodb/io_read_thread | BACKGROUND |          NULL | NULL            | NULL            | NULL          | NULL                |            NULL | NULL              | NULL            |            NULL | NULL | YES          | YES    | NULL            |        9138 | SYS_default    |
|        6 | thread/innodb/io_read_thread | BACKGROUND |          NULL | NULL            | NULL            | NULL          | NULL                |            NULL | NULL              | NULL            |            NULL | NULL | YES          | YES    | NULL            |        9137 | SYS_default    |
+-----------+------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
5 rows in set (0.00 sec)

其中 RESOURCE_GROUP 字段顯示線程使用的是哪個(gè)資源組。

| 如何使用資源組

假設(shè)我們現(xiàn)在需要對(duì)跑批任務(wù)創(chuàng)建一個(gè)資源組。

1.創(chuàng)建一個(gè)資源組

mysql> CREATE RESOURCE GROUP Batch
->  TYPE = USER
->  VCPU = 2-3
->  THREAD_PRIORITY = 10;
Query OK, 0 rows affected (0.03 sec)
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 |
| Batch              | USER                |                      1 | 2-3      |              10 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

2.指定使用資源組

將創(chuàng)建的Batch資源組綁定到執(zhí)行的線程上，有兩種方式：

• 方式一 
  從PERFORMANCE_SCHEMA.THREADS表中查找需要綁定執(zhí)行的線程ID(注意：THREADS表中的THREAD_ID和SHOW PROCESSLIST的ID不等同)

查看需要綁定的線程的THREAD_ID：

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_STATE | PROCESSLIST_INFO                                                | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID | RESOURCE_GROUP |
+-----------+--------------------------------+------------+----------------+------------------+------------------+----------------+---------------------+------------------+-------------------+------------------------------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+----------------+
|        61 | thread/sql/compress_gtid_table | FOREGROUND |              5 | NULL            | NULL            | 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)

將線程與Batch資源組綁定：

SET RESOURCE GROUP Batch FOR 65;

查看綁定結(jié)果:

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)

• 方式二

采用Optimizer Hints的方式指定SQL使用的資源組：

SELECT /*+ RESOURCE_GROUP(Batch) */ * FROM t2 ;

3.修改資源組配置

可能跑批任務(wù)使用CPU資源不夠，那就需要修改資源組的配置。

ALTER RESOURCE GROUP Batch VCPU = 10-20;

修改資源組優(yōu)先級(jí)：

ALTER RESOURCE GROUP Batch THREAD_PRIORITY = 5;

禁止使用資源組：

ALTER RESOURCE GROUP Batch DISABLE FORCE;

4.刪除資源組

對(duì)于不用的資源組可以刪除

DROP RESOURCE GROUP Batch;

| 使用注意點(diǎn)

• CREATE RESOURCE GROUP、ALTER RESOURCE GROUP、DROP RESOURCE GROUP等SQL語句并不會(huì)被記錄到binlog中，不會(huì)被復(fù)制到從庫。

• 如果MySQL安裝了thread pool插件，無法使用RESOURCE GROUP特性

• macOS 平臺(tái)上不支持RESOURCE GROUP特性

• FreeBSD 和 Solaris 平臺(tái)上無法指定資源組優(yōu)先級(jí)(priorities),所有線程運(yùn)行在0狀態(tài)。

• Linux 平臺(tái)上需要開啟 CAP_SYS_NICE 特性才能使用RESOURCE GROUP

# 檢查mysqld進(jìn)程是否開啟CAP_SYS_NICE特性
[root@qdata-sto3 /root]
#getcap /home/mysql/program/mysql8.0/bin/mysqld
# 給mysqld進(jìn)程開啟CAP_SYS_NICE特性
[root@qdata-sto3 /root]
#setcap cap_sys_nice+ep /home/mysql/program/mysql8.0/bin/mysqld
# 檢查是否開啟成功
[root@qdata-sto3 /root]
#getcap /home/mysql/program/mysql8.0/bin/mysqld
/home/mysql/program/mysql8.0/bin/mysqld = cap_sys_nice+ep

• Windows平臺(tái)上線程優(yōu)先級(jí)只有5個(gè)等級(jí)，分別為THREAD_PRIORITY_HIGHEST、THREAD_PRIORITY_ABOVE_NORMAL、THREAD_PRIORITY_NORMAL、THREAD_PRIORITY_BELOW_NORMAL、THREAD_PRIORITY_LOWEST

| 總結(jié)

Resource Group是MySQL在8.0中增加的資源管理的特性，從一個(gè)DBA的角度，可以更加靈活、便捷的管理數(shù)據(jù)庫使用的資源，這是一個(gè)令人眼前一亮的特性。Oracle在10g的時(shí)候已經(jīng)推出了Resource Manager的特性，MySQL的Resource Group目前還很簡單只能控制CPU的資源，但是相信后續(xù)Resource Group能操控的資源類型會(huì)越來越多。期待MySQL8.0早日成為一個(gè)成熟穩(wěn)定的版本。

|  作者簡介

沈 剛·沃趣科技數(shù)據(jù)庫技術(shù)專家

熟悉MySQL數(shù)據(jù)庫運(yùn)行機(jī)制，豐富的數(shù)據(jù)庫及復(fù)制架構(gòu)故障診斷、性能調(diào)優(yōu)、數(shù)據(jù)庫備份恢復(fù)及遷移經(jīng)驗(yàn)。