Interpretation of new features | MySQL 8.0.28 user connection memory limit

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

In the past, when MySQL wanted to limit the memory of a single connection, it could only set various SESSION variables carefully to prevent the memory overflow of a single connection caused by executing some SQL! Can we directly include such a function on the MySQL server to simplify the operation and maintenance of the database?

The latest version of MySQL 8.0.28 was released a few days ago. One of the new features is to limit the memory of a single connection on the database side, which is a little exciting.

Several new parameters related to this feature in MySQL 8.0.28 are as follows:

1. connection_memory_limit : Core parameters! It is used to limit the upper limit of memory for single-user connection. The default is the maximum value of BIGINT UNSIGNED: 18446744073709551615 bytes, and the minimum is 2MB.
2. global_connection_memory_tracking : Set whether to enable the tracking of the connection memory function, and store the connection memory data in the state variable Global_connection_memory . For performance reasons, it is disabled by default.
3. connection_memory_chunk_size : When the parameter global_connection_memory_tracking is enabled, set the update frequency of the status variable Global_connection_memory.

Next we experience this new feature.

The administrator side sets the upper limit of the memory limit parameter: in order to see the effect as soon as possible, set it to the minimum value.

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

Create a new user tt1 and give read-only permissions based on the library 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)

Create a table and insert a row of records: Using the longtext data type here can make the query result faster and out of memory.

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)

User tt1 login authentication: perform simple GROUP BY search on field r1, reports that the connection memory exceeds the set limit error, and the connection is closed.

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.

However, this new feature does not take effect for administrators and built-in users. Log in to MySQL again with the ROOT user and execute the SQL just now:

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)

As you can see, the administrator can execute this SQL normally. Therefore, when our DBA grants permissions to development users, in order to avoid unnecessary operation and maintenance work, it is forbidden to grant SUPER permissions.