Thanks for the info Dmitri! I'll probably dig into it at some point,
but I solved my immediate problem by cycling my DB connections before
they get to large, so it's dropped down on my priority list.
Thanks for your help,
- Sam
On Tue, 2007-01-30 at 12:03 +0300, Dmitri Lenev wrote:
> Hi, Samuel!
>
> * Samuel Ziegler <mysql@stripped> [07/01/25 20:09]:
> > Thanks for the feedback, Dmitri! I will try out 5.0.33 first thing this
> > morning.
> >
> > A couple of questions for you:
> > - I assume that copies of the stored procedures are kept due to the
> > overhead of preparing them for use, but why are the kept on a per
> > connection basis currently?
>
> Mainly due to historical reasons. At some point it was impossible to use
> objects which were created during parsing of routine's body in any thread/
> connection other than where they were created originally. Therefore each
> thread/connection had to use its own instance of routine object and so
> per-connection/thread routine caches were required.
>
> This limitation was removed during implementation of triggers. But we
> didn't implement global routine cache at that time.
>
> So this is something still to be done.
>
> > - Is there any reason that each connection can't have an upper limit on
> > the number of stored procedures it keeps around?
>
> I don't think there is any... Such limit, probably accompanied with some
> policy for retiring routines from cache (e.g. LRU), can be implemented.
>
> > - Where is the collection of per connection stored procedures
> > internally? (class/member variable)
>
> Look at "sp_proc_cache" and "sp_func_cache" members of "THD" class in
> sql/sql_class.h and at "sp_cache" class in sql/sp_cache.cc and .h.
>
> Best regards,
