Re: Db pool [was: My little comments to CPP code]

["Alexey Parshin" <alexeyp@gmail.com>]

Gentlemen,

Before we finalize the connection pool, I'd like to wish for something. When we finish working with the connection obtained from the pool, can we close that connection to release all the resources (temporary tables, primarily) to the server? This would make us to always have only freshly created connections in the pool..

I've almost finished with my emergency on the job, so I'd start working with UU by the end of the week.

2007/3/27, Ilya A. Volynets-Evenbakh < ilya@total-knowledge.com>:

sergey@total-knowledge.com wrote:
>> sergey@total-knowledge.com wrote:
>>
>>> - What pooling strategies we want to support (and why)
>>>
>>> 1. Close connections that have not been used for a specified time.
>>> 2. Queueing connections. If there is max connections for the pool
>>> specified, then objects requesting a connection can be put on hold until
>>> another object frees its connection.
>>>
>>>
>> How about fixed number of connections open at creation time?
>> For example, make it as large as CPPSERV's thread pool? This
>> way we'll always have a connection ready when a request arrives.
>>
> I got the idea and I see that thread pool size retrieved from engine.xml
> or set to 15 if not presented in engine.xml.
> Looks like I'll have to parse engine.xml too to get this number for DB pool.
>
Of course not. It's all parsed for you already. Just add an app-level
parameter
>>> - How will pooling and HTTP request object lifetimes will interoperate
>>>
>>> Pool initialized at init() time(not sure about this one), then the same
>>> connection can be used for subsequent requests. Since servlets can keep
>>> information between requests, a pool lives longer then HTTP request.
>> And what happens to connection during request processing?
>>
> It stays open. That's the whole point of having DB pool. When many hits
> arrive to the same servlet at once, the pool will open as many connections
> as needed (if not exceeded DB pool size).
>
Opening and closing are not the only things that can happen to connections.
>>> - What will API look like
>>>
>>> 1. The uuDBPool() constructor that takes these parameters:
>>> string login,
>>> string password,
>>>
>>>
>> Whose login and password?
>>
>
>
> This is not current user's login/password, I was wrong. This is DB
> login/password. This is used for differentiating users of the pool in case
> of some of the operations in DB are restricted to authorized users only.
>
I don't remember what we ended up with for sure, but I think we decided
on single
set of database authentication credentials. Alexey would have to confirm
that.
>>> int server(this one is needed for login() proc),
>>> string sessionid(needed for login() proc, not really used now),
>>>
>>>
>> How does that correlate with initializing the pool at servlet init time?
>>
>
> I was wrong here again. Current user login/password, server and sessionid
> will be used for initializing a single connection not at init() time, but
> the time when DB connection is actually needed.
>
>
>
>>> int maxCon(max connections for this pool).
>>>
>>>
>>> - Provide few use cases relevant for our application
>>>
>>> 1. Browser sends request to the servlet.
>>> 2. No connections available in the pool.
>>> 3. Is maxCon been reached?
>>>     a. Case "NO"
>>>             1) Create new connection using getConnection()' login() procedure.
>>>             2) Increament connections counter in the pool.
>>>             3) Return connection to the client.
>>>     b. Case "YES"
>>>             1) Wait 'timeout' milliseconds.
>>>
>>>
>> UGH. No. Read up on mutexes and signals. Also check out ThreadPool
>> implementation
>> in cppserv.
>>
>
>
> I read up on mysterious mutexes and signals and tried to learn how
> ThreadPool works in cppserv.
> The pool job is pretty much similar in both ThreadPool and uuDBPool imho.
>
That is correct. That is why they both that "Pool" in their name :-)
> Mutex object will prevent from using the same connection by more than one
> user at the same time. In cthreadpool.cpp you use sptk::CWaiter' lock()
> and unlock() operations to do this kind of job for threads.
>
Correct up to here.
> Theoretically speaking, signals are the "notificators" that sent to a
> process, they interrupt whatever the process is doing at this moment.
You mixed up system-level signals (as in signal(2)), and inter-thread
signals, as implemented by CWaiter::sendSignal() & friends.
Ideas below are close, but not quite right. Re-think with this note
in mind.
>  My
> understanding is that in order to free the currently used connection and
> to return it to the pool, the process has to get a signal that this
> connection is no longer used(for example, when user leaves the servlet his
> connection stays unused for some time that defined in the pool, then is
> considered "abandoned"). We would never know about it if we wouldn't use
> some kind of signal to notify the pool.
> So the use case will be:
>
> 1. Browser sends request to the servlet.
> 2. No connections available in the pool.
> 3. Is maxCon been reached?
>         a. Case "NO"
>                 1) Create new connection using getConnection()' login()
> procedure.
>                 2) Increament connections counter in the pool.
>                 3) Return connection to the client.
>         b. Case "YES"
>                 1) Wait for signal that indicates that there is an
> available connection in the pool
>                 2) Increament connections counter in the pool.
>               3) Return connection to the client.
>
>
>
>>>             2) Return to 3.
>>>
>>>
>>> Another case:
>>>
>>> 1. Browser sends request to the servlet.
>>> 2. Connections available in the pool.
>>> 3. Return a connection using getConnection()
>>>
>>>
>>> One more:
>>>
>>> 1. User leaves the site.
>>>
>>>
>> How do we know that?
>>
>
> When user leaves the site his connection stays unused for some time that
> defined in the pool, then is considered "abandoned"
>
What connection? Didn't we decide that connection is used during single
request?
>>> 2. Connection returned to the pool using releaseConnection() function
>>>
>>>
>> Why did we have a connection?
>>
>
> B/c it was established by the pool when user initilly sent a request to
> servlet that uses DB connection.
>
Again, see above. It can't be stressed enough that we cannot open a database
connection for each session.
>
>>> 3. Number of used connections is decremented.
>>>
>>>
>>> I'm not sure when shutdown() should be called.
>>>
>>>
>> Why did you introduce it then?
>>
> I found plenty of articles on internet about what typical DB connection
> pool should do, some of them have shutdown()-like operations, that provide
> a method for graceful shutdown. I guess it should be used when server is
> shut down, it's uuDBPool() responsibility to close all connections in the
> pool.
>
Don't forget about a thing called destructor...
>
> I have a question:
> Is it right idea to use sptk::CWaiter class for my DB pool? I checked this
> class in sptk3, it has Mutex object and all the signal methods that I
> need.
>
Yes.
> Also I found a source on codeproject.com that has implementation of
> template based Generic Pool using C++. It does what we need in the most
> generic way, you can check if you'd like to:
> http://www.codeproject.com/library/Generic_Pool.asp
>
I glanced a while back at it. I don't remember what license is hooked to
that code though. Nor am I sure it'll work too well for us, but feel free
to study it for ideas.

--
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com

-- 
Alexey Parshin,
http://www.sptk.net