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

[John Bush]

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"