diff options
| author | Vladislav Vaintroub <wlad@mariadb.com> | 2021-09-27 17:43:36 +0200 |
|---|---|---|
| committer | Vladislav Vaintroub <wlad@mariadb.com> | 2021-09-27 19:09:37 +0200 |
| commit | 1f099418b6cce00b72f601288735bd7454d037cd (patch) | |
| tree | 3a3b50780e621e67b6f5f2b695546825477de266 /mysql-test/main/sp.result | |
| parent | d7aa81c862b5d813f8fe3440b7e011a555e88d96 (diff) | |
| download | mariadb-git-1f099418b6cce00b72f601288735bd7454d037cd.tar.gz | |
MDEV-20699 mysqldump of routines causes MariaDB to get killed by oom-killerbb-10.3-wlad-MDEV-20699
The reason for this behavior is that SP get cached, per connection.
The stored_program_cache is size of this cache, which amounts to 256
routines by default. A compiled stored procedure can easily be several
megabytes in size. Thus calling SHOW CREATE PROCEDURE for all stored
procedures, like mysqldump does, can require significant amount of memory.
Fixed by bypassing the cache for "SHOW CREATE". This should normally be
fine also perfomance-wise, as cache is meant to be used for repeated
execution, not repeated SHOW CREATEs.
Added a test to verify that CREATE PROCEDURE + SHOW CREATE PROCEURE do not
cache, i.e amount of allocated memory does not change.
Note, there is a change in existing behavior in an edge case :
If "SHOW CREATE PROCEDURE p1" called from p1, after p1 was altered, now
this will now return altered code. Previour behavior - relied on caching
and would return old code. The previous behavior might was not necessarily
correct.
Diffstat (limited to 'mysql-test/main/sp.result')
| -rw-r--r-- | mysql-test/main/sp.result | 22 |
1 files changed, 22 insertions, 0 deletions
diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 6510eaf77f5..694c7bcd20e 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8893,4 +8893,26 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp BEGIN RETURN ''; END' at line 2 +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp0() SELECT 1; +SHOW CREATE PROCEDURE sp0; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp0 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp0`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP PROCEDURE sp0; +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp1() SELECT 1; +SHOW CREATE PROCEDURE sp1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp1 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE-@local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@local_mem_used +0 +SELECT VARIABLE_VALUE-@global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@global_mem_used +0 +DROP PROCEDURE sp1; # End of 10.3 tests |