MySQL 8.0 RESOURCE_GROUP Overview

MySQL 8.0 RESOURCE_GROUP Overview

PREVIOUS POST
NEXT POST

MySQL 8.0 RESOURCE_GROUPIn this blog post, we’ll provide an overview of the new MySQL 8.0 RESOURCE_GROUP feature.

One great new feature introduced in MySQL 8.0 that – from my point of view – requires attention is RESOURCE_GROUP.

Short disclaimer: I want to point out that MySQL 8.0 is not GA yet, so it is possible for the MySQL 8.0 RESOURCE_GROUP implementation to change in features and/or behavior.

I’ve used MySQL Community Server 8.0 RC, and everything mentioned below applies to this MySQL version.

In this post, I will quickly look at this feature and summarize what it’s for, how it makes the DBA’s life a little bit easier and highlight some known limitations.

The MySQL documentation describes it as follows:

“MySQL supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group. Group attributes enable control over its resources, to enable or restrict resource consumption by threads in the group. DBAs can modify these attributes as appropriate for different workloads.

“Currently, CPU time is a manageable resource, represented by the concept of “virtual CPU” as a term that includes CPU cores, hyperthreads, hardware threads, and so forth. The server determines at startup how many virtual CPUs are available, and database administrators with appropriate privileges can associate these CPUs with resource groups and assign threads to groups.”

Great, so now we can assign vCPU’s resources to the different groups and with different priorities. Before continuing to discover what resource groups are and how they’re cool, let’s take a quick look at limitations:

  • Resource groups are unavailable if you install the thread pool plugin
  • Resource groups are unavailable on macOS, which provides no API for binding CPUs to a thread
  • On FreeBSD and Solaris, resource group thread priorities are ignored (effectively, all threads run at priority 0)
  • On Linux, resource groups thread priorities are ignored unless the CAP_SYS_NICE capability is set. MySQL package installers for Linux systems should set this capability. For installation using a compressed tar file binary distribution or from source, the CAP_SYS_NICE capability can be set manually using the setcap command, specifying the pathname to the mysqld executable (this requires sudo access).
  • On Windows, threads run at one of five thread priority levels. The resource group thread priority range of -20 to 19 maps onto those levels.
  • Resource group management is local to the server on which it occurs. Resource group SQL statements and modifications to the resource_group data dictionary table are not written to the binary log and are not replicated.

Since we now know the limitations, let’s check on how we can use it for the real world scenario.

From my experience, many DBA’s have the problem of slow reporting and/or aggregation queries on the same instance when they have intensive OLTP workload and are executing some batch jobs. The easiest and usual way to solve this issue is to offload these activities to a slave instance. While this works, it takes some time to provision new hardware resources, set it up, etc. So in most cases, this approach could be considered mid-term.

What if you need to fix the resources contention issue on the existing server now, and offload such activities later? Or you cannot move a batch job to the slave since it’s required for consistency, and you cannot afford slave lag? Or in some cases, the batch job may need to perform a write so it simply can’t go to the slave?

In this case, resource groups are a good fix.

We have two default groups currently that we cannot delete or change:

Each group has a type, which is either SYSTEM or USER. The resource group type affects the range of priority values assignable to the group. This attribute, together with the differences in permitted priorities, enables system threads to be identified so as to protect them from contention for CPU resources against user threads.

The list below illustrates the priority ranges available and related to the each group type:

    • For system resource groups, the permitted priority range is -20 to 0.
    • For user resource groups, the permitted priority range is 0 to 19.The thread priority is the execution priority for threads assigned to the resource group. Priority values range from -20 (highest priority) to 19 (lowest priority). The default priority is 0, for both system and user groups.System groups are permitted a higher priority than user groups, ensuring that user threads never have a higher priority than system threads:

Assuming we have eight CPU’s (which is my case).

Now let’s create a resource group for our reporting queries, and another group for the batch jobs:

Let’s verify if the groups were properly created:

So now let’s consider couple scenarios :

  • We already have our huge reporting query running on the server, and it consumes a lot of resources and slows everything down. In this case, we can do the following:
    • Find out the thread_id of this reporting query from Performance Schema threads table and assign a specific resource group to that thread_id:

  • To execute a single statement using the reporting group, use the RESOURCE_GROUP optimizer hint:

Threads assigned to the reporting group execute with its resources, which you can modify as desired.

For example, at the same time we have executed a batch job script with Batch_job group resources. As result, we need to decrease priority and allocate fewer CPU resources to the our reporting resource group. We can do this in the following way:

Conclusion

MySQL 8.0 RESOURCE_GROUP is a great and easy-to-use feature that can help a lot in certain cases. I have a strong feeling that the list of the manageable resources will not be limited to the CPU’s only, but will extend to other aspects of the server also. Once it’s production ready, I think many database administrators will find it very helpful and useful.

As a next step, I will run a couple of tests on resource utilization for different resources groups usage scenarios and performance results.

PREVIOUS POST
NEXT POST

Share this post

Leave a Reply