Execute a query on a particular MySQL thread - IDEA

MySQL & MariaDB Other MySQL® Questions
1

I have an idea, but I’m not sure whether it is possible or not. / It is already available or not. Just sharing my points here, to get the suggestion whether it is possible in MySQL

Issues:
Say a small change is done in a query in a feature of a product and got released in production, due to this change it takes more time to complete the execution of the query.
Suddenly there is a surge from customers for this feature. Then the DB loaded heavily due to multiple queries of this feature, because of this feature query other features in the product also got affected (other simple queries) as the DB is loaded.

Why this occurred:
Say my innodb_thread_concurrency is 20. For ex: If 100 such costly query comes in, MySQL will use all THREADS available and process the query by doing context switching. Due to this other simple queries gets affected.

What can be one of the possible solution:
May be move the costly queries to slave, but still the same will occur in slave and slave get loaded.

What I think could be a better solution:
Say my
innodb_thread_concurrency = 20.
CPU CORE = 36

Here I have allocated 20 Threads from CPU to MYSQL. In this 20 Thread, I will make a split up in Application level, which query will be executed in which thread.

Split 20 thread into 3 Set based on priority:

Thread Priority vs Thread Count - which should be configurable.
Ex:
Low Priority: 4
Medium Priority: 6
High Priority: 10

High Priority:

  1. Say very simple queries. Like querying a config tables
  2. Insert, Update, Delete queries
  3. Basically these queries are the basic one for the products to run
  4. Mostly gets executed under 100ms to 1 sec

Medium Priority:

  1. Queries which are less important than core queries, which might take little extra time mostly 1 - 5 seconds

Low Priority:

  1. Log Table queries, Analytics queries
  2. Time taking queries greater than 5 sec

The rule here is:
→ The configured query should be executed only in that particular Priority_Threads and context switching also should be done only among those Priority_Threads.

→ All the queries should be configure in product level which query executes in which THREAD, if this query is simple and important, it will be executed using High_Priority_Thread, as our assumption is this query will be executed in 100ms.

Let come to our case which loaded the DB and affected all the queries, with this support we would have executed our query in Low_Priority_Thread. As said above, If 100 this type of queries come to Low_Priority_Thread, only that Priority Thread get affected as context switching will occur only between Low_Priority_Thread. So other Priority_Threads will be free which will serve their own queries which is assigned.

This can be used in different use cases:

  1. If a new feature comes in Beta version, we can execute this queries in Low_Priority_Thread. If any issue in this feature query, it won’t affect the core queries in other threads.
  2. Any changes done to the existing query in High_Priority query, we can change its Priority_Thread type to lower level for the time being until it is stable.

Please share your views on this.

2

Hello @Vinieth,
From what I read in your post, sounds like you want this MySQL :: MySQL 8.0 Reference Manual :: 5.1.16 Resource Groups

You can create several groups and use /* */ SQL comments to hint the optimizer which resource group the query should be executed within.

1 Like