Originator: Yang Taotao

 

Before MySQL 8.0, let's assume there was a bad SQL.

mysql
select * from t1 order by rand() ;

Running with multiple threads, causing the CPU to run full and other requests to be blocked.What about this?(

 

There are probably several solutions:

1. Set max_execution_time to prevent reading SQL for too long.The possible problem is that all long SQL will be given away to KILL.Some that have to be executed for a long time can also be killed by mistake.

2. Write your own script to detect such statements, such as order by rand(), and kill them with Kill query thread_id for more than a certain time.

Can you let him run without killing him, but without affecting other requests?

The resource group introduced by mysql 8.0 (short for micro RG) basically solves this problem.

For example, I can use RG to restrict him at the SQL level to a specific CPU core, so I leave him alone and let him continue running, if there are new statements of this type, let him queue up.

Why basic?Currently only CPU resources can be bound, others are temporarily unavailable.

Let me show you how to use RG.

 

Create a resource group, user_ytt. Explain what each parameter means here.

1. type = user indicates that this is a user thread, that is, the requesting thread in the foreground.If type=system, represents a background thread, which is used to limit mysql's own threads, such as Innodb purge thread, InnoDB read threads, and so on.

2. vcpu represents the number of logical cores of the cpu, where 0-1 represents the first two cores that are bound to this RG.You can list information about your CPU using lscpu, top, etc.

3. thread_priority sets priority.The user level priority setting is greater than 0.

mysql
mysql> create resource group user_ytt type = user vcpu = 0-1 thread_priority=19 enable;
Query OK, 0 rows affected (0.03 sec)

RG-related information can be retrieved from the information_schema.resource_groups system table.

mysql
mysql> select * from information_schema.resource_groups;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default | USER | 1 | 0-3 | 0 |
| SYS_default | SYSTEM | 1 | 0-3 | 0 |
| user_ytt | USER | 1 | 0-1 | 19 |
+---------------------+---------------------+------------------------+----------+-----------------+
3 rows in set (0.00 sec)

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

mysql> show processlist;
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 10179 | Waiting on empty queue | NULL |
| 240 | root | localhost | ytt | Query | 101 | Creating sort index | select guid from t1 group by left(guid,8) order by rand() |
| 245 | root | localhost | ytt | Query | 0 | starting | show processlist |
+-----+-----------------+-----------+------+---------+-------+------------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)

Find the thread_id corresponding to connection 240.

mysql
mysql> select thread_id from performance_schema.threads where processlist_id = 240;
+-----------+
| thread_id |
+-----------+
| 278 |
+-----------+
1 row in set (0.00 sec)

Give this thread 278 RG user_ytt.Success comes without error.

mysql
mysql> set resource group user_ytt for 278;
Query OK, 0 rows affected (0.00 sec)

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

mysql
mysql> select /*+ resource_group(user_ytt) */guid from t1 group by left(guid,8) order by rand().
...
8388602 rows in set (4 min 46.09 sec)

RG limitations:

1. CAPSYSNICE feature needs to be turned on on Linux platform.For example, my machine uses systemd to add mysql services

   systemctl edit mysql@80 [Service] AmbientCapabilities=CAP_SYS_NICE

2. RG fails when mysql thread pool is opened.

3. freebsd,solaris platform thread_priority failed.

4. Currently, only CPU s can be bound, not other resources.