[Building Sakai] Connection pooling

Wed Oct 12 08:43:27 PDT 2011

We use very similar JVM parameters.  Right now I'm more interested in
what Rutgers is doing with DBCP, and what others are doing?  Are you
mainly leaving things default?  I know a lot of institutions at some
point switched to C3PO, and I've seen talk about trying to upgrade DBCP.

>From the Rutgers doc linked earlier:

Mysql jdbc connection

Please note: we are carrying over this configuration from Sakai 2.4. The
underlying bug in DBCP has been fixed, but we don't think the fixed
version has made it to Sakai as of Sakai 2.7.

With the default parameters, there is a problem talking to Mysql: The
connection pools code that Sakai uses does additional database
operations for each query: It checks that the connection is valid and
then resets any connection parameters that do not have their default
value. Each of these operations generates a database query. What's
worse, those queries are done under a global lock, so only one
connection can proceed at a time. With a quad-processor system, you lose
much of the benefit from 3 of your processors, and generates lots of
extra database queries.

I recommend the following parameters in sakai.properties. This is as of
Sakai 2.7, for Mysql 5.1. For Mysql 4.x you would presumably use
MySQLInnoDBDialect.

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

vendor at org.sakaiproject.db.api.SqlService=mysql

driverClassName at javax.sql.BaseDataSource=com.mysql.jdbc.Driver

url at javax.sql.BaseDataSource=jdbc:mysql://xxxx:3306/sakai?useUnicode=tru
e&characterEncoding=UTF-8&useServerPrepStmts=false&cachePrepStmts=true&p
repStmtCacheSize=4096&prepStmtCacheSqlLimit=4096

username at javax.sql.BaseDataSource=xxx

password at javax.sql.BaseDataSource=xxx

testOnBorrow at javax.sql.BaseDataSource=false

validationQuery at javax.sql.BaseDataSource=

defaultTransactionIsolationString at javax.sql.BaseDataSource=TRANSACTION_R
EAD_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

Jeremy Kusnetz | Sr. Systems Engineer

American Public University System 
American Military University  |  American Public University
661 S George Street, Charles Town, WV 25414 
T 304-885-5333 | M 703-967-5212 |  jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu> 

From: Earle Nietzel [mailto:earle.nietzel at gmail.com] 
Sent: Wednesday, October 12, 2011 11:41 AM
To: Kusnetz, Jeremy
Cc: sakai-dev
Subject: Re: [Building Sakai] Connection pooling

I would say that is good starting point but there any many more
parameters that are not mentioned to help tune the JVM.

I found the following parameters made the most difference (which in the
link are not mentioned):

-XX:+CMSIncrementalMode - Incremental mode makes a huge difference

-XX:+CMSIncrementalPacing - sets the duty cycle for Incremental mode

-XX:+CMSParallelRemarkEnabled - self explanatory

-XX:+UseCMSInitiatingOccupancyOnly - CMS is based solely on % occupancy

-XX:CMSInitiatingOccupancyFraction=70 - % when to start CMS

-XX:CMSIncrementalSafetyFactor=75 - this is the most IMPORTANT option to
configure using CMS Incremental you should spend time tuning this number
and it works better than setting CMSIncrementalDutyCycle. Basically it
adjusts the jvm stats adding in a safety factor.

-XX:+UseCMSCompactAtFullCollection - Helps defragment the heap which can
be a problem

With these settings we almost never have a FULL GC occur and everything
runs rather smoothly. The only hurdle to overcome is that of heap
fragmentation which the new G1 collector has promised to solve. I have
done some initial testing with G1 in Java 6 and never got it to run as
good as CMS Incremental.

Just keep in mind that every running JVM is different (different tools,
different user populace, different hardware, etc.) and tuning the JVM to
that environment is an absolute MUST in order for it to be performant. 

Here is all the options we run currently on a 64bit 8GB ESX VM:

JAVA_OPTS="-Dsun.lang.ClassLoader.allowArraySyntax=true
-Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false
-Dhttp.agent=Sakai -server -d64 -Djava.awt.headless=true
-Djava.library.path=/usr/lib -Dsun.net.inetaddr.ttl=60 -Xms5g -Xmx5g
-XX:NewSize=768m -XX:MaxNewSize=768m -XX:PermSize=512m
-XX:MaxPermSize=512m -XX:+UseConcMarkSweepGC -XX:+CMSIncrementalMode
-XX:+CMSIncrementalPacing -XX:+CMSParallelRemarkEnabled
-XX:+CMSConcurrentMTEnabled -XX:ParallelGCThreads=5 -XX:+UseParNewGC
-XX:+UseCMSInitiatingOccupancyOnly -XX:CMSInitiatingOccupancyFraction=70
-XX:CMSIncrementalSafetyFactor=75 -XX:CMSMaxAbortablePrecleanTime=60000
-XX:+UseCMSCompactAtFullCollection -XX:SurvivorRatio=6
-XX:+DisableExplicitGC"

I hope this helps,

Earle

On Wed, Oct 12, 2011 at 11:04 AM, Kusnetz, Jeremy <JKusnetz at apus.edu>
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.

https://confluence.sakaiproject.org/display/PROD/Rutgers+performance+tun
ing+experience

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

Jeremy Kusnetz | Sr. Systems Engineer

American Public University System 
American Military University  |  American Public University
661 S George Street, Charles Town, WV 25414 
T 304-885-5333 | M 703-967-5212 |  jkusnetz at apus.edu| www.apus.edu
<http://www.apus.edu> 

This message is private and confidential. If you have received it in
error, please notify the sender and remove it from your system.

_______________________________________________
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"

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111012/7efd8b14/attachment.html 