[sakai2-tcc] Improving Database Connection Support

Thu Mar 14 12:55:43 PDT 2013

There is a kernel ticket for trying to do this (allow different types
of connection pools) which seems like a good place to capture whatever
ideas come out of this conversation.

https://jira.sakaiproject.org/browse/KNL-1000

-AZ

On Thu, Mar 14, 2013 at 3:25 PM, Noah Botimer <botimer at umich.edu> wrote:
> 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
>
>
>
> _______________________________________________
> sakai2-tcc mailing list
> sakai2-tcc at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
>

-- 
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile