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.

7 files changed, 51 insertions, 0 deletions

diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 74c5c23aaa2..5e347959d9b 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -511,6 +511,11 @@ uint sync_binlog_period= 0, sync_relaylog_period= 0,
      sync_relayloginfo_period= 0, sync_masterinfo_period= 0;
 ulong expire_logs_days = 0;
 ulong rpl_recovery_rank=0;
+/**
+  Soft upper limit for number of sp_head objects that can be stored
+  in the sp_cache for one connection.
+*/
+ulong stored_program_cache_size= 0;
 
 const double log_10[] = {
   1e000, 1e001, 1e002, 1e003, 1e004, 1e005, 1e006, 1e007, 1e008, 1e009,
diff --git a/sql/mysqld.h b/sql/mysqld.h
index eb885417da3..67e39b6a46b 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -181,6 +181,7 @@ extern ulonglong max_binlog_cache_size, max_binlog_stmt_cache_size;
 extern ulong max_binlog_size, max_relay_log_size;
 extern ulong opt_binlog_rows_event_max_size;
 extern ulong rpl_recovery_rank, thread_cache_size;
+extern ulong stored_program_cache_size;
 extern ulong back_log;
 extern char language[FN_REFLEN];
 extern "C" MYSQL_PLUGIN_IMPORT ulong server_id;
diff --git a/sql/sp_cache.cc b/sql/sp_cache.cc
index 8972303ef03..d6445799a36 100644
--- a/sql/sp_cache.cc
+++ b/sql/sp_cache.cc
@@ -57,6 +57,20 @@ public:
   {
     my_hash_delete(&m_hashtable, (uchar *)sp);
   }
+
+  /**
+    Remove all elements from a stored routine cache if the current
+    number of elements exceeds the argument value.
+
+    @param[in] upper_limit_for_elements  Soft upper limit of elements that
+                                         can be stored in the cache.
+  */
+  void enforce_limit(ulong upper_limit_for_elements)
+  {
+    if (m_hashtable.records > upper_limit_for_elements)
+      my_hash_reset(&m_hashtable);
+  }
+
 private:
   void init();
   void cleanup();
@@ -228,6 +242,21 @@ ulong sp_cache_version()
 }
 
 
+/**
+  Enforce that the current number of elements in the cache don't exceed
+  the argument value by flushing the cache if necessary.
+
+  @param[in] c  Cache to check
+  @param[in] upper_limit_for_elements  Soft upper limit for number of sp_head
+                                       objects that can be stored in the cache.
+*/
+void
+sp_cache_enforce_limit(sp_cache *c, ulong upper_limit_for_elements)
+{
+ if (c)
+   c->enforce_limit(upper_limit_for_elements);
+}
+
 /*************************************************************************
   Internal functions 
  *************************************************************************/
diff --git a/sql/sp_cache.h b/sql/sp_cache.h
index 39448568a76..db70066587a 100644
--- a/sql/sp_cache.h
+++ b/sql/sp_cache.h
@@ -62,5 +62,6 @@ sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 void sp_cache_invalidate();
 void sp_cache_flush_obsolete(sp_cache **cp, sp_head **sp);
 ulong sp_cache_version();
+void sp_cache_enforce_limit(sp_cache *cp, ulong upper_limit_for_elements);
 
 #endif /* _SP_CACHE_H_ */
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 32ccb8f2c5f..a5d23858a8d 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5632,6 +5632,8 @@ void mysql_parse(THD *thd, char *rawbuf, uint length,
       query_cache_abort(&thd->query_cache_tls);
     }
     thd_proc_info(thd, "freeing items");
+    sp_cache_enforce_limit(thd->sp_proc_cache, stored_program_cache_size);
+    sp_cache_enforce_limit(thd->sp_func_cache, stored_program_cache_size);
     thd->end_statement();
     thd->cleanup_after_query();
     DBUG_ASSERT(thd->change_list.is_empty());
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););
 
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index f5a9ab3b55c..b640bdba491 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3253,6 +3253,13 @@ static Sys_var_tz Sys_time_zone(
        SESSION_VAR(time_zone), NO_CMD_LINE,
        DEFAULT(&default_tz), NO_MUTEX_GUARD, IN_BINLOG);
 
+static Sys_var_ulong Sys_sp_cache_size(
+       "stored_program_cache",
+       "The soft upper limit for number of cached stored routines for "
+       "one connection.",
+       GLOBAL_VAR(stored_program_cache_size), CMD_LINE(REQUIRED_ARG),
+       VALID_RANGE(256, 512 * 1024), DEFAULT(256), BLOCK_SIZE(1));
+
 
 /****************************************************************************
   Used templates