[Building Sakai] Re Connection pooling

[David Adams]

Jeremy Kusnetz wrote:
> I'd like to get people's opinions on connection pooling paramaters.
>
> I've read what Rutgers has done, I'm not sure what their current
> settings are.
>
> We are currently using 2.6.3, which I understand has a pretty old
> version of the DBCP libraries, but looking at 2.8 it seems to be using
> the same version? commons-dbcp-1.2.2.jar

We have upgraded our DBCP to 1.4 or so, but I'm not sure how much difference it made. We looked into c3p0 but its parameters don't match up to DBCP so we never went very far with that once we discovered we couldn't patch the code cleanly.

We've struggled a lot with connection counts etc. My impression from talking to other schools is that Oracle in particular requires some more careful handling than MySQL for busy schools. After a year or so of tweaking, our current settings on our four user-facing app servers are:

driverClassName at javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
validationQuery at javax.sql.BaseDataSource=
defaultTransactionIsolationString at javax.sql.BaseDataSource=
testOnBorrow at javax.sql.BaseDataSource=false

timeBetweenEvictionRunsMillis at javax.sql.BaseDataSource=-1
removeAbandoned at javax.sql.BaseDataSource=true
removeAbandonedTimeout at javax.sql.BaseDataSource=120
logAbandoned at javax.sql.BaseDataSource=true

minIdle at javax.sql.BaseDataSource=0
maxIdle at javax.sql.BaseDataSource=75
initialSize at javax.sql.BaseDataSource=75
maxActive at javax.sql.BaseDataSource=75

I can't honestly say that I fully understand all of the implications. But the key settings are to turn off validation testing (validationQuery blank and testOnBorrow = false), and to start a certain # of connections and keep them active rather than letting things go up and down because Oracle connections are far more expensive than MySQL connections, both in terms of idle resource usage and in terms of creation/teardown expense.

We had the maxIdle, initialSize, and maxActive values set much lower in the past (20 each across six app servers), and generally we see very few (less than 10) in use at once, even during busy times. But when anything slows down on the database server, any number of connections will rapidly get chewed up as new requests keep coming in. We could throttle things earlier in the chain, but that wouldn't make our user's experience any better.

We've put a lot of time into tools to monitor the connection pools' status over time, and we find that some connections get dropped, some get marked active and never released, and so over the course of a week, we might go from 75 connections down to 50 or less, all due to this kind of entropy. What part of that is Sakai's fault and what part is Oracle's I'm not sure, although we've tracked at least some of it to certain error conditions in Sakai's code where a connection will fail but never get discarded or marked inactive.

Hopefully some of that will help. It's a big mess, and a huge topic, with lots of uncertainty and unknowns. Sakai's limited (ie non-existent) management API doesn't help, and we've had to hack a few things in the kernel just to be able to keep track of what's going on.

Good luck.
-- 
David Adams
Director, Learning Systems Integration and Support
Virginia Tech Learning Technologies