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

|  資源組介紹

1.簡介

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的福音了，我們可以對跑批任務指定執行的資源組，限制任務使用的資源，減少對其他執行緒的影響。

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預設會建立兩個資源組，一個是 USR_default 另一個是 SYS_default 。

• PERFORMANCE_SCHEMA.THREADS

PERFORMANCE_SCHEMA庫下的THREADS表中，可以檢視當前執行緒使用資源組的情況:

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 欄位顯示執行緒使用的是哪個資源組。

| 如何使用資源組

假設我們現在需要對跑批任務建立一個資源組。

1.建立一個資源組

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.指定使用資源組

將建立的Batch資源組繫結到執行的執行緒上，有兩種方式：

• 方式一 
  從PERFORMANCE_SCHEMA.THREADS表中查詢需要繫結執行的執行緒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;

檢視繫結結果:

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.修改資源組配置

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

ALTER RESOURCE GROUP Batch VCPU = 10-20;

修改資源組優先順序：

ALTER RESOURCE GROUP Batch THREAD_PRIORITY = 5;

禁止使用資源組：

ALTER RESOURCE GROUP Batch DISABLE FORCE;

4.刪除資源組

對於不用的資源組可以刪除

DROP RESOURCE GROUP Batch;

| 使用注意點

• CREATE RESOURCE GROUP、ALTER RESOURCE GROUP、DROP RESOURCE GROUP等SQL語句並不會被記錄到binlog中，不會被複制到從庫。

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

• macOS 平臺上不支援RESOURCE GROUP特性

• FreeBSD 和 Solaris 平臺上無法指定資源組優先順序(priorities),所有執行緒執行在0狀態。

• Linux 平臺上需要開啟 CAP_SYS_NICE 特性才能使用RESOURCE GROUP

# 檢查mysqld程式是否開啟CAP_SYS_NICE特性
[root@qdata-sto3 /root]
#getcap /home/mysql/program/mysql8.0/bin/mysqld
# 給mysqld程式開啟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平臺上執行緒優先順序只有5個等級，分別為THREAD_PRIORITY_HIGHEST、THREAD_PRIORITY_ABOVE_NORMAL、THREAD_PRIORITY_NORMAL、THREAD_PRIORITY_BELOW_NORMAL、THREAD_PRIORITY_LOWEST

| 總結

Resource Group是MySQL在8.0中增加的資源管理的特性，從一個DBA的角度，可以更加靈活、便捷的管理資料庫使用的資源，這是一個令人眼前一亮的特性。Oracle在10g的時候已經推出了Resource Manager的特性，MySQL的Resource Group目前還很簡單隻能控制CPU的資源，但是相信後續Resource Group能操控的資源型別會越來越多。期待MySQL8.0早日成為一個成熟穩定的版本。

|  作者簡介

沈 剛·沃趣科技資料庫技術專家

熟悉MySQL資料庫執行機制，豐富的資料庫及複製架構故障診斷、效能調優、資料庫備份恢復及遷移經驗。