{"id":2493,"date":"2018-09-07T12:03:57","date_gmt":"2018-09-07T12:03:57","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2493"},"modified":"2023-04-28T04:51:56","modified_gmt":"2023-04-28T04:51:56","slug":"mysql-8-resource-group-introduction-and-dynamic-allocation","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-8-resource-group-introduction-and-dynamic-allocation","title":{"rendered":"MySQL 8 Resource Group – introduction and dynamic allocation"},"content":{"rendered":"\r\n

MySQL 8 is GA and it has a variety of new features. Recently, we happened to work with resource groups to restrict resource utilization for a MySQL thread.<\/p>\r\n\r\n\r\n\r\n

That\u2019s why I thought of writing this blog post: to give you a quick introduction to resource groups, an idea of how to dynamically allocate them and to discuss a related bug report.<\/p>\r\n\r\n\r\n\r\n

Introduction To Resource Groups:<\/h3>\r\n\r\n\r\n\r\n

A resource group, a\u00a0new feature in MySQL 8, is the entity that defines the allowed resources consumption for threads pertaining to that group. Group attributes enable control over resources and hence the database threads get\u00a0restricted with those limits.<\/p>\r\n\r\n\r\n\r\n

Resource groups introduce\u00a0the following associated components:
1. SQL to manage the resource groups (create, alter, assign, drop).
2. Resource groups related privileges.
3. The information_schema.resource_groups table, which holds groups details.
4. A new column was added to the performance_schema.threads table to show us the assignments of threads to respective resource groups.<\/p>\r\n\r\n\r\n\r\n

<\/p>\r\n\r\n\r\n\r\n

We have two types of resource group: SYSTEM and USER:
The\u00a0SYSTEM type applies to background threads, with priority values ranging from -20 (highest) to 0 (normal).
The\u00a0USER type applies to foreground threads, with priority values ranging from 0 to 19 (lowest).<\/p>\r\n\r\n\r\n\r\n

Keep in mind that USER resource groups can only be assigned to user threads (foreground threads) and their priority should be in the 0-19 range. On the other hand, SYSTEM groups can only be assigned to background threads and their priority can\u2019t exceed 0, and can\u2019t be lower than -20.<\/p>\r\n\r\n\r\n\r\n

For example, creating a USER type resource group with -20 priority results in error:<\/p>\r\n\r\n\r\n\r\n

CREATE RESOURCE GROUP <\/span>Batch<\/span>\r\nTYPE <\/span>=<\/span> USER\r\nTHREAD_PRIORITY <\/span>=<\/span> -<\/span>20<\/span><\/pre>\r\n\r\n\r\n\r\n
Error:<\/p>\r\n\r\n\r\n\r\n
#3654 - Invalid thread priority value -20 for User resource group Batch. Allowed range is [0, 19].<\/span><\/pre>\r\n\r\n\r\n\r\n
Assigning a USER type resource group to a SYSTEM thread causes the following error:<\/p>\r\n\r\n\r\n\r\n
UNKNOWN_CODE_PLEASE_REPORT<\/span>:<\/span> Unable<\/span> to bind resource <\/span>group<\/span> Batch<\/span> with<\/span> thread id <\/span>(<\/span>4<\/span>).(<\/span>Resource<\/span> group<\/span> type <\/span>and<\/span> thread type doesn<\/span>'t match.).<\/span><\/pre>\r\n\r\n\r\n\r\n
The resource groups defined can be seen in the information_schema.resource_groups table. Initially, we have two default resource groups:\u00a0SYS_default<\/u>\u00a0and\u00a0USR_default<\/u>. Both default groups hold normal priority and have no CPU restrictions.<\/p>\r\n\r\n\r\n\r\n
In regards to grants, two new user privileges were introduced:<\/strong>
The RESOURCE_GROUP_ADMIN is for\u00a0creating, altering and dropping operations along with resource group assignments.
The RESOURCE_GROUP_USER is for the assignment of threads to resource groups.<\/p>\r\n\r\n\r\n\r\n
Creating a RESOURCE GROUP<\/b><\/p>\r\n\r\n\r\n\r\n
The following is a sample create statement for resource group:<\/p>\r\n\r\n\r\n\r\n
CREATE RESOURCE GROUP rg1\r\nTYPE <\/span>=<\/span> USER\r\nVCPU <\/span>=<\/span> 5<\/span>-<\/span>7<\/span>\r\nTHREAD_PRIORITY <\/span>=<\/span> 19<\/span>;<\/span><\/pre>\r\n\r\n\r\n\r\n
Here, rg1 is a USER resource group with CPU affinity of 5 to 7 (only those CPUs can be used) and with THREAD_PRIORITY 19 (lowest). If we do not specify the VCPU option, all CPUs can be used and we can only consider specifying the priority.<\/p>\r\n\r\n\r\n\r\n
The details about the RESOURCE GROUP are exposed via information_schema.resource_group table.<\/p>\r\n\r\n\r\n\r\n
mysql<\/span>><\/span> SELECT <\/span>*<\/span> FROM INFORMATION_SCHEMA<\/span>.<\/span>RESOURCE_GROUPS\\G\r\n<\/span>***************************<\/span> 1.<\/span> row <\/span>***************************<\/span>\r\nRESOURCE_GROUP_NAME<\/span>:<\/span> USR_default\r\nRESOURCE_GROUP_TYPE<\/span>:<\/span> USER\r\nRESOURCE_GROUP_ENABLED<\/span>:<\/span> 1<\/span>\r\nVCPU_IDS<\/span>:<\/span> 0<\/span>-<\/span>0<\/span>\r\nTHREAD_PRIORITY<\/span>:<\/span> 0<\/span>\r\n***************************<\/span> 2.<\/span> row <\/span>***************************<\/span>\r\nRESOURCE_GROUP_NAME<\/span>:<\/span> SYS_default\r\nRESOURCE_GROUP_TYPE<\/span>:<\/span> SYSTEM\r\nRESOURCE_GROUP_ENABLED<\/span>:<\/span> 1<\/span>\r\nVCPU_IDS<\/span>:<\/span> 0<\/span>-<\/span>0<\/span>\r\nTHREAD_PRIORITY<\/span>:<\/span> 0<\/span>\r\n***************************<\/span> 3.<\/span> row <\/span>***************************<\/span>\r\nRESOURCE_GROUP_NAME<\/span>:<\/span> rg1\r\nRESOURCE_GROUP_TYPE<\/span>:<\/span> USER\r\nRESOURCE_GROUP_ENABLED<\/span>:<\/span> 1<\/span>\r\nVCPU_IDS<\/span>:<\/span> 5<\/span>-<\/span>7<\/span>\r\nTHREAD_PRIORITY<\/span>:<\/span> 19<\/span><\/pre>\r\n\r\n\r\n\r\n
Enabling and assigning resource groups<\/strong><\/p>\r\n\r\n\r\n\r\n
To set the resource group for your current session, we can execute the following command:<\/p>\r\n\r\n\r\n\r\n
SET RESOURCE GROUP rg1<\/span>;<\/span><\/pre>\r\n\r\n\r\n\r\n
Or we can assign it to a specific thread as follows:<\/p>\r\n\r\n\r\n\r\n
SET RESOURCE GROUP rg1 FOR <\/span><thread_id><\/span>;<\/span><\/pre>\r\n\r\n\r\n\r\n
There is currently no way to allocate resource groups to a specific user, and hence, to all threads created by that account. However, there is a way to assign them to queries using hints as follows:<\/p>\r\n\r\n\r\n\r\n
INSERT <\/span>\/*+ RESOURCE_GROUP(rg1) *\/<\/span> INTO sample_table VALUES<\/span>(<\/span>2<\/span>);<\/span><\/pre>\r\n\r\n\r\n\r\n
Dynamically Assigning Resource Groups To Threads<\/h3>\r\n\r\n\r\n\r\n
Resource groups give the DBA control over CPU utilization and prioritization of threads so we can better manage the workloads. For example, for a batch process, the THREAD_PRIORITY can be lowered while for user transactions it can be increased.<\/p>\r\n\r\n\r\n\r\n
In such a scenario, the attempt was made to set the resource groups dynamically based on the logic provided in a stored procedure.<\/p>\r\n\r\n\r\n\r\n
Consider the following user_rg table:<\/p>\r\n\r\n\r\n\r\n
\r\n\r\n\r\n\r\n\r\n\r\n
_user<\/strong><\/td>\r\n_resource_group<\/strong><\/td>\r\n<\/tr>\r\n
reports<\/td>\r\nrg1<\/td>\r\n<\/tr>\r\n
qa_users<\/td>\r\nrg2<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/figure>\r\n\r\n\r\n\r\n
The idea is that a process will call a stored procedure to set the resource group for that specific session before executing queries.<\/p>\r\n\r\n\r\n\r\n
SELECT _resource_group <\/span>into<\/span> rg_name <\/span>from<\/span> user_rg <\/span>where<\/span> _user<\/span>=<\/span>current_user<\/span>();<\/span>\r\nSET <\/span>@cmd<\/span> =<\/span> CONCAT<\/span>(<\/span>\"SET RESOURCE GROUP \"<\/span>,<\/span> @rg_name<\/span> );<\/span>\r\nPREPARE stmt FROM <\/span>@cmd<\/span>;<\/span>\r\nEXECUTE stmt<\/span>;<\/span><\/pre>\r\n\r\n\r\n\r\n
This should work, but it doesn\u2019t ! Let\u2019s see how the manual execution goes:<\/p>\r\n\r\n\r\n\r\n
mysql<\/span>><\/span> select<\/span> @rg_name<\/span>;<\/span>\r\n+-----------+<\/span>\r\n|<\/span> @rg_name<\/span>  |<\/span>\r\n+-----------+<\/span>\r\n|<\/span> rg1       <\/span>|<\/span>\r\n+-----------+<\/span>\r\n1<\/span> row <\/span>in<\/span> set<\/span> (<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nmysql<\/span>><\/span> SET <\/span>@cmd<\/span> =<\/span> CONCAT<\/span>(<\/span>\"SET RESOURCE GROUP \"<\/span>,<\/span> @rg_name<\/span> );<\/span>\r\nQuery<\/span> OK<\/span>,<\/span> 0<\/span> rows affected <\/span>(<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nmysql<\/span>><\/span> PREPARE stmt FROM <\/span>@cmd<\/span>;<\/span>\r\nERROR <\/span>1295<\/span> (<\/span>HY000<\/span>):<\/span> This<\/span> command <\/span>is<\/span> not<\/span> supported <\/span>in<\/span> the prepared statement protocol yet\r\nmysql<\/span>><\/span> select<\/span> @cmd<\/span>;<\/span>\r\n+------------------------+<\/span>\r\n|<\/span> @cmd<\/span>                   |<\/span>\r\n+------------------------+<\/span>\r\n|<\/span> SET RESOURCE GROUP rg1 <\/span>|<\/span>\r\n+------------------------+<\/span>\r\n1<\/span> row <\/span>in<\/span> set<\/span> (<\/span>0.00<\/span> sec<\/span>)<\/span><\/pre>\r\n\r\n\r\n\r\n
We receive an optimistic error conveying\u00a0\u201cThis command is not supported\u00a0yet!<\/strong>\u201d<\/em>\u00a0That derails our plan to dynamically allocate the resource groups.<\/p>\r\n\r\n\r\n\r\n
But wait, the solution is simple. We just have to use IF\u2026ELSE:<\/p>\r\n\r\n\r\n\r\n
If<\/span> (<\/span>@rg_name<\/span> =<\/span> 'rg1'<\/span>)<\/span> then<\/span>\r\nset<\/span> resource <\/span>group<\/span> rg1<\/span>;<\/span>\r\nelse<\/span>\r\nset<\/span> resource <\/span>group<\/span> rg2<\/span>;<\/span>\r\nend<\/span> if<\/span>;<\/span><\/pre>\r\n\r\n\r\n\r\n
See a sample procedure here that explains the idea:<\/p>\r\n\r\n\r\n\r\n
mysql<\/span>><\/span> delimiter <\/span>\/\/<\/span>\r\nmysql<\/span>><\/span> drop procedure <\/span>if<\/span> exists testproc<\/span>\/\/<\/span>\r\nQuery<\/span> OK<\/span>,<\/span> 0<\/span> rows affected <\/span>(<\/span>0.08<\/span> sec<\/span>)<\/span>\r\n\r\nmysql<\/span>><\/span> CREATE PROCEDURE testproc <\/span>(<\/span>in_user varchar<\/span>(<\/span>20<\/span>))<\/span>\r\n-><\/span> BEGIN<\/span>\r\n-><\/span> set<\/span> @val<\/span>=<\/span>''<\/span>;<\/span>\r\n-><\/span> select<\/span> _resource_group <\/span>into<\/span> @val<\/span> from<\/span> user_rg <\/span>where<\/span> _user<\/span>=<\/span>in_type<\/span>;<\/span>\r\n-><\/span> if<\/span> (<\/span>@val<\/span>=<\/span>'rg1'<\/span>)<\/span> then<\/span>\r\n-><\/span> select<\/span> \"setting resource group RG1\"<\/span>;<\/span>\r\n-><\/span> elseif <\/span>(<\/span>@val<\/span>=<\/span>'rg2'<\/span>)<\/span> then<\/span>\r\n-><\/span> select<\/span> \"setting resource group RG2\"<\/span>;<\/span>\r\n-><\/span> else<\/span> select<\/span> \"Ignoring resource group changes.\"<\/span>;<\/span>\r\n-><\/span> end<\/span> if<\/span>;<\/span>\r\n-><\/span> END<\/span>\/\/<\/span>\r\ndelimiter <\/span>;<\/span>\r\nmysql<\/span>><\/span> delimiter <\/span>;<\/span>\r\nmysql<\/span>><\/span> call testproc<\/span>(<\/span>'reports'<\/span>);<\/span>\r\n+----------------------------+<\/span>\r\n|<\/span> setting resource <\/span>group<\/span> RG1 <\/span>|<\/span>\r\n+----------------------------+<\/span>\r\n|<\/span> setting resource <\/span>group<\/span> RG1 <\/span>|<\/span>\r\n+----------------------------+<\/span>\r\n1<\/span> row <\/span>in<\/span> set<\/span> (<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nQuery<\/span> OK<\/span>,<\/span> 0<\/span> rows affected <\/span>(<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nmysql<\/span>><\/span> call testproc<\/span>(<\/span>'qa_users'<\/span>);<\/span>\r\n+----------------------------+<\/span>\r\n|<\/span> setting resource <\/span>group<\/span> RG2 <\/span>|<\/span>\r\n+----------------------------+<\/span>\r\n|<\/span> setting resource <\/span>group<\/span> RG2 <\/span>|<\/span>\r\n+----------------------------+<\/span>\r\n1<\/span> row <\/span>in<\/span> set<\/span> (<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nQuery<\/span> OK<\/span>,<\/span> 0<\/span> rows affected <\/span>(<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nmysql<\/span>><\/span> call testproc<\/span>(<\/span>'different_user'<\/span>)<\/span>\r\n+-----------------------------------+<\/span>\r\n|<\/span> Ignoring<\/span> resource <\/span>group<\/span> changes<\/span>.<\/span>  |<\/span>\r\n+-----------------------------------+<\/span>\r\n|<\/span> Ignoring<\/span> resource <\/span>group<\/span> changes<\/span>.<\/span>  |<\/span>\r\n+-----------------------------------+<\/span>\r\n1<\/span> row <\/span>in<\/span> set<\/span> (<\/span>0.00<\/span> sec<\/span>)<\/span>\r\n\r\nQuery<\/span> OK<\/span>,<\/span> 0<\/span> rows affected<\/span>,<\/span> 1<\/span> warning <\/span>(<\/span>0.00<\/span> sec<\/span>)<\/span><\/pre>\r\n\r\n\r\n\r\n
In above example, in_user could be current_user() and thus we can actually execute the SET RESOURCE GROUP command.<\/p>\r\n\r\n\r\n\r\n
Note that as per the documentation and in my experience, the SET command does work for PREPARE but the SET RESOURCE GROUP command fails. We have filed the following bug report for the error:\u00a0https:\/\/bugs.mysql.com\/bug.php?id=91876<\/a><\/p>\r\n\r\n\r\n\r\n
As such,\u00a0resource group is a new feature and presently the only resource that is manageable is CPU. It would have been a good idea to allow allocating resource groups to system users and thus threads created by them.<\/p>\r\n\r\n\r\n\r\n
That\u2019s it for now, folks, go explore the resource groups and the brand new MySQL 8!<\/p>\r\n\r\n\r\n\r\n
PS: This article was originally published on The Pythian Blog here.<\/a><\/em><\/p>\r\n","protected":false},"excerpt":{"rendered":"Introduction to resource groups in MySQL 8 and dynamical allocation to threads\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[467,468,427,469,470,471],"class_list":{"0":"post-2493","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-dynamically-allocating-resource-groups","9":"tag-introduction-to-resource-groups","10":"tag-mysql","11":"tag-mysql-8","12":"tag-mysql-resource-group","13":"tag-resource-group"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2493","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2493"}],"version-history":[{"count":4,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2493\/revisions"}],"predecessor-version":[{"id":3265,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2493\/revisions\/3265"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2493"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2493"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2493"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}