PROCEDURE cache is maintained on a
per thread basis, in
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
THD threads at once, implemented with the
Cversion in file
sp_cache.cc, which is incremented by
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:
THDhas 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.
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.