案例篇 | MySQL 一个会话占用几十 GB？！

1. 背景

在客户现场遇到某个会话占用几十 GB 内存的情况，且内存还在不断增大。后面发现是变量被循环赋予更大的值，导致会话内存不断增大导致的。

因此设计实验，确认如下两个问题：

• 变量被不断赋予不同值时，是否会导致会话的内存增大？
• 变量被赋予大的值时，是否会导致会话的内存增大？

2. 准备环境

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 实验一

变量被不断赋予不同值时，是否会导致会话的内存增大？

2.1.1 创建一个不断给变量赋值的 function(在死循环里面给变量赋值)

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 ;
    endif ;
    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 调用方法

select test_db.fun_test("1") ;

2.1.3 查看调用函数的 processlist_id

MySQL [(none)]> select * from information_schema.processlist ;
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+
| 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 根据 processlit_id 找到 thread_id：

MySQL [(none)]> select PROCESSLIST_ID,thread_id, name from performance_schema.threads where PROCESSLIST_ID=3 ;
+----------------+-----------+---------------------------+
| PROCESSLIST_ID | thread_id | name                      |
+----------------+-----------+---------------------------+
|              3 |        29 | thread/sql/one_connection |
+----------------+-----------+---------------------------+
1 row in set (0.00 sec)

2.1.5 根据 thread_id 查看会话占用的内存(多观察几次)

MySQL [(none)]> select * from sys.memory_by_thread_by_current_bytes where thread_id=29 ;
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 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)

MySQL [(none)]> select * from sys.memory_by_thread_by_current_bytes where thread_id=29 ;
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 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)

多刷新几次发现 total_allocated 不断的增大，但 current_allocated 基本不变，说明变量被不断赋予不同值时，并不会导致会话占用的内存变大。

2.2 实验 2

变量被赋予大的值时，是否会导致会话的内存增大？

2.2.1 创建一个给变量赋予 size 很大的值的 function(在死循环中，每次给变量的值增加一些内容即可)

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 ;
    endif ;
    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 调用方法

select test_db.fun_test_var("1") ;

2.2.3 查看调用函数的 processlist_id

MySQL [(none)]> select * from information_schema.processlist ;
+----+----------+-----------------+---------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------+
| 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 根据 processlit_id 找到 thread_id

MySQL [(none)]> select PROCESSLIST_ID,thread_id, name from performance_schema.threads where PROCESSLIST_ID=6 ;
+----------------+-----------+---------------------------+
| PROCESSLIST_ID | thread_id | name                      |
+----------------+-----------+---------------------------+
|              6 |        32 | thread/sql/one_connection |
+----------------+-----------+---------------------------+
1 row in set (0.00 sec)

2.2.5 根据 thread_id 查看会话占用的内存(多观察几次)

MySQL [(none)]> select * from sys.memory_by_thread_by_current_bytes where thread_id=32 ;
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 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)

MySQL [(none)]> select * from sys.memory_by_thread_by_current_bytes where thread_id=32 ;
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 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)

MySQL [(none)]> select * from sys.memory_by_thread_by_current_bytes where thread_id=32 ;
+-----------+--------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| 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)

多刷新几次发现 total_allocated 不断的增大， current_allocated 也在不断增大，说明变量被赋予大的值时，会导致会话占用的内存变大。

3. 总结

• 当会话中的变量被不断赋予值时，并不会导致会话使用的内存不断增大。
• 当会话中的变量被赋予大值时，可能会导致会话使用的内存明显增大。