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.


sql/mysqld.cc:
  Added global variable stored_program_cache_size to store value of
  configuration parameter 'stored-program-cache'.
sql/mysqld.h:
  Added declaration of global variable stored_program_cache_size.
sql/sp_cache.cc:
  Extended interface for sp_cache by adding helper routine
  sp_cache_enforce_limit to control size of stored routines cache for
  overflow. Also added method enforce_limit into class sp_cache that
  implements control of cache size for overflow.
sql/sp_cache.h:
  Extended interface for sp_cache by adding standalone routine
  sp_cache_enforce_limit to control size of stored routines cache
  for overflow.
sql/sql_parse.cc:
  Added flush of sp_cache after processing of next sql-statement
  received from a client.
sql/sql_prepare.cc:
  Added flush of sp_cache after preparation/execution of next prepared
  sql-statement received from a client.
sql/sys_vars.cc:
  Added support for configuration parameter stored-program-cache.

1 files changed, 29 insertions, 0 deletions

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 
  *************************************************************************/