前置条件
在Linux系统上, mysqld可执行命令需要有CAP_SYS_NICE能力，例如，
代码语言：txt复制$ setcap cap_sys_nice ep /usr/sbin/mysqld
$ getcap /usr/sbin/mysqld
/usr/sbin/mysqld = cap_sys_nice ep
使用资源组的限制
- 目前仅支持对CPU的设定，不包含IO，内存等，因此实际使用场景有限！
- 资源组类型只支持USER和SYSTEM两种类型，而常见的线程类型是FOREGROUN和BACKGROUND，直接通过SET RESOURCE GROUP来指定线程的资源组往往会报3661错
- 对操作系统平台有强依赖。
查看资源组
代码语言：txt复制[test]> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS;
--------------------- --------------------- ------------------------ ---------- -----------------
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
--------------------- --------------------- ------------------------ ---------- -----------------
| USR_default | USER | 1 | 0-15 | 0 |
| SYS_default | SYSTEM | 1 | 0-15 | 0 |
| SYS_internal | SYSTEM | 1 | 0-15 | 0 |
--------------------- --------------------- ------------------------ ---------- -----------------
3 rows in set (0.00 sec)我这里DMEO机器是16个逻辑CPU的，因此上面的VCPU_IDS默认都是0-15，表示全部都可以被使用。
线程优先级是线程的执行优先级 分配给资源组。优先级值范围从 -20（最高优先级）到 19（最低优先级）。
默认 对于系统组和用户组，优先级均为 0。
允许系统组具有比用户更高的优先级 组，确保用户线程永远不会有更高的 优先级高于系统线程：
对于系统资源组，允许的优先级范围 是 -20 到 0。
对于用户资源组，允许的优先级范围 是 0 到 19。
创建资源组
资源组管理是其所在服务器的本地管理方式 发生。资源组 SQL 语句和对resource_groups数据字典表不是 写入二进制日志，并且不会被复制。
创建自定义的资源组（表示这个资源组的相关会话可以调度到 cpu2 或者cpu3）
代码语言：txt复制CREATE RESOURCE GROUP slow_sql TYPE = USER VCPU = 2-3 THREAD_PRIORITY = 10;
再次查看
代码语言：txt复制mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS WHERE RESOURCE_GROUP_NAME = 'slow_sql'G将线程分配给slow_sql组
方法1、将已运行中的线程分配给slow_sql组
代码语言：txt复制SET RESOURCE GROUP slow_sql FOR 59; -- 这里的59是从performance_schema.threads 里面查出来的方法2和3，都是只能对尚未执行的sql进行绑定，如下：
代码语言：txt复制方法2：
[test]> SET RESOURCE GROUP slow_sql;
[test]> select * from sbtest.sbtest2 order by id desc limit 2000,10;
[test]> call xxxx();
方法3：
INSERT /* RESOURCE_GROUP(sql_thread) */ INTO tbl1 VALUES(1); <== 当前语句会使用sql_thread资源组修改资源组
代码语言：txt复制ALTER RESOURCE GROUP sql_thread VCPU = 5, 6 THREAD_PRIORITY = -5;修改资源组sql_thread绑定的逻辑CPU为编号5，6，并降低优先级到-5。
删除资源组
代码语言：txt复制DROP RESOURCE GROUP sql_thread ;注意，如果这个资源组正在使用中，则无法删除，会提示资源繁忙，需要等慢的sql会话断开后（或者被kill掉），才能删除资源组。
禁用资源组
[test]> alter RESOURCE GROUP slow_sql disable;
注意，如果这个资源组正在使用中，虽然能禁用掉，但是删除还是会提示资源繁忙，需要等慢的sql会话断开后（或者被kill掉），才能删除资源组。
实验演示
创建一个存储过程，用于模拟慢sql
代码语言：txt复制use test;
DELIMITER //
CREATE PROCEDURE intensive_calculations()
BEGIN
DECLARE v INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
WHILE i < 10000000 DO
SET v = SQRT(i * i (RAND() * 10000));
SET i = i 1;
END WHILE;
END//
DELIMITER ;开3个窗口，调用存储过程
代码语言：txt复制use test;
-- 调用存储过程
CALL intensive_calculations();稍等就可以通过top观察到有3个cpu核心cpu打满了。
创建资源组,绑定到CPU1上（我这台测试机器配置较低只有2个核）
代码语言：txt复制CREATE RESOURCE GROUP slow_sql TYPE = USER VCPU=1 THREAD_PRIORITY = 10;然后找到慢sql对应的thread_id
代码语言：txt复制SELECT
THREAD_ID,
PROCESSLIST_INFO,
RESOURCE_GROUP,
PROCESSLIST_TIME
FROM
performance_schema.threads
WHERE
PROCESSLIST_INFO REGEXP 'SELECT|INSERT|UPDATE|DELETE|CALL'
AND PROCESSLIST_TIME > 2 -- 便于观察，把这个值调小了点
AND PROCESSLIST_USER!=''
AND PROCESSLIST_COMMAND !='Sleep'
AND RESOURCE_GROUP='USR_default'
;假设上面找到的thread_id为 65 67 69。
对于已经在运行状态的sql会话，需要使用下面的set 命令才能将其绑定到资源组上
代码语言：txt复制[test]> SET RESOURCE GROUP slow_sql FOR 65;
[test]> SET RESOURCE GROUP slow_sql FOR 67;
[test]> SET RESOURCE GROUP slow_sql FOR 69;再次查看
代码语言：txt复制[test]> SELECT THREAD_ID, PROCESSLIST_INFO, RESOURCE_GROUP, PROCESSLIST_TIME
FROM performance_schema.threads
WHERE PROCESSLIST_USER!='' G再次查看cpu的负载，如下：
耗时对比：
结合自动化运维
python编写守护进程
1、连接到mysql后，尝试创建资源组
2、while true死循环，每隔几秒检测一次ps.threads表，将慢查询的thread_id绑定到step1的资源组上
3、发送一个dingding消息，通知到DBA（可选）
代码如下：
config.py
代码语言：txt复制# -*- coding: utf-8 -*-
hostip = "192.168.3.14"
username = "dts"
password = "dts"
dbname = "test"
dbport = 3306
charset = "utf8"
long_time = 10main.py
代码语言：txt复制# -*- coding: utf-8 -*-
# 参考 https://github.com/hcymysql/imprison_rg/blob/main/imprison_rg.php
# 注意，建议和MySQL部署在同一个机器上，便于获取到cpu核心数，不然的话只能像我代码里这样写死cpu
import mysql.connector
import logging
import configs
import time
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
def add_resource_group(group_name, cpu_core=1):
mydb = mysql.connector.connect(
host=configs.hostip,
port=configs.dbport,
user=configs.username,
passwd=configs.password,
charset=configs.charset,
)
cursor = mydb.cursor()
add_sql = f"CREATE RESOURCE GROUP {group_name} TYPE = USER VCPU={cpu_core} THREAD_PRIORITY = 10;"
try:
cursor.execute(add_sql)
except Exception as e:
# print(e)
logger.warning(f"{e}")
def bind_slowlog_rg(group_name, long_time):
mydb = mysql.connector.connect(
host=configs.hostip,
port=configs.dbport,
user=configs.username,
passwd=configs.password,
charset=configs.charset,
)
cursor = mydb.cursor()
# 只关注RESOURCE_GROUP为USR_default且存在慢查询的thread
query_sql = f"SELECT THREAD_ID, PROCESSLIST_INFO, RESOURCE_GROUP,PROCESSLIST_TIME FROM performance_schema.threads WHERE PROCESSLIST_INFO REGEXP 'SELECT|INSERT|UPDATE|DELETE|CALL' AND RESOURCE_GROUP='USR_default' AND PROCESSLIST_COMMAND!='Sleep' AND PROCESSLIST_TIME > {long_time} AND PROCESSLIST_USER!=''"
try:
cursor.execute(query_sql)
res = cursor.fetchall()
for thread in res:
logger.info(
f"SQL明细:{thread[1]},持续时间(秒): {thread[3]},用户名: {thread[4]},地址: {thread[5]},库名: {thread[6]},状态: {thread[7]},会话ID: {thread[8]}"
)
set_sql = f"SET RESOURCE GROUP {group_name} FOR {thread[0]};"
cursor.execute(set_sql)
# 这里还可以加一个钉钉告警
except Exception as e:
logger.warning(f"{e}")
if __name__ == "__main__":
add_resource_group(group_name="slow_sql", cpu_core=1)
while True:
bind_slowlog_rg(group_name="slow_sql", long_time=5)
time.sleep(2)运行结果如下：
