[Building Sakai] Connection pooling

[Earle Nietzel]

Understood I have not seen any issues with DBCP as of yet. (We may not have
the load for it)

But here is our MySQL options:

vendor at org.sakaiproject.db.api.SqlService=mysql
driverClassName at javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
validationQuery at javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString at javax.sql.BaseDataSource
=TRANSACTION_READ_COMMITTED
url at javax.sql.BaseDataSource
=jdbc:mysql://xxx:3306/xxx?useUnicode=true&characterEncoding=UTF-8

Earle

On Wed, Oct 12, 2011 at 11:43 AM, Kusnetz, Jeremy <JKusnetz at apus.edu> wrote:

> 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=true&characterEncoding=UTF-8&useServerPrepStmts=false&cachePrepStmts=true&prepStmtCacheSize=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_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****
>
> ** **
>
> ** **
>
> 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****
>
> ** **
>
> *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+tuning+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****
>
>  ****
>
> 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/74ed671b/attachment.html