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