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

[R.P. Aditya]

On 2009-12-17, 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.

so what does the test do and what are the stats of the test with and
without 3cp0 -- db response time, hits per second, how many connections
used in Sakai versus Active/idle connections in Oracle?

we give each of our 5 appservers 50 connections to the db and at peak
DBCP reports only about 8 in use per appserver (and Oracle much less
since it doesn't count the time used sending the data back to the client
as "ACTIVE"/on-cpu time) -- furthermore we see db cpu entirely
proportional to activity so whatever overhead DBCP has is "fixed"

Adi

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