Execute a query on a particular MySQL thread - IDEA

user_5431418 0
Viewed 69

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. Is something like this possible in MYSQL, or any other databases ?

innodb mysql
2 Answers

A case against thread priorities

I suspect that if "thread priorities" were useful, they would already have been implemented.

Here is one problem that argues against such:

  1. A low-priority thread gets some time.
  2. That low thread has locked a row because he is about to Update it. (Say, a "Likes" counter.)
  3. A high-priority thread comes along and needs that row, but is now blocked. (Oops, he is now as slow as the low thread.)
  4. Another high-priority thread comes along... Well, I'll let you finish the murder mystery story.

The best thing to do (in most cases) is to work on speeding up the slowest queries. Priority is less important than how fast it can get out of the way.

Keep in mind that "context switching" is necessary to handle inter-thread coordination. And probably the number of switches is the same, with or without priorities.

"100ms to 1 sec" -- Even these are worth optimizing.

A problem I sometimes see stems from allowing too many concurrent processes. I like the grocery store analogy...

Crowded grocery store analogy

Put simply, if "too many" people are in the store at the same time, no one will get out very fast; everyone is slowed down.

Now let's say some people are more privileged and the others must step aside to allow them to go faster. Well, suppose I (low-priority) may be standing in front of the deli counter and the single clerk is slicing some meat for me. You (high-priority) come along and demand that the clerk stop with my order to handle your request. This will lead to the clerk being less efficient (because of the half-finished order sitting in the meat slicer). And more customers will be standing behind you.

Freeway traffic

Studies have determined that metering lights on freeway ramps make freeways more efficient. Yeah, it is counter-intuitive. Also, frequent lane switching on the freeway actually requires more space. Etc.

Alternative proposal

So, Use the slowlog to determine which query types are the most burdensome on the system, then provide a sample, together with SHOW CREATE TABLE and EXPLAIN SELECT. We can probably speed it up.

user_17668310

MySQL Resource Groups

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.

MySQL :: MySQL 8.0 Reference Manual :: 5.1.16 Resource Groups

Few other reference links:

https://medium.com/synaptic-tech/scaling-our-platform-using-mysql-8-resource-groups-e7c09d2cedd7

https://dzone.com/articles/mysql-8-load-fine-tuning-with-resource-groups

user_54314180
Related