Using MySQL 8.0.28 Connection Memory Limit to Prevent Per‑Connection Memory Overruns

Historically, limiting the memory consumption of a single MySQL connection required careful tuning of various SESSION variables to avoid out‑of‑memory errors caused by specific queries. MySQL 8.0.28 adds a built‑in feature that simplifies this task by allowing administrators to set a per‑connection memory cap directly on the server.

The new version introduces three key parameters:

connection_memory_limit : the core setting that defines the maximum memory a single connection may use, defaulting to the maximum BIGINT UNSIGNED value (18446744073709551615 bytes) with a minimum of 2 MiB.

global_connection_memory_tracking : enables tracking of connection memory usage and stores the data in the Global_connection_memory status variable; it is disabled by default for performance reasons.

connection_memory_chunk_size : controls the update frequency of the Global_connection_memory variable when tracking is enabled.

To see the feature in action, the article walks through a practical test:

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

A new user tt1 is created with read‑only access to a test database:

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)

A table containing a LONGTEXT column is created and populated with a large string to provoke high memory usage:

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)

When the regular user tt1 runs a simple GROUP BY query, MySQL aborts the statement because the connection exceeds the 2 MiB limit:

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.

The article notes that the memory‑limit enforcement does not apply to the root or internal users. Executing the same query as root succeeds without error:

root@debian-ytt1:~# mysql -S /tmp/mysqld_3306.sock
...
localhost:(none)>use ytt
Database changed
localhost:ytt>select count(r1) from t group by r1;
+-----------+
| count(r1) |
+-----------+
|         1 |
+-----------+
1 row in set (0.03 sec)

This behavior demonstrates that administrators can safely enforce memory limits for application users while retaining full access for privileged accounts, reducing the risk of accidental out‑of‑memory crashes in production environments.