tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From amit shah <amits...@gmail.com>
Subject Re: Using Tomcat7 JDBC Connection Pool
Date Thu, 09 Feb 2012 12:56:35 GMT
One more comment below about oracle UCP.

On Thu, Feb 9, 2012 at 5:10 PM, amit shah <amits.84@gmail.com> wrote:

> Comments below.
>
> On Wed, Feb 8, 2012 at 9:19 PM, Pid <pid@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 <pid@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 <pid@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]
>>
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message