Hi,
Here's the short version of my question:
Is there a way to have a statement handle cache shared among 2 or more database
handles?
And here's the verbose version:
We have an architecture where we have a server with one resident mod_perl
process (an Apache child) that serves requests from the
outside world (the typical web application model). This process accepts
requests for one of 40 different databases. To make our
application snappier, we are using DBI->connect_cached() so that once this
process initiates a connection to one of the 40
databases, the next request for that same database handle gets a snappier
response, since we cached that database connection handle.
Further, we cache our statements using prepare_cached() on everything in our
code. Thus the next request for that same database
handle will be snappier and snappier as the cache grows (by the way, we do use
Tie::Cache::LRU, not that it bears on this
conversation).
Now, all 40 of our databases have the *exact same* schema (248 tables). Thus
all the kinds of statements that are prepared for each
of the 40 databases are all usually going to be the same.
So in the interest of saving both memory and time, I wanted to have all the
database handles share the same statement handle cache
{CachedKids}. This saves memory because DBI won't have to have 40 statement
handle caches (one for each database connection), and
it saves time because DBI won't have to build a statement handle cache for any
new database handles that haven't yet been created
and cached.
So I tried tying the caches together by looping through the {Driver} hash and
seeing if another handle existed and if it did, use
its {CachedKids}. The problem, however, was this. Say the process starts up
afresh. Then we get a request for Database #1 which
asks for 'SELECT * FROM foo'. A connection to Database #1 then gets created
and cached, and then that statement gets prepared and
pinned in the statements cache. Then we get a request for Database #2 which
asks for 'SELECT * FROM foo'. Now a connection to
Database #2 gets created and cached, and then we point its statements cache to
the existing cache for Database #1. However, the
result was that the request that came for Database #2 ended up pulling
information from Database #1, because that statement was
cached in such a way that it could only pull from Database #1.
So, here's the question. Is it possible to have an option so that statements
that are cached are not locked-down to only receive
from a particular database handle, but could instead be used by any database
handle?
Thanks for reading through this lengthy explanation.
--
Eric Simon
