MySQL 8.0 resource group effectively solves the CPU alarm caused by slow SQL

The role of the resource group is to isolate resources (you can understand it as the hardware configuration checked when activating the CVM). Assign the slow SQL thread id online to a core of the CPU and let it run slowly, so as not to affect the overall performance of the CPU.

Create a resource group

Mysql > create resource group slowsql_rg type=user vcpu=3 thread_priority=19 enable

Slowsql_rg is the resource group name

The source of type=user is the slow SQL of the client.

Which CPU core does vcpu=3 assign it to (you can use cat / proc/cpuinfo | grep processor to see how many cores CPU has)

Thread_priority is the priority, with a range of 0 to 19. 19 is the lowest priority and 0 is the highest priority.

View resource group information

Mysql > select * from information_schema.resource_groups

Find the thread ID of slow SQL

SELECT THREAD_ID,PROCESSLIST_INFO,RESOURCE_GROUP,PROCESSLIST_TIME FROM performance_schema.threads WHERE PROCESSLIST_INFO REGEXP 'SELECT | INSERT | UPDATE | DELETE | ALTER' AND PROCESSLIST_TIME > 10

Put the value taken out by THREAD_ID into the resource group as a restriction

Set resource group slowsql_rg for 379

For example, you can also change the restrictions you want to relax.

ALTER RESOURCE GROUP slowsql_rg VCPU = 3 THREAD_PRIORITY = 0

Close the resource group and lift the restriction

ALTER RESOURCE GROUP slowsql_rg DISABLE FORCE