MySQL Internals Manual  /  ...  /  Stored Procedure Cache

16.6.1 Stored Procedure Cache

The PROCEDURE cache is maintained on a per thread basis, in THD::sp_proc_cache.

The function used to lookup the cache is sp_find_routine. It relies on the C++ class sp_cache for the low level implementation.

There is a global mechanism to invalidate all the caches of all the THD threads at once, implemented with the variable Cversion in file, which is incremented by function sp_cache_invalidate(). This global invalidation is used when the server executes DROP PROCEDURE or ALTER PROCEDURE statements.

Each entry in the cache is keyed by name, and consists of a linked list of stored procedure instances which are all duplicates of the same object. The reason for the list is recursion, when the runtime needs to evaluate several calls to the same procedure at once.

The runtime behavior of this caching mechanism has some limitations, and in particular:

  • each THD has its own cache, so each separate client connection to the server uses its own cache. Multiple client connections calling the same Stored Procedure will cause the parser to be invoked multiple times, and memory to be consumed multiple times.

  • If a given client constantly opens and closes a new connection to the server, and invokes Stored Procedures, the cache will be always empty, causing excessive parsing of used stored procedures on every invocation.

  • If a given client constantly keeps an existing connection to the server for a long time, and invokes Stored Procedures, the cache size will grow, consuming and retaining memory. In other words, memory limits or expulsion of cold members of the stored procedure cache is not implemented.

  • Calling sp_cache_invalidate() does not reclaim the cache memory. This memory will be reclaimed only if a Stored Procedure is looked up in the cache again, causing the cache to flush.

User Comments
Sign Up Login You must be logged in to post a comment.