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

沃趣科技發表於2018-07-19

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

資源組介紹

簡介

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

資源組資訊檢視

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

如何使用資源組

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

建立一個資源組

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)
複製程式碼

指定使用資源組

將建立的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 ;
複製程式碼

修改資源組配置

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

ALTER RESOURCE GROUP Batch VCPU = 10-20;
複製程式碼

修改資源組優先順序：

ALTER RESOURCE GROUP Batch THREAD_PRIORITY = 5;
複製程式碼

禁止使用資源組：

ALTER RESOURCE GROUP Batch DISABLE FORCE;
複製程式碼

刪除資源組

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

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早日成為一個成熟穩定的版本。

部落格地址：win-man.github.io/
公眾號：歡迎關注

Resource Manager資源管理之使用組切換分析
2011-12-09
Oracle 資源管理（resource manager）
2014-03-02
Oracle
資源路由resource index 不是get
2020-08-20
路由Index
REST – 如何抽象為資源（Resource）
2019-03-01
REST抽象
REST - 如何抽象為資源（Resource）
2018-03-07
REST抽象
profile的resource limits和資源計劃resource_manager_plan的limit
2018-06-19
MIT
利用資源限制效能診斷resource limit
2011-12-19
MIT
C# Resource Management (資源管理器)
2007-11-16
C#
資料庫資源管理器（Database Resource Manager）
2012-10-29
資料庫Database
group by分組查詢
2021-09-03
MySQL的Group By分組
2014-05-21
MySql
SQL 分組排序group by
2016-06-01
SQL排序
利用Resource Timing監控資源載入速度
2019-03-04
前端效能優化 - Resource Hints 資源預載入
2018-01-08
前端優化
Presto資源組配置
2017-02-12
REST
Open Group開源CDE
2012-08-10
Laravel 路由 resource 方法：定義使用者資源路由
2022-03-02
Laravel路由
MySQL8.0輕鬆搞定GTID組複製
2022-01-21
MySql
HTML 統一資源定位器(Uniform Resource Locators)簡介
2022-09-29
HTMLORM
跨域資源共享 CORS（Cross-origin resource sharing）
2018-05-18
跨域CORSROS
Sql group by 分組取時間最新的一條資料
2016-04-02
SQL
【資源管理器】資源使用者組、資源計劃、資源計劃指令
2015-12-10
Oracle和MySQL分組查詢GROUP BY
2017-07-20
OracleMySql
Visio Studio中的resource.h檔案.rc資源
2013-03-29
Oracle OCP 1Z0 053 Q435(Resource Manager&OHTER_GROUP)
2016-04-03
Oracle
oracle 高階分組group by cube擴充
2015-07-02
Oracle
oracle group by與分組列為null空
2012-12-19
OracleNull
web效能之資源載入時間分析【Resource Timing】【原創】
2020-04-03
Web
《Effective C++》第三版-3. 資源管理（Resource Management）
2024-04-30
C++
OCM實驗-GC資源管理器資源消耗組演示
2014-01-28
GC
中文Google群組資源－轉載
2009-02-14
Go
Oracle DBA的資源和組織
2011-05-11
Oracle
資料庫的sort group by和hash group by
2023-04-10
資料庫
Android外掛化（三）：載入外掛apk中的Resource資源
2016-01-08
AndroidAPK
mysql使用group by實現組內排序實戰
2021-09-09
MySql排序
MySQL GROUP BY分組取欄位最大值
2016-09-20
MySql
[Mysql 查詢語句]——分組查詢group by
2017-03-18
MySql
MySQL 5.7.17 Group Relication（組複製）搭建手冊
2017-02-09
MySql

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

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

資源組介紹

簡介

資源組資訊檢視

如何使用資源組

建立一個資源組

指定使用資源組

修改資源組配置

刪除資源組

使用注意點

總結

相關文章