[sakai2-tcc] Improving Database Connection Support

[Noah Botimer]

A few quick details:

As far as the Tomcat pool, I just mean the pool implementation instead of DBCP, not necessarily the container XML configs, JNDI or anything environmental in particular. The important bit is that this should just be the default that can be configured roughly as we have (specify a driver, connection string, user/password and get on with it) but be swappable.

http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

I don't much care what the final configuration mechanism is, but being able to set up and point to a DataSource rather than just supplying strings to our instantiating code is the idea. Somewhere down the line, the DataSource is injected on to SqlService and Hibernate, so yes, the Springier the better, I'd say. If you want to use a different pool, that should amount to some custom instantiation and wiring it in, not modifying db-components.xml and rebuilding the kernel.

As far as metrics, I mean some very light investment -- stuff like average connections active each minute, queries/transactions per request and minute, logging URL/session ID if a request goes over 100 queries or 20 seconds (these would help passively identify candidates for async/queued operations, nasty N+1 errors, etc.).

Any locally tuned failover strategy should be something managed by a custom DataSource, IMO. Provided you can plug in your own DataSource, this would (rightly) be transparent to the application. Same goes if you like container config, JNDI, or other: set it up and wire it in as a DataSource bean.

Thanks,
-Noah

On Mar 14, 2013, at 1:25 AM, John Bush wrote:

> I actually think going back to the standard J2EE way using jndi and
> configuring stuff in a container specific way would be a step
> backwards, furthermore it would complicate configuration which I think
> goes against one of the other items you brought up (too many ways of
> doing configuration).
> 
> Since we are configuring our datasource (and every other service) via
> Spring, I think we need a Spring way of doing this, maybe using
> HotSwappableTargetSource would work, seems like there are some example
> of people using it for exactly this purpose:
> http://book.javanb.com/Professional-Java-Development-with-the-Spring-Framework/BBL0039.html
> http://affy.blogspot.com/2007/11/dynamic-datasource-via-spring-using.html
> 
> For metrics and root cause detection, I'm not sure that is really
> stuff we want to build into the app, have you looked at appdynamics or
> something like that.  Its a bit expensive but even the free version is
> useful, you only get to keep 2 hours of data with it, but when all
> hell breaks loose 2 hours is good enough. You can very easily see
> exactly what caused your problem, for root cause detection in
> production, there is no better java tool on the market, I even use it
> instead of yourkit at this point in dev/qa.
> 
> The idea of managing (killing/restating) the connections at runtime
> scare me a bit, I'm not going to even touch that one.
> 
> 
> 
> On Wed, Mar 13, 2013 at 9:42 PM, David Adams <da1 at vt.edu> wrote:
>> David Adams wrote:
>>> 5. Lack of support for database failover except through driver indirection.
>> 
>> Okay, so this is an issue close to my heart, and is probably an easier
>> fix than introducing an entirely new component to the architecture.
>> 
>> Here's the scenario: We have two Oracle database servers set up in an
>> active-passive failover structure. The config works great, and we can
>> failover in a few seconds. However, Sakai doesn't cooperate, and you
>> have to reconfigure and restart the app servers to regain
>> connectivity. Luckily, Oracle's full JDBC driver supports the
>> failover completely transparently, right? Oh, except Sakai doesn't do
>> JDBC the standard way, so no dice.
>> 
>> I've been toying around inside the SakaiBasicDataSource class and the
>> various other hacks around commons-pool on and off for the past year
>> trying to find a safe workaround to allow me to either watch for
>> busted connections and restart them on the standby db host, or just to
>> force a complete rebuild of the database connection pool. This latter
>> is what I've settled on by providing a resetDataSource method in my
>> extension class that I can call through our custom REST-RPC servlet,
>> which just trashes the connection pool and updates the bean properties
>> so that the next call to createDataSource from the app will regenerate
>> the pool with the new settings.
>> 
>> I've yet to move my hacky code out to production, and honestly I'd rather
>> not have to resort to such a messy solution.
>> 
>> Here's what Noah proposed:
>> 
>> Noah Botimer wrote:
>>> First step: undo our weird binding to a very old DBCP and allow a real
>>> DataSource to be plugged in -- whatever pool or config you need.
>> 
>>> Second step: pick the Tomcat pool by default and document how to switch
>>> if you need something else (hint: should be very standard and basically
>>> link to JDK/Tomcat docs).
>> 
>> So are you talking about, eg, setting up a Resource in server.xml, and
>> binding to that, or something else? I admit I'm unfamiliar with Tomcat
>> SOPs as I've been so immersed in Sakai for so long that "correct" Tomcat
>> usage is often foreign to me.
>> 
>>> Third step: add some better connection/query/transaction/request metrics
>>> that work OOTB so implementors can better see what's going on and decide
>>> what to do about it.
>> 
>> This is very important. Ideally, we need to be able to peek into the
>> status of connections, find out what those hung active connections are
>> up to, and selectively kill/restart/tweak them on the fly.
>> 
>> I'm definitely on board with this project if there's someone to
>> partner up with who understands exactly how Sakai has hijacked DBCP
>> and where all the tendrils lead. Obviously, my situation is just one
>> scenario, but it seems like any legal JDBC connection ought to be
>> valid for Sakai. If we can configure it in the "standard" way as well
>> that's another win.
>> 
>> Anyone have any other thoughts?
>> 
>> -dave
>> _______________________________________________
>> sakai2-tcc mailing list
>> sakai2-tcc at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
> 
> 
> 
> --
> John Bush
> 602-490-0470
> _______________________________________________
> sakai2-tcc mailing list
> sakai2-tcc at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130314/e6e9ac99/attachment-0001.html