MySQL Session Memory Leak Experiments Revealed

​1. Background​
On a client site, we encountered a session occupying dozens of GBs of memory, with consumption continuously increasing. Investigation revealed that a variable was being assigned increasingly larger values in a loop, causing session memory to grow. To address this, we designed experiments to confirm two key questions:

• Does repeatedly assigning different values to a variable increase session memory?
• Does assigning large values to a variable increase session memory?

​2. Preparation Environment

DROP DATABASE IF EXISTS test_db;  
CREATE DATABASE test_db;  
USE test_db;  
CREATE TABLE a (i INT, j VARCHAR(2000));  
INSERT INTO a VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');  

‍

2.1 Experiment 1: Does repeatedly assigning different values increase session memory?​​​

‍2.1.1 Create a function that continuously assigns values in a loop

DROP FUNCTION IF EXISTS test_db.fun_test;  
DELIMITER //  
CREATE FUNCTION test_db.fun_test(_id VARCHAR(32))  
RETURNS VARCHAR(4000) DETERMINISTIC  
BEGIN  
    DECLARE _tb_i VARCHAR(32) DEFAULT _id;  
    IF _tb_i IS NULL THEN  
        RETURN NULL;  
    END IF;  
    WHILE _tb_i IS NOT NULL DO  
        SELECT i INTO @var_i FROM test_db.a WHERE i = _tb_i;  
    END WHILE;  
    RETURN 1;  
END //  
DELIMITER ;  

‍

2.1.2 Call the function

SELECT test_db.fun_test("1");  

‍

2.1.3 Check the processlist ID

SELECT * FROM information_schema.processlist;  

‍

Output:

+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
| ID | USER     | HOST            | DB      | COMMAND | TIME | STATE        | INFO                                                                                                          |  
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
|  3 | mgr_user | 127.0.0.1:48704 | test_db | Query   |   24 | Sending data | SELECT i INTO @var_i FROM test_db.a WHERE i = NAME_CONST('_tb_i', _utf8mb4'1' COLLATE 'utf8mb4_general_ci') |  
|  2 | mgr_user | 127.0.0.1:47104 | NULL    | Query   |    0 | executing    | SELECT * FROM information_schema.processlist                                                                  |  
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
2 rows in set (0.00 sec)  

‍

2.1.4 Find the thread ID using the processlist ID

SELECT PROCESSLIST_ID, thread_id, name FROM performance_schema.threads WHERE PROCESSLIST_ID=3;  

‍

Output:

+----------------+-----------+---------------------------+  
| PROCESSLIST_ID | thread_id | name                      |  
+----------------+-----------+---------------------------+  
|              3 |        29 | thread/sql/one_connection |  
+----------------+-----------+---------------------------+  
1 row in set (0.00 sec)  

‍

2.1.5 Monitor session memory usage by thread ID (observe multiple times)​

SELECT * FROM sys.memory_by_thread_by_current_bytes WHERE thread_id=29;  

‍

Output examples:

+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
| thread_id | user               | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
|        29 | mgr_user@127.0.0.1 |                 82 | 194.02 KiB        | 2.37 KiB          | 71.80 KiB         | 60.37 GiB       |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
1 row in set (0.03 sec)  

‍

+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
| thread_id | user               | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
|        29 | mgr_user@127.0.0.1 |                 82 | 194.02 KiB        | 2.37 KiB          | 71.80 KiB         | 61.97 GiB       |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
1 row in set (0.03 sec)  

‍

After multiple refreshes, total_allocated increases steadily, but current_allocated remains stable. This indicates that repeatedly assigning different values does not increase session memory.

​2.2 Experiment 2: Does assigning large values increase session memory?​​
​2.2.1 Create a function assigning large values in a loop (appending content each iteration)​

DELIMITER //  
CREATE FUNCTION test_db.fun_test_var(_id VARCHAR(32))  
RETURNS VARCHAR(4000) DETERMINISTIC  
BEGIN  
    DECLARE _tb_i VARCHAR(32) DEFAULT _id;  
    DECLARE _abc VARCHAR(4000) DEFAULT NULL;  
    IF _tb_i IS NULL THEN  
        RETURN NULL;  
    END IF;  
    WHILE _tb_i IS NOT NULL DO  
        SELECT i INTO @var_i FROM test_db.a WHERE i = _tb_i;  
        IF @var_i IS NOT NULL THEN  
            SET _abc = CONCAT(@var_i, '>', _abc);  
        END IF;  
    END WHILE;  
    RETURN 1;  
END //  
DELIMITER ;  

‍

2.2.2 Call the function

SELECT test_db.fun_test_var("1");  

‍

2.2.3 Check the processlist ID

SELECT * FROM information_schema.processlist;  

‍

Output:

+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
| ID | USER     | HOST            | DB      | COMMAND | TIME | STATE        | INFO                                                                                                          |  
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
|  6 | mgr_user | 127.0.0.1:59548 | test_db | Query   |   49 | Sending data | SELECT i INTO @var_i FROM test_db.a WHERE i = NAME_CONST('_tb_i', _utf8mb4'1' COLLATE 'utf8mb4_general_ci') |  
|  2 | mgr_user | 127.0.0.1:47104 | NULL    | Query   |    0 | executing    | SELECT * FROM information_schema.processlist                                                                  |  
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+  
2 rows in set (0.00 sec)  

‍

2.2.4 Find the thread ID using the processlist ID

SELECT PROCESSLIST_ID, thread_id, name FROM performance_schema.threads WHERE PROCESSLIST_ID=6;  

‍

Output:

+----------------+-----------+---------------------------+  
| PROCESSLIST_ID | thread_id | name                      |  
+----------------+-----------+---------------------------+  
|              6 |        32 | thread/sql/one_connection |  
+----------------+-----------+---------------------------+  
1 row in set (0.00 sec)  

‍

2.2.5 Monitor session memory usage by thread ID (observe multiple times)​

SELECT * FROM sys.memory_by_thread_by_current_bytes WHERE thread_id=32;  

‍

Output examples:

+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
| thread_id | user               | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
|        32 | mgr_user@127.0.0.1 |                825 | 693.13 MiB        | 860.32 KiB        | 564.82 MiB        | 248.01 GiB      |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
1 row in set (0.03 sec)  

‍

+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
| thread_id | user               | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
|        32 | mgr_user@127.0.0.1 |                834 | 705.05 MiB        | 865.67 KiB        | 576.75 MiB        | 249.93 GiB      |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
1 row in set (0.03 sec)  

‍

+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
| thread_id | user               | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
|        32 | mgr_user@127.0.0.1 |                848 | 727.74 MiB        | 878.78 KiB        | 599.44 MiB        | 253.84 GiB      |  
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+  
1 row in set (0.03 sec)  

‍

Multiple refreshes show both total_allocated and current_allocated increasing, indicating that assigning large values significantly increases session memory.

​3. Summary​

• Repeatedly assigning different values to a session variable does not cause continuous memory growth.
• Assigning large values to a session variable can lead to substantial memory increases.
  ​Keywords:​​ #MySQL #FunctionCall #MemoryManagement