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

[sergey@total-knowledge.com]

> sergey@total-knowledge.com wrote:
>>> 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.
>>
> Let me repeat. No.  Just add an app-level parameter.

Ok, inside <app> scope in engine.xml
<num_connections value="bla-bla"/> will be added.


>
>> 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()
>>
> During request processing?
>> 2. Connection can be closed.
>> This will happen in uudb() class' destractor, db.close() will be called.
>>
> During request processing?


Maybe that's how things should be:

We'll have to override init() and destroy() for each servlet. I'm not sure
what's the best way to organize it, certainly it's not pretty to do it
inside of each servlet itself. Maybe we should use EnvironmentSetupServlet
where among with overriden init() and destroy() will be other common for
all servlets functionalities(like, for example, getting sessionid from
HttpSession object, etc..). This EnvironmentSetupServlet will be included
in Header.cpp.
In EnvironmentSetupServlet::init() we open the connection and call login()
stored procedure. All needed parameters for login() will be taken from
request.
In EnvironmentSetupServlet::destroy() we close the connection and call
logout() stored procedure.
Also perhaps we can retrieve DB handle in EnvironmentSetupServlet too.



>> 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 is the difference between 3 and 5?

Sorry, same thing.

>>>
>>> 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".
>>
> I have a feeling you are missing large piece of scenario here.
> Who is waiting in line, and for what? Assign your roles properly.

Can't quite figure it out.
I see that you use Task object in CThreadPool implementation, perhaps I
should have something similar in uuDBPool. The queue consists of Tasks,
and Task is actually the job that pool does(getConnection(),
releaseConnection()).
Maybe you give me hints?


>>> What connection? Didn't we decide that connection is used during single
>>> request?
>>>
>> Yes, the connection is used during single request.
>>
> So it has nothing to do with when user leaves the site.
>> 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.
>>
> So, what if you have two model-layer classes, that need to perform some
> related actions on single database connection during single request?

If I implement EnvironmentSetupServlet the way I described above, it won't
be a problem, since
1. Connection will be opened in this servlet init() time.
2. Servlet' service() will pass DB handle to as many model-layer classes
as needed.
3. Connection will be closed in this servlet destroy() time.


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