MySQL select * from t 1 order by rand()；

Multi-thread running, CPU is full, other requests can only be blocked. What about this situation? ?

There are probably the following solutions:

Set max_execution_time to prevent reading SQL for too long. The possible problem is that all long SQL KILL will be killed. Some long-term execution will also be killed by mistake.

Write a script to detect such a statement, such as order by rand (), and kill it with Kill query thread_id after a certain period of time.

Can you make it run normally without killing it, but without affecting other requests?

Then the resource group (abbreviated as micro RG) introduced by mysql 8.0 can basically solve this kind of problem.

For example, I can use RG to limit it to a specific CPU core at SQL level, so I will leave him alone and let him continue running. If there is a new saying, let him line up.

Why do you say foundation? At present, only CPU resources can be bound, and others are not for the time being.

Let me tell you how to use RG.

Create a resource group user_ytt. The meaning of each parameter is explained here.

Type = user indicates that this is a user-mode thread, that is, a foreground request thread. If type=system, it means background thread, which is used to restrict mysql's own threads, such as InnoDB Purge thread, InnoDB Read thread and so on.

Vcpu indicates the number of logical cores of cpu, where 0- 1 indicates that the first two cores are bound to this RG. You can use lscpu, top and so on. List your own CPU related information.

Thread_priority sets the priority. User-level priority setting is greater than 0.

Mysqlmysql & gt create resource group user_ytt type = user? Vcpu = 0-1thread _ priority =19 enabled; The query is normal, and 0 lines are affected (0.03 seconds).

RG related information can be retrieved from information _ schema. Resource group system table.

mysqlmysql & gtselect * from information _ schema . resource _ groups； +-+-+-+| Resource Group Name | Resource Group Type | Resource Group Enable | VCPU ID | Thread Priority |+-+-+| USR Default | User |? 1 | 0-3 ? | ? 0 || SYS_default | SYSTEM？ | ? 1 | 0-3 ? | ? 0 || user_ytt | USER |？ 1 | 0- 1 ? | ? 19 |+-+-+3 lines in the set (0.00 seconds)

Let's give the statement select guid from t1group by left (guid, 8) order by rand () RG user_ytt.

Mysql & gt displays the process list; + - + - + - + - + - + - + - +| Id？ | User | Host? | db？ | Command | Time? | State? | Information? |+ - + - + - + - + - + - + - + - +| ? 4 | event _ scheduler | localhost | null | daemon? | 10 179 | Waiting for an empty queue | NULL? || 240 | root | localhost | ytt？ | Query? | ? 10 1 | Create a sort index | Select guid group by left(guid, 8) order by rand () ||| 245 | root | localhost | YTT? | Query? | 0 | Start? | Show process list? |+-+-+-+-+-+-3 lines in the set (0.00 seconds)

Thread_id corresponding to connection 240 is found.

Mysqlmysql & gt select thread _ id from performance _ schema.threads where processlist _ id = 240+-+| thread _ id |+-+|? 0 row (0.00 second) in 278 |+65438+set

Give this thread 278 RG user_ytt. If you don't report your mistakes, you will succeed.

Mysqlmysql & gt sets the resource group user_ytt to 278; The query is normal, and 0 lines are affected (0.00 seconds).

Of course, this is done at the operation and maintenance level, and we can also combine MYSQL tips at the development level to give RG this statement separately. For example:

Mysqlmysql & gt select/*+resource _ group (user _ YTT) */guid from t1group by left (guid, 8) order by rand () ... 8388602 lines in the collection (4 minutes and 46.09 seconds).

Restriction of RG:

CAPSYSNICE function needs to be turned on on Linux platform. For example, I use systemd to add mysql services to my machine.

system CTL edit MySQL @ 80[Service]ambient capabilities = CAP _ SYS _ NICE

RG failed after mysql thread pool was opened.

FreeBSD, thread_priority of Solaris platform is invalid.

At present, only CPU can be bound, and other resources cannot be bound.