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 11:40:03 GMT
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?
>
>
> >> 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