tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pid <...@pidster.com>
Subject Re: Using Tomcat7 JDBC Connection Pool
Date Fri, 10 Feb 2012 08:45:21 GMT
On 09/02/2012 17:24, Amit wrote:
> Comment below
> 
> 
> 
> On 09-Feb-2012, at 10:18 PM, Pid <pid@pidster.com> wrote:
> 
>> On 09/02/2012 16:21, Amit wrote:
>>> Any thoughts on the first point about executing multiple SQL queries on physical
connection creation?
>>
>> I have no idea if it'll work, but I'd try:
>>
>> SELECT 1; SELECT 1;
>>
>> If you are controlling the pool (and you are) by passing in
>> username/password parameters each time, then you could do it as an extra
>> transaction thereafter.
>>
>>
> 
> Executing the queries after retrieving the connection would not be the right option since
they would execute every time the connection is borrowed instead of executing it only on physical
connection creation. 

I was assuming that, as you controlled the pool, you'd be able to figure
out when you run the extra commands.


> Can the jdbc interceptor architecture be extended to provide a method which is called
when the physical connection is created? ( similar to disconnect())

Interceptors can do a bunch of things.  What have you tried/looked at so
far?


p

>> p
>>
>>> On 09-Feb-2012, at 7:05 PM, Pid <pid@pidster.com> wrote:
>>>
>>>> On 09/02/2012 12:56, amit shah wrote:
>>>>> One more comment below about oracle UCP.
>>>>
>>>> <snip>
>>>>
>>>>>>>>> The pool returns members at random, so how would you
know which cached
>>>>>>>>> credentials you were getting?
>>>>>>>>>
>>>>>>>>> The credentials which are passed to the getConnection(String
username,
>>>>>>>> String password) method. When we configure the same pool
to be used for
>>>>>>>> multiple schema's the pool will *not *be configured with
default
>>>>>>> username
>>>>>>>> password.
>>>>>>>
>>>>>>> OK, so you create a bunch of connections with various credentials,
you
>>>>>>> want to cache those connections and only return them if the creds
match
>>>>>>> for the new request?
>>>>>>>
>>>>>>> So you're basically creating an uncontrolled pool per cred pair,
inside
>>>>>>> the outer pool which is controlled?
>>>>>>>
>>>>>>
>>>>>> Yes right.
>>>>
>>>> So why not create multiple controlled pools & not run into availability
>>>> problems?
>>>>
>>>>
>>>> <snip>
>>>>
>>>>>>> What overhead?
>>>>>>>
>>>>>>
>>>>>> The application server and database server resources (memory, cpu
etc) for
>>>>>> keeping the connections open?
>>>>
>>>> That's a total connection count dependent metric.
>>>>
>>>> So the overhead is virtually the same regardless of whether you have 5
>>>> pools or 1, if you have the same total number of connections.
>>>>
>>>>
>>>>>>> For e.g. If we have 5 tenants with 5
>>>>>>>> pools configured with 10 min pool size, we would have min
50 connections
>>>>>>>> always open to the database server. This count would be for
each
>>>>>>>> application server. If we had the same pool for all 5 tenants,
there
>>>>>>> would
>>>>>>>> be just 10 connections open per application server.
>>>>>>>
>>>>>>> There's a flaw in your logic.
>>>>>>>
>>>>>>> In your example there may be zero connections open for a given
tenant
>>>>>>> because they use a shared pool.
>>>>>>>
>>>>>>> So you might has well have separate pools with the minimum set
to 2 and
>>>>>>> still have more connections guaranteed per tenant, and the 10
you were
>>>>>>> aiming for.
>>>>>>>
>>>>>>> Worse, if you hit your max with other tenants, a remaining tenant
might
>>>>>>> not be able to get a connection at all, thus failing to address
one of
>>>>>>> the key requirements in a multi-tenant system - guaranteed availability.
>>>>>>>
>>>>>>> Probably true when all the tenants are actively used. As I said,
there is
>>>>>> always a flexibility in the configuration to use a separate pool
for a
>>>>>> particular tenant.
>>>>
>>>> That should be the default IMO.  You're asking for trouble otherwise.
>>>>
>>>>
>>>>>>>> Also the application can always provide a configuration flexibility
to
>>>>>>>> allow a tenant to use a separate pool instead of sharing
it with other
>>>>>>>> tenants (like I said above).
>>>>>>>>
>>>>>>>> This flexibility is provided by the Oracle Universal Connection
>>>>>>>> Pool<http://docs.oracle.com/cd/E11882_01/java.112/e12265/toc.htm>
>>>>>>>
>>>>>>> So if that's a better fit for your requirement, why not use it?
>>>>>>>
>>>>>>>
>>>>> It provides the feature I mentioned about by has lock contention issues.
>>>>> Tomcat 7 jdbc pool seems to be better and hence I was trying it out.
>>>>
>>>> !
>>>>
>>>> <snip>
>>>>
>>>>>>> If you are programmatically registering the pool, can you not
just
>>>>>>> register it with the MBean server yourself?
>>>>>>>
>>>>>>> Ok I will try this and provide an update.
>>>>
>>>> Cool.
>>>>
>>>>
>>>> p
>>>>
>>>>
>>>>
>>>>
>>>> -- 
>>>>
>>>> [key:62590808]
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>
>>
>>
>> -- 
>>
>> [key:62590808]
>>
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
> 


-- 

[key:62590808]


Mime
View raw message