tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Filip Hanik - Dev Lists <devli...@hanik.com>
Subject Re: Using Tomcat7 JDBC Connection Pool
Date Mon, 13 Feb 2012 22:12:35 GMT
Here is an example

String query = "BEGIN DBMS_LOCK.sleep(seconds => 5.01); END;"


It is pretty obvious that you can't do "SELECT 1; SELECT 1;" is this would result in TWO result
sets.
But you can create a block and execute any number of instructions

The above example executes a stored procedure.
Filip

On 2/9/2012 9:48 AM, Pid 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.
>
>
> 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
>>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message