Comment below
On 09-Feb-2012, at 10:18 PM, Pid <p...@pidster.com> wrote: > On 09/02/2012 16:21, Amit wrote: >> Any thoughts on the first point about executing multiple SQL queries on >> physical connection creation? > > I have no idea if it'll work, but I'd try: > > SELECT 1; SELECT 1; > > If you are controlling the pool (and you are) by passing in > username/password parameters each time, then you could do it as an extra > transaction thereafter. > > Executing the queries after retrieving the connection would not be the right option since they would execute every time the connection is borrowed instead of executing it only on physical connection creation. Can the jdbc interceptor architecture be extended to provide a method which is called when the physical connection is created? ( similar to disconnect()) > p > >> On 09-Feb-2012, at 7:05 PM, Pid <p...@pidster.com> wrote: >> >>> On 09/02/2012 12:56, amit shah wrote: >>>> One more comment below about oracle UCP. >>> >>> <snip> >>> >>>>>>>> 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. >>> >>> So why not create multiple controlled pools & not run into availability >>> problems? >>> >>> >>> <snip> >>> >>>>>> What overhead? >>>>>> >>>>> >>>>> The application server and database server resources (memory, cpu etc) for >>>>> keeping the connections open? >>> >>> That's a total connection count dependent metric. >>> >>> So the overhead is virtually the same regardless of whether you have 5 >>> pools or 1, if you have the same total number of connections. >>> >>> >>>>>> 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. >>> >>> That should be the default IMO. You're asking for trouble otherwise. >>> >>> >>>>>>> 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. >>> >>> ! >>> >>> <snip> >>> >>>>>> 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. >>> >>> Cool. >>> >>> >>> p >>> >>> >>> >>> >>> -- >>> >>> [key:62590808] >>> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org >> For additional commands, e-mail: users-h...@tomcat.apache.org >> > > > -- > > [key:62590808] > --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
