[sakai2-tcc] Improving Database Connection Support

[John Bush]

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