如何限制用户修改long_query_time

文摘   科技   2024-10-30 08:00   福建  

* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。


需求来源

数据库的 long_query_time 设置了写入慢查询日志的SQL语句执行时长的阈值,当应用系统修改为很小的值或0时,会在数据库的慢查询日志中记录大量SQL语句,导致数据库性能降低和占用磁盘空间的快速增长。

GreatSQL 对于影响整个数据库会话级变量设置为受限会话变量(如:binlog_format,sql_log_bin和sql_log_off),同时增加了用户权限 SESSION_VARIABLES_ADMIN,只有授予了 SESSION_VARIABLES_ADMIN 的用户才能更改这些受限会话变量。

但 long_query_time 不在受限会话变量中,该如何限制应用程序修改 long_query_time 呢?

解决方法

performance_schema 中有表 variables_by_thread 存储了每个活动会话的会话级系统变量。可以编写一个event定时检查用户的long_query_time设置,如果与全局的long_query_time变量值不同,将该会话kill掉。

相关系统表:

#performance_schema下的系统表
#存储每个会话的会话级系统变量
greatsql> SHOW CREATE TABLE variables_by_thread\G
*************************** 1. row ***************************
       Table: variables_by_thread
Create TableCREATE TABLE `variables_by_thread` (
  `THREAD_ID` bigint unsigned NOT NULL,
  `VARIABLE_NAME` varchar(64NOT NULL,
  `VARIABLE_VALUE` varchar(1024DEFAULT NULL,
  PRIMARY KEY (`THREAD_ID`,`VARIABLE_NAME`)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#存储全局的系统变量
greatsql> SHOW CREATE TABLE global_variables\G
*************************** 1. row ***************************
       Table: global_variables
Create TableCREATE TABLE `global_variables` (
  `VARIABLE_NAME` varchar(64NOT NULL,
  `VARIABLE_VALUE` varchar(1024DEFAULT NULL,
  PRIMARY KEY (`VARIABLE_NAME`)
ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

event程序的实现:

DELIMITER $$
CREATE EVENT check_session_long_query_time
ON SCHEDULE EVERY 5 SECOND
DO
BEGIN
    DECLARE v_processlist_id BIGINT UNSIGNED;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT t.PROCESSLIST_ID
        FROM performance_schema.variables_by_thread v
        inner join performance_schema.threads t on v.thread_id=t.thread_id
        WHERE v.VARIABLE_NAME = 'long_query_time' 
        AND v.VARIABLE_VALUE != (select VARIABLE_VALUE from performance_schema.global_variables where 
        VARIABLE_NAME = 'long_query_time' ) ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_processlist_id ;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 终止连接
        KILL v_processlist_id ;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

总结

通过kill会话来限制用户修改会话级变量,有些暴力,但也是DBA的无奈手段。较好的方式是修改受限系统变量实现方法,将受限的会话变量做成一个可动态添加的列表,如在某个系统表中予以存储,DBA可以通过添加和删除数据行来动态修改受限会话变量。
MySQL开源版本也存在同样的问题,MySQL 社区已确认作者提的feature request《Optimize the handling of SESSION_VARIABLES_ADMIN permission(https://bugs.mysql.com/bug.php?id=115944)》。


Enjoy GreatSQL :)

<往 期 推 荐>
工具分享丨数据闪回工具 MyFlash
MariaDB 和 GreatSQL 性能差异背后的真相
展会 | 斩获殊荣!GreatSQL亮相2024 OSCAR开源产业大会  彰显开源实力
GreatSQL 在SQL中使用 HINT 语法修改会话变量
某市驾驶培训监管服务平台 GreatSQL 数据库适配之旅

《GreatSQL运维实战》视频课程

<关于 GreatSQL>

GreatSQL数据库是一款开源免费数据库,可在普通硬件上满足金融级应用场景,具有高可用、高性能、高兼容、高安全等特性,可作为MySQL或Percona Server for MySQL的理想可选替换。

💻社区官网: https://greatsql.cn/ 
Gitee  https://gitee.com/GreatSQL/GreatSQL
GitHub  https://github.com/GreatSQL/

🆙BiliBili  : https://space.bilibili.com/1363850082

(对文章有疑问或见解可去社区官网提出哦~)

加入微信交流群
加入QQ交流群

想看更多技术好文,点个"在看"吧!

GreatSQL社区
专注GreatSQL数据库及相关产品
 最新文章