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/
公眾號：歡迎關注

REST – 如何抽象為資源（Resource）
2019-03-01
REST抽象
資源路由resource index 不是get
2020-08-20
路由Index
REST - 如何抽象為資源（Resource）
2018-03-07
REST抽象
profile的resource limits和資源計劃resource_manager_plan的limit
2018-06-19
MIT
利用Resource Timing監控資源載入速度
2019-03-04
跨域資源共享 CORS（Cross-origin resource sharing）
2018-05-18
跨域CORSROS
Kubernetes Extended Resource 擴充套件資源使用簡介
2024-11-06
套件
group by分組查詢
2021-09-03
Laravel 路由 resource 方法：定義使用者資源路由
2022-03-02
Laravel路由
HTML 統一資源定位器(Uniform Resource Locators)簡介
2022-09-29
HTMLORM
B組group1：A - Artwork
2020-09-27
【kingsql分享】多租戶細粒度資源模型 - New Resource Modeling Scheme
2020-05-27
SQL模型Scheme
web效能之資源載入時間分析【Resource Timing】【原創】
2020-04-03
Web
C組group2 A - Odd Selection
2020-09-26
MySQL8.0輕鬆搞定GTID組複製
2022-01-21
MySql
《Effective C++》第三版-3. 資源管理（Resource Management）
2024-04-30
C++
SpringBoot 專案打包後獲取不到resource下資源的解決
2019-01-06
Spring Boot
資料庫的sort group by和hash group by
2023-04-10
資料庫
Android開發 - 掌握ConstraintLayout（九）分組(Group)
2018-12-03
AndroidAI
mysql使用group by實現組內排序實戰
2021-09-09
MySql排序
人力資源六大模組
2018-12-07
三維基因組相關資源
2024-06-29
POLIR-Society-Organization-Politics: “How”-政治分析+組織建設：資源整合:整合"組織"+"人才"+"資源"+"資本"
2024-11-11
Laravel Resource Routes和API Resource Routes講解
2024-05-15
LaravelAPI
聚合函式及分組與過濾（GROUP BY … HAVING）
2020-11-01
函式
Django筆記十七之group by 分組用法總結
2023-04-08
Django筆記
從零手寫實現 nginx-19-HTTP CORS（Cross-Origin Resource Sharing，跨源資源共享）介紹+解決方案
2024-06-12
NginxHTTPCORSROS
【譯】Resource Hints
2019-01-01
MYSQL學習筆記8: DQL分組查詢(group by)
2024-03-08
MySql筆記
MySQL8.0:倒序索引資料的資料排列方式
2018-12-27
MySql索引
sql中的left切割與 group by後的兩次分組
2018-03-09
SQL
Mysql系列第九講分組查詢詳解（group by & having）
2020-09-30
MySql
系統變數group_replication_group_seeds為空導致MySQL節點無法啟動組複製
2024-11-06
變數MySql
模組化開發靜態資源對映
2019-09-11
DRM - Dynamic Resource Mastering
2019-04-16
AST
Android resource linking failed
2018-11-27
AndroidAI
Metasploit resource命令技巧
2020-03-30
Spring系列.Resource介面
2021-02-19
Spring

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

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

資源組介紹

簡介

資源組資訊檢視

如何使用資源組

建立一個資源組

指定使用資源組

修改資源組配置

刪除資源組

使用注意點

總結

相關文章