[#MDEV-26723] Cached stored procedures are counted as global memory, should be session memory

When stored procedure is cached, the bytes are counted towards global memory, which can be
retrieved with

select variable_value  from information_schema.global_status where variable_name='MEMORY_USED';

This is wrong, as the caches are per-session (THD::sp_proc_cache etc). Thus populating cache should result in increase of "information_schema.session_status where variable_name='MEMORY_USED'" instead, which is not happening

Commands below create and execute a dummy stored procedure, and measure increase of global and session memory for that operation.

session memory increases by 96 bytes , global memory by some 10K . Session memory increase only happens first time around, i.e if you create another procedure, and execute it, session memory will increase by 0 bytes, but global memory by some 12K. (Those numbers are taken from current debug 10.3 on my box.

mysql>  select variable_value into @global_mem_used from information_schema.global_status where variable_name='MEMORY_USED';

Query OK, 1 row affected (0.00 sec)

mysql>  select variable_value into @local_mem_used from information_schema.session_status where variable_name='MEMORY_USED';

Query OK, 1 row affected (0.00 sec)

mysql>  create procedure sp1() BEGIN END;

Query OK, 0 rows affected (0.01 sec)

mysql> call sp1();

Query OK, 0 rows affected (0.00 sec)

mysql>  select variable_value-@global_mem_used as global_mem_diff from information_schema.global_status where variable_name='MEMORY_USED';

+-----------------+

| global_mem_diff |

+-----------------+

|           10888 |

+-----------------+

1 row in set (0.00 sec)

mysql>  select variable_value-@local_mem_used as local_mem_diff from information_schema.session_status where variable_name='MEMORY_USED';

+----------------+

| local_mem_diff |

+----------------+

|             96 |

+----------------+

1 row in set (0.00 sec)

[MDEV-26723] Cached stored procedures are counted as global memory, should be session memory Created: 2021-09-29 Updated: 2023-04-27
Status:	Open
Project:	MariaDB Server
Component/s:	Information Schema, Stored routines
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s:	10.4, 10.5, 10.6

[MDEV-26723] Cached stored procedures are counted as global memory, should be session memory Created: 2021-09-29 Updated: 2023-04-27