One more comment below about oracle UCP. On Thu, Feb 9, 2012 at 5:10 PM, amit shah <amits...@gmail.com> wrote:
> 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? >> >> 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] >> >> >
