Do MySQL Session Variables Inflate Memory? Experiments Reveal the Truth

1. Background

During a customer site visit we observed a session consuming dozens of GB of memory, which kept growing. The cause was a variable being repeatedly assigned larger values inside a loop, causing the session memory to increase.

We designed experiments to answer two questions:

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

Does assigning a large value to a variable increase session memory?

2. Test 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 – Variable assigned different values

We created a function that repeatedly assigns a variable inside an infinite 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;
//
delimiter ;

Calling the function: select test_db.fun_test("1"); We retrieved the processlist ID, thread ID, and memory usage:

select * from information_schema.processlist;
select PROCESSLIST_ID, thread_id, name from performance_schema.threads where PROCESSLIST_ID=3;
select * from sys.memory_by_thread_by_current_bytes where thread_id=29;

Repeated observations showed that total_allocated kept increasing while current_allocated remained roughly constant, indicating that assigning different values does not increase session memory.

2.2 Experiment 2 – Variable assigned large values

We created a function that concatenates a large string in each loop 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;
//
delimiter ;

Calling the function: select test_db.fun_test_var("1"); We obtained the processlist ID, thread ID, and memory usage similarly. In this case both total_allocated and current_allocated grew noticeably, showing that assigning large values can increase session memory.

3. Conclusion

Repeatedly assigning values to a session variable does not cause the session’s memory usage to grow.

Assigning a large value to a session variable may cause a significant increase in memory usage.