[Building Sakai] Connection pooling

[Charles Hedrick]

Our advice is probably largely out of date. We were dealing with two issues:

* bugs in dbcp (or commons pool). I believe we're now using a version that has fixed those bugs. This caused us to disable the validation query and I believe also explains the minidle
* a connection leak. I believe that's now fixed. This caused the 300 connection number.

Here's what we use:

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
vendor at org.sakaiproject.db.api.SqlService=mysql
driverClassName at javax.sql.BaseDataSource=com.mysql.jdbc.Driver
testOnBorrow at javax.sql.BaseDataSource=false
validationQuery at javax.sql.BaseDataSource=
#validationQuery at javax.sql.BaseDataSource=select 1 from DUAL                                                                          
defaultTransactionIsolationString at javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
initialSize at javax.sql.BaseDataSource=300
maxActive at javax.sql.BaseDataSource=300
maxIdle at javax.sql.BaseDataSource=300
minIdle at javax.sql.BaseDataSource=0
url at javax.sql.BaseDataSource=jdbc:mysql://:3306/sakai?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096&useLocalSessionState=true                            
username at javax.sql.BaseDataSource=sakaiuser
password at javax.sql.BaseDataSource=xxx

However something closer to the default may well be fine. Indeed I'm thinking of reducing the number of connections. There's some indication that too many connections uses up CPU time unnecessarily.

I believe our URL is still right. You want the caching and localSessionState.

We haven't seen dbcp-related problems for over a year, and maybe longer.


On Oct 12, 2011, at 1:09 PM, Seth Theriault wrote:

> Kusnetz, Jeremy wrote:
> 
>> I know a lot of institutions at some point switched to C3PO, 
>> and I've seen talk about trying to upgrade DBCP.
> 
> A few years ago, there was an extended debate on the merits of 
> C3P0 v. DBCP.
> 
> As far as I can tell, there are some institutions that switched 
> over, but I think that number is small as DBCP was maintained in 
> the community code.
> 
> Seth
> 
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
> 
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"