MySQL用户连接内存限制特性

数据运维 2024-04-09 大猫 手机阅读

通常情况下，MySQL想要限制单个连接的内存，只能小心翼翼设置各种SESSION变量，防止执行某些SQL导致单个连接内存溢出！能不能直接在MySQL服务端包含这样一个功能，简化数据库的运维工作？

MySQL 8.0.28，其中有一项新功能就是在数据库侧来限制单个连接的内存，技术社群的这篇文章《新特性解读 | MySQL 8.0.28 用户连接内存限制》给我们讲解了这个新的特性。

MySQL 8.0.28与此功能有关的几个新参数如下，
  1. connection_memory_limit：核心参数！用来限制单用户连接的内存上限值，默认为BIGINT UNSIGNED的最大值：18446744073709551615字节，最小为2MB。
  2. global_connection_memory_tracking：设置是否开启对连接内存功能的追踪，并且把连接内存数据存入状态变量Global_connection_memory。为了性能考虑，默认关闭。
  3. connection_memory_chunk_size:在参数global_connection_memory_tracking开启的场景下，设置状态变量Global_connection_memory的更新频率。
接下来我们体验下这个新特性。

管理员端设置内存限制参数上限：为了尽快看到效果，设置为最小值。

localhost:(none)>set global connection_memory_limit=2097152;
Query OK, 0 rows affected (0.00 sec)

创建一个新用户tt1，并赋予基于库ytt的只读权限。

localhost:(none)>create user tt1 identified by 'tt';
Query OK, 0 rows affected (0.03 sec)

localhost:(none)>grant select on ytt.* to tt1;
Query OK, 0 rows affected (0.02 sec)

创建一张表，插入一行记录：这里使用longtext数据类型能让查询结果更快内存溢出。

localhost:ytt>create table t(id int primary key, r1 longtext);
Query OK, 0 rows affected (2.39 sec)
localhost:ytt>insert t values (1,lpad('mysql',6000000,'database'));
Query OK, 1 row affected (0.63 sec)

用户tt1登录验证：对字段r1进行简单GROUP BY检索 ，报连接内存超出设定限制错误，连接关闭。

debian-ytt1:ytt>select count(r1) from t group by r1;
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 7094928 bytes.

不过这个新功能对管理员和内置用户不生效。用ROOT用户重新登录MySQL执行刚才那条SQL，

root@debian-ytt1:~# mysql -S tmp/mysqld_3306.sock
...

localhost:(none)>use ytt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
localhost:ytt>select count(r1) from t group by r1;
+-----------+
| count(r1) |
+-----------+
| 1 |
+-----------+
1 row in set (0.03 sec)

可以看到，管理员可以正常执行这条SQL。所以我们DBA给开发用户赋予权限时，为了避免不必要的运维工作，禁止赋SUPER权限。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：《VMWare虚拟化全系脑图了解》
《推荐几个数据库领域的优质公众号》《给读者朋友们提供的赠书活动》《数据库只追求性能就够了？》《MySQL数据表的授权注意事项》
近期的热文：《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引：《公众号1400篇文章分类和索引》