You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by amit shah <am...@gmail.com> on 2012/02/08 12:41:54 UTC
Using Tomcat7 JDBC Connection Pool
I am trying to use the tomcat 7 jdbc connection pool in our application by
using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic connection
pool works fine. I have few questions/clarifications
1. Executing multiple statements on connection initialization
- The pool provides a flexibility to execute a single sql query
when the physical connection is established (initSQL property). I couldn't
find a way to execute multiple sql queries on connection initialization.
The JDBC Interceptor mechanism also doesn't seem to help out. Any
suggestions?
2. alternateUserNameAllowed property
- If a connection is requested with the credentials user1/password1 and the
connection was previously connected using user2/password2, the connection
will be closed, and reopened with the requested credentials. This property
was added as an enhancement to bug
50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
I didn’t understand the reason behind closing the previous connection. Can
the pool not still maintain the previous connection and open a new
connection if the user/password combination do not match?. This way the
same pool can be used for multiple schemas.
3. JMX & Statistics
- How can one enable jmx when tomcat 7 jdbc connection pool is
used independently? I tried specifying the jmx vm options
(-Dcom.sun.management.jmxremote
-Dcom.sun.management.jmxremote.port=1617
-Dcom.sun.management.jmxremote.authenticate=false
-Dcom.sun.management.jmxremote.ssl=false)
but they don’t seem to help out? Neither did I found a way to print or
access the pool statistics programmatically. Any suggestions?
Thank you,
Amit
Re: Not able to get threadcount and thread busy metrices in Tocat
7.0
Posted by Pid <pi...@pidster.com>.
On 10/02/2012 13:03, Mendiratta, Shashank wrote:
> Hi ,
> I am facing a problem. We used to monitor tomcat JVM and used to monitor " current ThreadCount " and "current Threadbusy"
> Recently we upgraded from tomcat 6.0 to tomcat 7.0.16 and we are not able to get these metrices .
> On digging in we found that in tomcat6.0 the thread pool metrices were retrieved by class "org.apache.tomcat.util.threads.ThreadPool "
> But in tomcat 7.0 there is no such class instead it is replace by org.apache.tomcat.util.threads.ThreadPoolExecutor which does not give the above parameters.
> So my question is tomcat 7.0 how can we get the above metrices
>
> P.S- > Am new to tomcat so please bear if it is a silly question
Please start a new thread, rather than replying to an existing one* and
editing the reply/body - which is called thread-hijacking and puts your
new message in the middle of an existing thread, for those of us using a
threaded conversation view.
p
* "Using Tomcat7 JDBC Connection Pool" 09/02/2012 16:21
--
[key:62590808]
Not able to get threadcount and thread busy metrices in Tocat 7.0
Posted by "Mendiratta, Shashank" <Sh...@intuit.com>.
Hi ,
I am facing a problem. We used to monitor tomcat JVM and used to monitor " current ThreadCount " and "current Threadbusy"
Recently we upgraded from tomcat 6.0 to tomcat 7.0.16 and we are not able to get these metrices .
On digging in we found that in tomcat6.0 the thread pool metrices were retrieved by class "org.apache.tomcat.util.threads.ThreadPool "
But in tomcat 7.0 there is no such class instead it is replace by org.apache.tomcat.util.threads.ThreadPoolExecutor which does not give the above parameters.
So my question is tomcat 7.0 how can we get the above metrices
P.S- > Am new to tomcat so please bear if it is a silly question
Regards
Shashank
Re: Using Tomcat7 JDBC Connection Pool
Posted by amit shah <am...@gmail.com>.
Thanks for the response.
The below suggestion to make an SP call worked out.
Another update on programmatic registering the pool to jmx - It worked out
and here is how I did it
MBeanServer mBeanServer =
ManagementFactory.getPlatformMBeanServer();
try {
tomcatDataSource.createPool();
mBeanServer.registerMBean(tomcatDataSource.getPool().getJmxPool(), new
ObjectName("tomcat.jdbc:type=ConnectionPool"));
} catch (Exception e) {
throw new RuntimeException("Error registering the tomcat pool
data source", e);
}
Thanks for the quick support on my queries.
On Tue, Feb 14, 2012 at 3:42 AM, Filip Hanik - Dev Lists <devlists@hanik.com
> wrote:
> 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<pi...@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<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<us...@tomcat.apache.org>
>>> For additional commands, e-mail: users-help@tomcat.apache.org
>>>
>>>
>>
>
> ------------------------------**------------------------------**---------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.**apache.org<us...@tomcat.apache.org>
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: Using Tomcat7 JDBC Connection Pool
Posted by Filip Hanik - Dev Lists <de...@hanik.com>.
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<pi...@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
Re: Using Tomcat7 JDBC Connection Pool
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Amit,
On 2/9/12 11:21 AM, Amit wrote:
> Any thoughts on the first point about executing multiple SQL
> queries on physical connection creation?
Doesn't Oracle have a CALL() syntax in a regular query?
How are you getting parametric information from the client code into
the JDBC connection set-up code? ThreadLocals or something like that?
Otherwise, I'm not sure how you'd know that the NLS settings should be
set to something specific. Can't you pass that stuff in via the URL, too?
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk85hHwACgkQ9CaO5/Lv0PC4NgCeKkh0knaDzEsbd5eQDyAXcaH7
bkcAoJJEJnmuH6+3spTmv5Se+I+fQIBw
=i/p+
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
On 09/02/2012 17:24, Amit wrote:
> Comment below
>
>
>
> On 09-Feb-2012, at 10:18 PM, Pid <pi...@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 <pi...@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]
Re: Using Tomcat7 JDBC Connection Pool
Posted by Amit <am...@gmail.com>.
Comment below
On 09-Feb-2012, at 10:18 PM, Pid <pi...@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.
Can the jdbc interceptor architecture be extended to provide a method which is called when the physical connection is created? ( similar to disconnect())
> p
>
>> On 09-Feb-2012, at 7:05 PM, Pid <pi...@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
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
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 <pi...@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]
Re: Using Tomcat7 JDBC Connection Pool
Posted by Amit <am...@gmail.com>.
Any thoughts on the first point about executing multiple SQL queries on physical connection creation?
On 09-Feb-2012, at 7:05 PM, Pid <pi...@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
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
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]
Re: Using Tomcat7 JDBC Connection Pool
Posted by amit shah <am...@gmail.com>.
One more comment below about oracle UCP.
On Thu, Feb 9, 2012 at 5:10 PM, amit shah <am...@gmail.com> wrote:
> Comments below.
>
> On Wed, Feb 8, 2012 at 9:19 PM, Pid <pi...@pidster.com> wrote:
>
>> On 08/02/2012 14:59, amit shah wrote:
>> > Responses below.
>> >
>> > Thanks.
>> >
>> > On Wed, Feb 8, 2012 at 7:14 PM, Pid <pi...@pidster.com> wrote:
>> >
>> >> On 08/02/2012 12:30, amit shah wrote:
>> >>> Thanks for the reply. Responses below.
>> >>>
>> >>> On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
>> >>>
>> >>>> On 08/02/2012 11:41, amit shah wrote:
>> >>>>> I am trying to use the tomcat 7 jdbc connection pool in our
>> application
>> >>>> by
>> >>>>> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic
>> >> connection
>> >>>>> pool works fine. I have few questions/clarifications
>> >>>>>
>> >>>>>
>> >>>>>
>> >>>>> 1. Executing multiple statements on connection initialization
>> >>>>>
>> >>>>> - The pool provides a flexibility to execute a single
>> sql
>> >>>> query
>> >>>>> when the physical connection is established (initSQL property). I
>> >>>> couldn't
>> >>>>> find a way to execute multiple sql queries on connection
>> >> initialization.
>> >>>>> The JDBC Interceptor mechanism also doesn't seem to help out. Any
>> >>>>> suggestions?
>> >>>>
>> >>>> Why do you want to execute multiple SQL statements for each
>> connection
>> >>>> in the pool? Normally you want to do the absolute minimum to
>> validate
>> >>>> the connection.
>> >>>
>> >>> These sql statements are not for validating the connection. We use
>> Oracle
>> >>> as our database server. So I wanted to execute the NLS (National
>> >> Language
>> >>> Setting) queries after the connection is established.
>> >>
>> >> You need to do this because it's multi-tenant (per below) and each
>> >> tenant may require different settings?
>> >>
>> >>
>> > Yes you are right. Each tenant could have different language settings.
>> Even
>> > if the settings are same for all the tenants, the queries are to be
>> > executed on every physical connection creation. These settings cannot be
>> > set when the schema's are created. They are to be set per session level.
>> > I understand that one way to implement this would be to embed the
>> queries
>> > in a stored procedure but I was just trying to understand if there was a
>> > simpler way of achieving this through configuration. Let me know if
>> there
>> > is a way out.
>> >
>>
>
> Executing an SP doesn't seem to work out since internally the tomcat jdbc
> pool code tries to execute the initSQL query using a Statement object
> instead of a CallableStatement which would be required in this case. Any
> suggestions/alternatives?
>
>
>> >
>> >>
>> >>>>> 2. alternateUserNameAllowed property
>> >>>>>
>> >>>>> - If a connection is requested with the credentials user1/password1
>> and
>> >>>> the
>> >>>>> connection was previously connected using user2/password2, the
>> >> connection
>> >>>>> will be closed, and reopened with the requested credentials. This
>> >>>> property
>> >>>>> was added as an enhancement to bug
>> >>>>> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
>> >>>>> I didn’t understand the reason behind closing the previous
>> connection.
>> >>>> Can
>> >>>>> the pool not still maintain the previous connection and open a new
>> >>>>> connection if the user/password combination do not match?. This way
>> the
>> >>>>> same pool can be used for multiple schemas.
>> >>>>
>> >>>> The old connection is closed so that the current user (who has
>> different
>> >>>> credentials) can't then use that connection.
>> >>>>
>> >>>> If you want to use the old connection, don't pass in new credentials.
>> >>>>
>> >>>> Note: this is a pool of connections, not a single connection.
>> >>>
>> >>> Can the pool still not close the old connection and maintain a map of
>> >>> username/password vs connection. So that the same pool can be used for
>> >>> multiple schemas on an Oracle server. This would help out in
>> implementing
>> >>> multi-tenant applications where not all environments are active at the
>> >> same
>> >>> time. So the same pool can be used for multiple environments. The
>> >>> application can still provides the ability the create a specific pool
>> for
>> >>> individual environments. Let me know if anything is unclear.
>> >>
>> >> 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.
>
>>
>>
>> >> If the pool kept all of the connections open with different credentials
>> >> how can you guarantee availability/performance/SLA for each tenant?
>> >
>> > All the connections can still follow the same configuration rules of
>> > timeout.
>>
>> Not relevant if the connections are in use.
>>
>>
>> >> What is the advantage of a single pool in this case?
>> >>
>> >
>> > The benefit we gain is not having many pools (reduces the pool mgmt
>> > overhead on the application server) which means less number of
>> application
>> > server and database server resources.
>>
>> What overhead?
>>
>
> The application server and database server resources (memory, cpu etc) for
> keeping the connections open?
>
>
>>
>>
>> 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.
>
>>
>> > 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.
>
>> >> You are asking the wrong question IMHO.
>> >>
>> >>
>> >>>>> 3. JMX & Statistics
>> >>>>>
>> >>>>> - How can one enable jmx when tomcat 7 jdbc connection
>> pool
>> >>>> is
>> >>>>> used independently? I tried specifying the jmx vm options
>> >>>>> (-Dcom.sun.management.jmxremote
>> >>>>> -Dcom.sun.management.jmxremote.port=1617
>> >>>>> -Dcom.sun.management.jmxremote.authenticate=false
>> >>>>> -Dcom.sun.management.jmxremote.ssl=false)
>> >>>>> but they don’t seem to help out? Neither did I found a way to print
>> or
>> >>>>> access the pool statistics programmatically. Any suggestions?
>> >>>>
>> >>>> I don't know the answer offhand, but I assume that an examination of
>> the
>> >>>> source code would lead to an understanding of how Tomcat handles
>> this.
>> >>>>
>> >>>> I had a look at the source code. The ConnectionPool class includes a
>> >> check
>> >>> where a call is made to create an MBean but I couldn't see any calls
>> the
>> >>> register the MBean with the MBeanServer. Hence thought of posting a
>> >>> question. Any suggestions on the statistics part. There are no methods
>> >>> in org.apache.tomcat.jdbc.pool.DataSource for statistics.
>> >>
>> >> What statistics do you want?
>> >>
>> >> The Interceptor mechanism can be used to create bespoke statistics.
>> >>
>> >> The statistics to know the current pool size, current borrowed
>> > connections, current available connections, avg connection wait time
>> etc.
>>
>>
>> Some of those are available on the ConnectionPoolMBean.
>>
>>
>> http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/jmx/ConnectionPoolMBean.java
>>
>> Some of those are available in existing Interceptors, have you looked at
>> those?
>>
>>
>> http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/interceptor/
>>
>> 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.
>
>>
>> p
>>
>>
>>
>> --
>>
>> [key:62590808]
>>
>>
>
Re: Using Tomcat7 JDBC Connection Pool
Posted by amit shah <am...@gmail.com>.
Comments below.
On Wed, Feb 8, 2012 at 9:19 PM, Pid <pi...@pidster.com> wrote:
> On 08/02/2012 14:59, amit shah wrote:
> > Responses below.
> >
> > Thanks.
> >
> > On Wed, Feb 8, 2012 at 7:14 PM, Pid <pi...@pidster.com> wrote:
> >
> >> On 08/02/2012 12:30, amit shah wrote:
> >>> Thanks for the reply. Responses below.
> >>>
> >>> On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
> >>>
> >>>> On 08/02/2012 11:41, amit shah wrote:
> >>>>> I am trying to use the tomcat 7 jdbc connection pool in our
> application
> >>>> by
> >>>>> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic
> >> connection
> >>>>> pool works fine. I have few questions/clarifications
> >>>>>
> >>>>>
> >>>>>
> >>>>> 1. Executing multiple statements on connection initialization
> >>>>>
> >>>>> - The pool provides a flexibility to execute a single sql
> >>>> query
> >>>>> when the physical connection is established (initSQL property). I
> >>>> couldn't
> >>>>> find a way to execute multiple sql queries on connection
> >> initialization.
> >>>>> The JDBC Interceptor mechanism also doesn't seem to help out. Any
> >>>>> suggestions?
> >>>>
> >>>> Why do you want to execute multiple SQL statements for each connection
> >>>> in the pool? Normally you want to do the absolute minimum to validate
> >>>> the connection.
> >>>
> >>> These sql statements are not for validating the connection. We use
> Oracle
> >>> as our database server. So I wanted to execute the NLS (National
> >> Language
> >>> Setting) queries after the connection is established.
> >>
> >> You need to do this because it's multi-tenant (per below) and each
> >> tenant may require different settings?
> >>
> >>
> > Yes you are right. Each tenant could have different language settings.
> Even
> > if the settings are same for all the tenants, the queries are to be
> > executed on every physical connection creation. These settings cannot be
> > set when the schema's are created. They are to be set per session level.
> > I understand that one way to implement this would be to embed the queries
> > in a stored procedure but I was just trying to understand if there was a
> > simpler way of achieving this through configuration. Let me know if there
> > is a way out.
> >
>
Executing an SP doesn't seem to work out since internally the tomcat jdbc
pool code tries to execute the initSQL query using a Statement object
instead of a CallableStatement which would be required in this case. Any
suggestions/alternatives?
> >
> >>
> >>>>> 2. alternateUserNameAllowed property
> >>>>>
> >>>>> - If a connection is requested with the credentials user1/password1
> and
> >>>> the
> >>>>> connection was previously connected using user2/password2, the
> >> connection
> >>>>> will be closed, and reopened with the requested credentials. This
> >>>> property
> >>>>> was added as an enhancement to bug
> >>>>> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
> >>>>> I didn’t understand the reason behind closing the previous
> connection.
> >>>> Can
> >>>>> the pool not still maintain the previous connection and open a new
> >>>>> connection if the user/password combination do not match?. This way
> the
> >>>>> same pool can be used for multiple schemas.
> >>>>
> >>>> The old connection is closed so that the current user (who has
> different
> >>>> credentials) can't then use that connection.
> >>>>
> >>>> If you want to use the old connection, don't pass in new credentials.
> >>>>
> >>>> Note: this is a pool of connections, not a single connection.
> >>>
> >>> Can the pool still not close the old connection and maintain a map of
> >>> username/password vs connection. So that the same pool can be used for
> >>> multiple schemas on an Oracle server. This would help out in
> implementing
> >>> multi-tenant applications where not all environments are active at the
> >> same
> >>> time. So the same pool can be used for multiple environments. The
> >>> application can still provides the ability the create a specific pool
> for
> >>> individual environments. Let me know if anything is unclear.
> >>
> >> 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.
>
>
> >> If the pool kept all of the connections open with different credentials
> >> how can you guarantee availability/performance/SLA for each tenant?
> >
> > All the connections can still follow the same configuration rules of
> > timeout.
>
> Not relevant if the connections are in use.
>
>
> >> What is the advantage of a single pool in this case?
> >>
> >
> > The benefit we gain is not having many pools (reduces the pool mgmt
> > overhead on the application server) which means less number of
> application
> > server and database server resources.
>
> What overhead?
>
The application server and database server resources (memory, cpu etc) for
keeping the connections open?
>
>
> 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.
>
> > 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?
>
>
> >> You are asking the wrong question IMHO.
> >>
> >>
> >>>>> 3. JMX & Statistics
> >>>>>
> >>>>> - How can one enable jmx when tomcat 7 jdbc connection
> pool
> >>>> is
> >>>>> used independently? I tried specifying the jmx vm options
> >>>>> (-Dcom.sun.management.jmxremote
> >>>>> -Dcom.sun.management.jmxremote.port=1617
> >>>>> -Dcom.sun.management.jmxremote.authenticate=false
> >>>>> -Dcom.sun.management.jmxremote.ssl=false)
> >>>>> but they don’t seem to help out? Neither did I found a way to print
> or
> >>>>> access the pool statistics programmatically. Any suggestions?
> >>>>
> >>>> I don't know the answer offhand, but I assume that an examination of
> the
> >>>> source code would lead to an understanding of how Tomcat handles this.
> >>>>
> >>>> I had a look at the source code. The ConnectionPool class includes a
> >> check
> >>> where a call is made to create an MBean but I couldn't see any calls
> the
> >>> register the MBean with the MBeanServer. Hence thought of posting a
> >>> question. Any suggestions on the statistics part. There are no methods
> >>> in org.apache.tomcat.jdbc.pool.DataSource for statistics.
> >>
> >> What statistics do you want?
> >>
> >> The Interceptor mechanism can be used to create bespoke statistics.
> >>
> >> The statistics to know the current pool size, current borrowed
> > connections, current available connections, avg connection wait time etc.
>
>
> Some of those are available on the ConnectionPoolMBean.
>
>
> http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/jmx/ConnectionPoolMBean.java
>
> Some of those are available in existing Interceptors, have you looked at
> those?
>
>
> http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/interceptor/
>
> 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.
>
> p
>
>
>
> --
>
> [key:62590808]
>
>
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
On 08/02/2012 14:59, amit shah wrote:
> Responses below.
>
> Thanks.
>
> On Wed, Feb 8, 2012 at 7:14 PM, Pid <pi...@pidster.com> wrote:
>
>> On 08/02/2012 12:30, amit shah wrote:
>>> Thanks for the reply. Responses below.
>>>
>>> On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
>>>
>>>> On 08/02/2012 11:41, amit shah wrote:
>>>>> I am trying to use the tomcat 7 jdbc connection pool in our application
>>>> by
>>>>> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic
>> connection
>>>>> pool works fine. I have few questions/clarifications
>>>>>
>>>>>
>>>>>
>>>>> 1. Executing multiple statements on connection initialization
>>>>>
>>>>> - The pool provides a flexibility to execute a single sql
>>>> query
>>>>> when the physical connection is established (initSQL property). I
>>>> couldn't
>>>>> find a way to execute multiple sql queries on connection
>> initialization.
>>>>> The JDBC Interceptor mechanism also doesn't seem to help out. Any
>>>>> suggestions?
>>>>
>>>> Why do you want to execute multiple SQL statements for each connection
>>>> in the pool? Normally you want to do the absolute minimum to validate
>>>> the connection.
>>>
>>> These sql statements are not for validating the connection. We use Oracle
>>> as our database server. So I wanted to execute the NLS (National
>> Language
>>> Setting) queries after the connection is established.
>>
>> You need to do this because it's multi-tenant (per below) and each
>> tenant may require different settings?
>>
>>
> Yes you are right. Each tenant could have different language settings. Even
> if the settings are same for all the tenants, the queries are to be
> executed on every physical connection creation. These settings cannot be
> set when the schema's are created. They are to be set per session level.
> I understand that one way to implement this would be to embed the queries
> in a stored procedure but I was just trying to understand if there was a
> simpler way of achieving this through configuration. Let me know if there
> is a way out.
>
>
>>
>>>>> 2. alternateUserNameAllowed property
>>>>>
>>>>> - If a connection is requested with the credentials user1/password1 and
>>>> the
>>>>> connection was previously connected using user2/password2, the
>> connection
>>>>> will be closed, and reopened with the requested credentials. This
>>>> property
>>>>> was added as an enhancement to bug
>>>>> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
>>>>> I didn’t understand the reason behind closing the previous connection.
>>>> Can
>>>>> the pool not still maintain the previous connection and open a new
>>>>> connection if the user/password combination do not match?. This way the
>>>>> same pool can be used for multiple schemas.
>>>>
>>>> The old connection is closed so that the current user (who has different
>>>> credentials) can't then use that connection.
>>>>
>>>> If you want to use the old connection, don't pass in new credentials.
>>>>
>>>> Note: this is a pool of connections, not a single connection.
>>>
>>> Can the pool still not close the old connection and maintain a map of
>>> username/password vs connection. So that the same pool can be used for
>>> multiple schemas on an Oracle server. This would help out in implementing
>>> multi-tenant applications where not all environments are active at the
>> same
>>> time. So the same pool can be used for multiple environments. The
>>> application can still provides the ability the create a specific pool for
>>> individual environments. Let me know if anything is unclear.
>>
>> 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?
>> If the pool kept all of the connections open with different credentials
>> how can you guarantee availability/performance/SLA for each tenant?
>
> All the connections can still follow the same configuration rules of
> timeout.
Not relevant if the connections are in use.
>> What is the advantage of a single pool in this case?
>>
>
> The benefit we gain is not having many pools (reduces the pool mgmt
> overhead on the application server) which means less number of application
> server and database server resources.
What overhead?
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.
> 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?
>> You are asking the wrong question IMHO.
>>
>>
>>>>> 3. JMX & Statistics
>>>>>
>>>>> - How can one enable jmx when tomcat 7 jdbc connection pool
>>>> is
>>>>> used independently? I tried specifying the jmx vm options
>>>>> (-Dcom.sun.management.jmxremote
>>>>> -Dcom.sun.management.jmxremote.port=1617
>>>>> -Dcom.sun.management.jmxremote.authenticate=false
>>>>> -Dcom.sun.management.jmxremote.ssl=false)
>>>>> but they don’t seem to help out? Neither did I found a way to print or
>>>>> access the pool statistics programmatically. Any suggestions?
>>>>
>>>> I don't know the answer offhand, but I assume that an examination of the
>>>> source code would lead to an understanding of how Tomcat handles this.
>>>>
>>>> I had a look at the source code. The ConnectionPool class includes a
>> check
>>> where a call is made to create an MBean but I couldn't see any calls the
>>> register the MBean with the MBeanServer. Hence thought of posting a
>>> question. Any suggestions on the statistics part. There are no methods
>>> in org.apache.tomcat.jdbc.pool.DataSource for statistics.
>>
>> What statistics do you want?
>>
>> The Interceptor mechanism can be used to create bespoke statistics.
>>
>> The statistics to know the current pool size, current borrowed
> connections, current available connections, avg connection wait time etc.
Some of those are available on the ConnectionPoolMBean.
http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/jmx/ConnectionPoolMBean.java
Some of those are available in existing Interceptors, have you looked at
those?
http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool/src/main/java/org/apache/tomcat/jdbc/pool/interceptor/
If you are programmatically registering the pool, can you not just
register it with the MBean server yourself?
p
--
[key:62590808]
Re: Using Tomcat7 JDBC Connection Pool
Posted by amit shah <am...@gmail.com>.
Responses below.
Thanks.
On Wed, Feb 8, 2012 at 7:14 PM, Pid <pi...@pidster.com> wrote:
> On 08/02/2012 12:30, amit shah wrote:
> > Thanks for the reply. Responses below.
> >
> > On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
> >
> >> On 08/02/2012 11:41, amit shah wrote:
> >>> I am trying to use the tomcat 7 jdbc connection pool in our application
> >> by
> >>> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic
> connection
> >>> pool works fine. I have few questions/clarifications
> >>>
> >>>
> >>>
> >>> 1. Executing multiple statements on connection initialization
> >>>
> >>> - The pool provides a flexibility to execute a single sql
> >> query
> >>> when the physical connection is established (initSQL property). I
> >> couldn't
> >>> find a way to execute multiple sql queries on connection
> initialization.
> >>> The JDBC Interceptor mechanism also doesn't seem to help out. Any
> >>> suggestions?
> >>
> >> Why do you want to execute multiple SQL statements for each connection
> >> in the pool? Normally you want to do the absolute minimum to validate
> >> the connection.
> >
> > These sql statements are not for validating the connection. We use Oracle
> > as our database server. So I wanted to execute the NLS (National
> Language
> > Setting) queries after the connection is established.
>
> You need to do this because it's multi-tenant (per below) and each
> tenant may require different settings?
>
>
Yes you are right. Each tenant could have different language settings. Even
if the settings are same for all the tenants, the queries are to be
executed on every physical connection creation. These settings cannot be
set when the schema's are created. They are to be set per session level.
I understand that one way to implement this would be to embed the queries
in a stored procedure but I was just trying to understand if there was a
simpler way of achieving this through configuration. Let me know if there
is a way out.
>
> >>> 2. alternateUserNameAllowed property
> >>>
> >>> - If a connection is requested with the credentials user1/password1 and
> >> the
> >>> connection was previously connected using user2/password2, the
> connection
> >>> will be closed, and reopened with the requested credentials. This
> >> property
> >>> was added as an enhancement to bug
> >>> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
> >>> I didn’t understand the reason behind closing the previous connection.
> >> Can
> >>> the pool not still maintain the previous connection and open a new
> >>> connection if the user/password combination do not match?. This way the
> >>> same pool can be used for multiple schemas.
> >>
> >> The old connection is closed so that the current user (who has different
> >> credentials) can't then use that connection.
> >>
> >> If you want to use the old connection, don't pass in new credentials.
> >>
> >> Note: this is a pool of connections, not a single connection.
> >
> > Can the pool still not close the old connection and maintain a map of
> > username/password vs connection. So that the same pool can be used for
> > multiple schemas on an Oracle server. This would help out in implementing
> > multi-tenant applications where not all environments are active at the
> same
> > time. So the same pool can be used for multiple environments. The
> > application can still provides the ability the create a specific pool for
> > individual environments. Let me know if anything is unclear.
>
> 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.
> If the pool kept all of the connections open with different credentials
> how can you guarantee availability/performance/SLA for each tenant?
>
All the connections can still follow the same configuration rules of
timeout.
>
> What is the advantage of a single pool in this case?
>
The benefit we gain is not having many pools (reduces the pool mgmt
overhead on the application server) which means less number of application
server and database server resources. 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.
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>
.
> You are asking the wrong question IMHO.
>
>
> >>> 3. JMX & Statistics
> >>>
> >>> - How can one enable jmx when tomcat 7 jdbc connection pool
> >> is
> >>> used independently? I tried specifying the jmx vm options
> >>> (-Dcom.sun.management.jmxremote
> >>> -Dcom.sun.management.jmxremote.port=1617
> >>> -Dcom.sun.management.jmxremote.authenticate=false
> >>> -Dcom.sun.management.jmxremote.ssl=false)
> >>> but they don’t seem to help out? Neither did I found a way to print or
> >>> access the pool statistics programmatically. Any suggestions?
> >>
> >> I don't know the answer offhand, but I assume that an examination of the
> >> source code would lead to an understanding of how Tomcat handles this.
> >>
> >> I had a look at the source code. The ConnectionPool class includes a
> check
> > where a call is made to create an MBean but I couldn't see any calls the
> > register the MBean with the MBeanServer. Hence thought of posting a
> > question. Any suggestions on the statistics part. There are no methods
> > in org.apache.tomcat.jdbc.pool.DataSource for statistics.
>
> What statistics do you want?
>
> The Interceptor mechanism can be used to create bespoke statistics.
>
> The statistics to know the current pool size, current borrowed
connections, current available connections, avg connection wait time etc.
>
> >> [key:62590808]
> >>
> >>
> >
>
>
> --
>
> [key:62590808]
>
>
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
On 08/02/2012 12:30, amit shah wrote:
> Thanks for the reply. Responses below.
>
> On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
>
>> On 08/02/2012 11:41, amit shah wrote:
>>> I am trying to use the tomcat 7 jdbc connection pool in our application
>> by
>>> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic connection
>>> pool works fine. I have few questions/clarifications
>>>
>>>
>>>
>>> 1. Executing multiple statements on connection initialization
>>>
>>> - The pool provides a flexibility to execute a single sql
>> query
>>> when the physical connection is established (initSQL property). I
>> couldn't
>>> find a way to execute multiple sql queries on connection initialization.
>>> The JDBC Interceptor mechanism also doesn't seem to help out. Any
>>> suggestions?
>>
>> Why do you want to execute multiple SQL statements for each connection
>> in the pool? Normally you want to do the absolute minimum to validate
>> the connection.
>
> These sql statements are not for validating the connection. We use Oracle
> as our database server. So I wanted to execute the NLS (National Language
> Setting) queries after the connection is established.
You need to do this because it's multi-tenant (per below) and each
tenant may require different settings?
>>> 2. alternateUserNameAllowed property
>>>
>>> - If a connection is requested with the credentials user1/password1 and
>> the
>>> connection was previously connected using user2/password2, the connection
>>> will be closed, and reopened with the requested credentials. This
>> property
>>> was added as an enhancement to bug
>>> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
>>> I didn’t understand the reason behind closing the previous connection.
>> Can
>>> the pool not still maintain the previous connection and open a new
>>> connection if the user/password combination do not match?. This way the
>>> same pool can be used for multiple schemas.
>>
>> The old connection is closed so that the current user (who has different
>> credentials) can't then use that connection.
>>
>> If you want to use the old connection, don't pass in new credentials.
>>
>> Note: this is a pool of connections, not a single connection.
>
> Can the pool still not close the old connection and maintain a map of
> username/password vs connection. So that the same pool can be used for
> multiple schemas on an Oracle server. This would help out in implementing
> multi-tenant applications where not all environments are active at the same
> time. So the same pool can be used for multiple environments. The
> application can still provides the ability the create a specific pool for
> individual environments. Let me know if anything is unclear.
The pool returns members at random, so how would you know which cached
credentials you were getting?
If the pool kept all of the connections open with different credentials
how can you guarantee availability/performance/SLA for each tenant?
What is the advantage of a single pool in this case?
You are asking the wrong question IMHO.
>>> 3. JMX & Statistics
>>>
>>> - How can one enable jmx when tomcat 7 jdbc connection pool
>> is
>>> used independently? I tried specifying the jmx vm options
>>> (-Dcom.sun.management.jmxremote
>>> -Dcom.sun.management.jmxremote.port=1617
>>> -Dcom.sun.management.jmxremote.authenticate=false
>>> -Dcom.sun.management.jmxremote.ssl=false)
>>> but they don’t seem to help out? Neither did I found a way to print or
>>> access the pool statistics programmatically. Any suggestions?
>>
>> I don't know the answer offhand, but I assume that an examination of the
>> source code would lead to an understanding of how Tomcat handles this.
>>
>> I had a look at the source code. The ConnectionPool class includes a check
> where a call is made to create an MBean but I couldn't see any calls the
> register the MBean with the MBeanServer. Hence thought of posting a
> question. Any suggestions on the statistics part. There are no methods
> in org.apache.tomcat.jdbc.pool.DataSource for statistics.
What statistics do you want?
The Interceptor mechanism can be used to create bespoke statistics.
>> [key:62590808]
>>
>>
>
--
[key:62590808]
Re: Using Tomcat7 JDBC Connection Pool
Posted by amit shah <am...@gmail.com>.
Thanks for the reply. Responses below.
On Wed, Feb 8, 2012 at 5:19 PM, Pid <pi...@pidster.com> wrote:
> On 08/02/2012 11:41, amit shah wrote:
> > I am trying to use the tomcat 7 jdbc connection pool in our application
> by
> > using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic connection
> > pool works fine. I have few questions/clarifications
> >
> >
> >
> > 1. Executing multiple statements on connection initialization
> >
> > - The pool provides a flexibility to execute a single sql
> query
> > when the physical connection is established (initSQL property). I
> couldn't
> > find a way to execute multiple sql queries on connection initialization.
> > The JDBC Interceptor mechanism also doesn't seem to help out. Any
> > suggestions?
>
> Why do you want to execute multiple SQL statements for each connection
> in the pool? Normally you want to do the absolute minimum to validate
> the connection.
>
These sql statements are not for validating the connection. We use Oracle
as our database server. So I wanted to execute the NLS (National Language
Setting) queries after the connection is established.
> > 2. alternateUserNameAllowed property
> >
> > - If a connection is requested with the credentials user1/password1 and
> the
> > connection was previously connected using user2/password2, the connection
> > will be closed, and reopened with the requested credentials. This
> property
> > was added as an enhancement to bug
> > 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
> > I didn’t understand the reason behind closing the previous connection.
> Can
> > the pool not still maintain the previous connection and open a new
> > connection if the user/password combination do not match?. This way the
> > same pool can be used for multiple schemas.
>
> The old connection is closed so that the current user (who has different
> credentials) can't then use that connection.
>
> If you want to use the old connection, don't pass in new credentials.
>
> Note: this is a pool of connections, not a single connection.
>
Can the pool still not close the old connection and maintain a map of
username/password vs connection. So that the same pool can be used for
multiple schemas on an Oracle server. This would help out in implementing
multi-tenant applications where not all environments are active at the same
time. So the same pool can be used for multiple environments. The
application can still provides the ability the create a specific pool for
individual environments. Let me know if anything is unclear.
>
>
> > 3. JMX & Statistics
> >
> > - How can one enable jmx when tomcat 7 jdbc connection pool
> is
> > used independently? I tried specifying the jmx vm options
> > (-Dcom.sun.management.jmxremote
> > -Dcom.sun.management.jmxremote.port=1617
> > -Dcom.sun.management.jmxremote.authenticate=false
> > -Dcom.sun.management.jmxremote.ssl=false)
> > but they don’t seem to help out? Neither did I found a way to print or
> > access the pool statistics programmatically. Any suggestions?
>
> I don't know the answer offhand, but I assume that an examination of the
> source code would lead to an understanding of how Tomcat handles this.
>
> I had a look at the source code. The ConnectionPool class includes a check
where a call is made to create an MBean but I couldn't see any calls the
register the MBean with the MBeanServer. Hence thought of posting a
question. Any suggestions on the statistics part. There are no methods
in org.apache.tomcat.jdbc.pool.DataSource for statistics.
>
> p
>
>
> --
>
> [key:62590808]
>
>
Re: Using Tomcat7 JDBC Connection Pool
Posted by Pid <pi...@pidster.com>.
On 08/02/2012 11:41, amit shah wrote:
> I am trying to use the tomcat 7 jdbc connection pool in our application by
> using the tomcat-jdbc.jar and tomcat-juli.jar files. The basic connection
> pool works fine. I have few questions/clarifications
>
>
>
> 1. Executing multiple statements on connection initialization
>
> - The pool provides a flexibility to execute a single sql query
> when the physical connection is established (initSQL property). I couldn't
> find a way to execute multiple sql queries on connection initialization.
> The JDBC Interceptor mechanism also doesn't seem to help out. Any
> suggestions?
Why do you want to execute multiple SQL statements for each connection
in the pool? Normally you want to do the absolute minimum to validate
the connection.
> 2. alternateUserNameAllowed property
>
> - If a connection is requested with the credentials user1/password1 and the
> connection was previously connected using user2/password2, the connection
> will be closed, and reopened with the requested credentials. This property
> was added as an enhancement to bug
> 50025<https://issues.apache.org/bugzilla/show_bug.cgi?id=50025>.
> I didn’t understand the reason behind closing the previous connection. Can
> the pool not still maintain the previous connection and open a new
> connection if the user/password combination do not match?. This way the
> same pool can be used for multiple schemas.
The old connection is closed so that the current user (who has different
credentials) can't then use that connection.
If you want to use the old connection, don't pass in new credentials.
Note: this is a pool of connections, not a single connection.
> 3. JMX & Statistics
>
> - How can one enable jmx when tomcat 7 jdbc connection pool is
> used independently? I tried specifying the jmx vm options
> (-Dcom.sun.management.jmxremote
> -Dcom.sun.management.jmxremote.port=1617
> -Dcom.sun.management.jmxremote.authenticate=false
> -Dcom.sun.management.jmxremote.ssl=false)
> but they don’t seem to help out? Neither did I found a way to print or
> access the pool statistics programmatically. Any suggestions?
I don't know the answer offhand, but I assume that an examination of the
source code would lead to an understanding of how Tomcat handles this.
p
--
[key:62590808]