On 09/02/2012 17:24, Amit wrote: > 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.
I was assuming that, as you controlled the pool, you'd be able to figure out when you run the extra commands. > Can the jdbc interceptor architecture be extended to provide a method which > is called when the physical connection is created? ( similar to disconnect()) Interceptors can do a bunch of things. What have you tried/looked at so far? p >> 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 > -- [key:62590808]
signature.asc
Description: OpenPGP digital signature
