Fixed bug#11753187 (formerly known as bug 44585): SP_CACHE BEHAVES AS

MEMORY LEAK.

Background:
 - There are caches for stored functions and stored procedures (SP-cache);
 - There is no similar cache for events;
 - Triggers are cached together with TABLE objects;
 - Those SP-caches are per-session (i.e. specific to each session);
 - A stored routine is represented by a sp_head-instance internally;
 - SP-cache basically contains sp_head-objects of stored routines, which
   have been executed in a session;
 - sp_head-object is added into the SP-cache before the corresponding
   stored routine is executed;
 - SP-cache is flushed in the end of the session.

The problem was that SP-cache might grow without any limit. Although this
was not a pure memory leak (the SP-cache is flushed when session is closed),
this is still a problem, because the user might take much memory by
executing many stored routines.

The patch fixes this problem in the least-intrusive way. A soft limit
(similar to the size of table definition cache) is introduced. To represent
such limit the new runtime configuration parameter 'stored_program_cache'
is introduced. The value of this parameter is stored in the new global
variable stored_program_cache_size that used to control the size of SP-cache
to overflow. 

The parameter 'stored_program_cache' limits number of cached routines for
each thread. It has the following min/default/max values given from support:
  min = 256, default = 256, max = 512 * 1024.
Also it should be noted that this parameter limits the size of 
each cache (for stored procedures and for stored functions) separately.

The SP-cache size is checked after top-level statement is parsed.
If SP-cache size exceeds the limit specified by parameter
'stored_program_cache' then SP-cache is flushed and memory allocated for
cache objects is freed. Such approach allows to flush cache safely 
when there are dependencies among stored routines.

1 files changed, 6 insertions, 0 deletions

diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 48b6886f6f2..cfdf7d27e58 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2195,6 +2195,9 @@ void mysqld_stmt_prepare(THD *thd, const char *packet, uint packet_length)
 
   thd->protocol= save_protocol;
 
+  sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size);
+  sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size);
+
   /* check_prepared_statemnt sends the metadata packet in case of success */
   DBUG_VOID_RETURN;
 }
@@ -2560,6 +2563,9 @@ void mysqld_stmt_execute(THD *thd, char *packet_arg, uint packet_length)
   stmt->execute_loop(&expanded_query, open_cursor, packet, packet_end);
   thd->protocol= save_protocol;
 
+  sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size);
+  sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size);
+
   /* Close connection socket; for use with client testing (Bug#43560). */
   DBUG_EXECUTE_IF("close_conn_after_stmt_execute", vio_close(thd->net.vio););