On 08/02/2012 14:59, amit shah wrote: > Responses below. > > Thanks. > > On Wed, Feb 8, 2012 at 7:14 PM, Pid <p...@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 <p...@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]
signature.asc
Description: OpenPGP digital signature
