Working with resource groups in MySQL 8

Written by Kedar Vaijanapurkar | Sep 5, 2018 4:00:00 AM

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. That’s 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.

Introduction to resource groups:

A resource group, a new 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 restricted with those limits. Resource groups introduce the 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. We have two types of resource group: SYSTEM and USER: The SYSTEM type applies to background threads, with priority values ranging from -20 (highest) to 0 (normal). The USER type applies to foreground threads, with priority values ranging from 0 to 19 (lowest). 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't exceed 0, and can't be lower than -20. For example, creating a USER type resource group with -20 priority results in error:

CREATE RESOURCE GROUP Batch
 TYPE = USER
 THREAD_PRIORITY = -20

Error:

#3654 - Invalid thread priority value -20 for User resource group Batch. Allowed range is [0, 19].

Assigning a USER type resource group to a SYSTEM thread causes the following error:

UNKNOWN_CODE_PLEASE_REPORT: Unable to bind resource group Batch with thread id (4).(Resource group type and thread type doesn't match.).

The resource groups defined can be seen in the information_schema.resource_groups table. Initially, we have two default resource groups: SYS_default and USR_default. Both default groups hold normal priority and have no CPU restrictions. In regards to grants, two new user privileges were introduced: The RESOURCE_GROUP_ADMIN is for creating, altering and dropping operations along with resource group assignments. The RESOURCE_GROUP_USER is for the assignment of threads to resource groups. Creating a resource group The following is a sample create statement for resource group:

CREATE RESOURCE GROUP rg1
 TYPE = USER
 VCPU = 5-7
 THREAD_PRIORITY = 19;

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. The details about the RESOURCE GROUP are exposed via information_schema.resource_group table.

mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\G
 *************************** 1. row ***************************
 RESOURCE_GROUP_NAME: USR_default
 RESOURCE_GROUP_TYPE: USER
 RESOURCE_GROUP_ENABLED: 1
 VCPU_IDS: 0-0
 THREAD_PRIORITY: 0
 *************************** 2. row ***************************
 RESOURCE_GROUP_NAME: SYS_default
 RESOURCE_GROUP_TYPE: SYSTEM
 RESOURCE_GROUP_ENABLED: 1
 VCPU_IDS: 0-0
 THREAD_PRIORITY: 0
 *************************** 3. row ***************************
 RESOURCE_GROUP_NAME: rg1
 RESOURCE_GROUP_TYPE: USER
 RESOURCE_GROUP_ENABLED: 1
 VCPU_IDS: 5-7
 THREAD_PRIORITY: 19

Enabling and assigning resource groups To set the resource group for your current session, we can execute the following command:

SET RESOURCE GROUP rg1;

Or we can assign it to a specific thread as follows:

SET RESOURCE GROUP rg1 FOR <thread_id>;

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:

INSERT /*+ RESOURCE_GROUP(rg1) */ INTO sample_table VALUES(2);

Dynamically assigning resource groups to threads

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. In such a scenario, the attempt was made to set the resource groups dynamically based on the logic provided in a stored procedure. Consider the following user_rg table:

_user	_resource_group
reports	rg1
qa_users	rg2

The idea is that a process will call a stored procedure to set the resource group for that specific session before executing queries.

SELECT _resource_group into rg_name from user_rg where _user=current_user();
 SET @cmd = CONCAT("SET RESOURCE GROUP ", @rg_name );
 PREPARE stmt FROM @cmd;
 EXECUTE stmt;

This should work, but it doesn’t ! Let’s see how the manual execution goes:

mysql> select @rg_name;
 +-----------+
 | @rg_name |
 +-----------+
 | rg1 |
 +-----------+
 1 row in set (0.00 sec)
 
 mysql> SET @cmd = CONCAT("SET RESOURCE GROUP ", @rg_name );
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> PREPARE stmt FROM @cmd;
 ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet
 mysql> select @cmd;
 +------------------------+
 | @cmd |
 +------------------------+
 | SET RESOURCE GROUP rg1 |
 +------------------------+
 1 row in set (0.00 sec)

We receive an optimistic error conveying “This command is not supported yet!” That derails our plan to dynamically allocate the resource groups. But wait, the solution is simple. We just have to use IF...ELSE:

If (@rg_name = 'rg1') then
 set resource group rg1;
 else
 set resource group rg2;
 end if;

See a sample procedure here that explains the idea:

mysql> delimiter //
 mysql> drop procedure if exists testproc//
 Query OK, 0 rows affected (0.08 sec)
 
 mysql> CREATE PROCEDURE testproc (in_user varchar(20))
 -> BEGIN
 -> set @val='';
 -> select _resource_group into @val from user_rg where _user=in_type;
 -> if (@val='rg1') then
 -> select "setting resource group RG1";
 -> elseif (@val='rg2') then
 -> select "setting resource group RG2";
 -> else select "Ignoring resource group changes.";
 -> end if;
 -> END//
 delimiter ;
 mysql> delimiter ;
 mysql> call testproc('reports');
 +----------------------------+
 | setting resource group RG1 |
 +----------------------------+
 | setting resource group RG1 |
 +----------------------------+
 1 row in set (0.00 sec)
 
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> call testproc('qa_users');
 +----------------------------+
 | setting resource group RG2 |
 +----------------------------+
 | setting resource group RG2 |
 +----------------------------+
 1 row in set (0.00 sec)
 
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> call testproc('different_user')
 +-----------------------------------+
 | Ignoring resource group changes. |
 +-----------------------------------+
 | Ignoring resource group changes. |
 +-----------------------------------+
 1 row in set (0.00 sec)
 
 Query OK, 0 rows affected, 1 warning (0.00 sec)

In above example, in_user could be current_user() and thus we can actually execute the SET RESOURCE GROUP command. 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: https://bugs.mysql.com/bug.php?id=91876 As such, resource 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. That’s it for now, folks, go explore the resource groups and the brand new MySQL 8!

SQL Server Database Consulting Services

Ready to future-proof your SQL Server investment?

View full post