[Building Sakai] How does Sakai respond to an overloaded database?

[John Bush]

Here's how we did it, drop sakai-configuration.xml into sakai.home  
folder.  Copy the 3cp0 jar into tomcat/shared/lib.  I also had to  
patch the default.sakai.properties which has bean ids that reference  
dbcp and were destroying Spring at startup, see attached patch.   Then  
set the following in sakai.properties, and get rid of any dbcp  
settings.  Alternatively, you can modify the db components.xml instead  
of using sakai-configuration.xml, but for us this way is more  
maintainable.

user at javax.sql.BaseDataSource=sakai
password at javax.sql.BaseDataSource=sakaipassword
driverClass at javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
jdbcUrl at javax.sql.BaseDataSource=jdbc:oracle:thin:@oracleserver: 
1521:sakai
preferredTestQuery at javax.sql.BaseDataSource=select 1 from DUAL
initialPoolSize at javax.sql.BaseDataSource=100
minPoolSize at javax.sql.BaseDataSource=100
maxPoolSize at javax.sql.BaseDataSource=100


-------------- next part --------------
A non-text attachment was scrubbed...
Name: 3cp0.patch
Type: application/octet-stream
Size: 1654 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091217/a3117280/attachment.obj 
-------------- next part --------------


-------------- next part --------------
A non-text attachment was scrubbed...
Name: sakai-configuration.xml
Type: application/xml
Size: 2963 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091217/a3117280/attachment.rdf 
-------------- next part --------------




On Dec 17, 2009, at 3:28 AM, Joshua Swink wrote:

> Thanks, John, I'd like to see how you configure Sakai to use 3cp0. We
> put more ram and cpus in the database server and things have been
> better, but that will only last so long.
>
> Josh
>
> On Thu, Dec 17, 2009 at 12:56 AM, John Bush <john.bush at rsmart.com>  
> wrote:
>> Well, we've been finishing up some load testing over the last few  
>> weeks and
>> our traction with oracle has been very different than mysql.   
>> Here's what
>> I'd recommend.
>>
>> #1 switch to using 3cp0
>> #2 give yourself gobs of connections per app server, like at least  
>> 100
>>
>> We saw a dramatic drop in database load and solved connection  
>> exhausted
>> problems by switching from dbcp to 3cp0.  I attribute this to c3p0's
>> statement pooling, but am not certain.  I believe there is a way to  
>> enable
>> that with dbcp, but I don't think thats setup by default, and we  
>> haven't
>> tried it out.   For MySQL we haven't needed this, and I think  
>> because the
>> MySQL driver supports caching prepared statements ootb, but I'm  
>> guessing.
>>  Empirically, I can tell you, test bad with dbcp, good with 3pc0,  
>> using
>> roughly the same connection properties.  The theorists can debate  
>> it all
>> they want, but I saw it help when it was the only change made.   
>> Play it
>> safe, switch to 3cp0.
>>
>> For the load testing we've done with Oracle and MySql, we've needed  
>> way more
>> connections than what I hear some people recommend.   Bump up the  
>> ulimit on
>> the database and app servers, and beef up the connections. FYI,  we  
>> are
>> running high load tests (3k concurrent users pounding for extended  
>> amounts
>> of time).
>>
>> If you want some specifics on 3cp0 tuning, let me know,  I'll share  
>> whats
>> working for us, I can also tell you how to configure it using the
>> sakai-configuration.xml vs modifying any components.xml, which is  
>> super
>> handy.  Many thanks to Ray Davis, see
>> http://confluence.sakaiproject.org/display/REL/More+Flexible+Sakai+Configuration 
>> ,
>> this is my favorite feature of 2.6.
>>
>> On Dec 9, 2009, at 1:49 PM, Joshua Swink wrote:
>>
>>> We had a near-outage recently when the database server got heavily
>>> loaded. We had an unprecedented number of users on Sakai. Sakai was
>>> behaving like this:
>>>
>>> - very slow page loads (up to 10 minutes)
>>> - system load factor very high (10-15)
>>> - database connection pool being exhausted (one tomcat did exhaust  
>>> it
>>> and needed to be restarted; the others survived)
>>>
>>> Obviously we need to add better hardware.
>>>
>>> What I want to know is whether the application's behavior can be
>>> attributed to the user load alone, or if the database's increased
>>> response time could be responsible. One hypothesis is that the
>>> application servers would have been fine if the database had been
>>> responding in a timely fashion. Another is that the usage would have
>>> led to Sakai bogging down even if the db had been fine.
>>>
>>> Any thoughts?
>>>
>>> Josh
>>> _______________________________________________
>>> 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"
>>
>>