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

[sergey@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


Ok, I'll get it from ServerConfig::GlobalContext using
getGlobalParam("num_threads") in InitServlet.
uuDBPool() constructor will take these value as the parameter. At this
point that is the only value that will be taken by uuDBPool() constructor.


>>>> - 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.


Hereis the list of events that may happen to connections:
1. Connection can be opened.
This will happen in uudb() class' constractor, db.open() will be called.
uudb object created at InitServlet::init()

2. Connection can be closed.
This will happen in uudb() class' destractor, db.close() will be called.

3. Connection can be checked in.
I'll use std::list named m_connections that will contain all available for
client Connections. When Connections checked in they are added to the end
of m_connections. getConnection() operation will pick first available
connection from m_connections to get round-robin usage.
If for some reason the connection that got picked is closed, I'll try
again picking the first Connection in the m_connections.

4. Connection can be checked out.
If Connection is available in m_connections it can be checked out and
returned to client, the counter of checked out Connections will be
incremented.
If no Connection is available in the m_connections and the maxConn limit
has not been reached, new Connection will be created and checked out to
client(the counter of checked out Connections will be incremented).

5. Connection can be released.
Connection can be returned to the pool. It will be added at the end of
m_connections and number of checked out connections will be decremented.




>>>> - 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.


Ok, that's how I think it will work:

1. When any connection is closed, CWaiter sends a signal(there are 2
options: with lock and without) to the connection queue.
2. Connection queue constantly checks for a signal (if there are no
available connections).
3. If signal is present, the connection is added to m_connections.

So basically it's like waiting in line in the bank until the teller gives
a signal "Next".


>>  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?


Yes, the connection is used during single request.
Whenever we call libdm class from servlet it will open DB connection and
login() stored procedure will be called, b/c libdm classes get uudb&
handle as as an argument to constructor.
After job for this servlet is done, the ~uudb() will close DB connection
and call logout() stored procedure.


>>>> 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.

Explanation above.

>>
>>>> 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...

Ok, understood.

>>
>> 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
>
>