[Building Sakai] Severe DB Performance Issues for 2.9

[Noah Botimer]

Quick workaround...

If you have heap headroom, go 100x on the bottom two and 10x on the top one.

That doesn't excuse the excess operations, but at least there is a cache in a place that will help.

Thanks,
-Noah

On Sep 4, 2013, at 12:24 PM, Sobieralski, Damian Michael wrote:

> org.sakaiproject.site.impl.SiteCacheImpl.cache: count:10000 hits:39267479 misses:3107148 hit%:92
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY: count:10000 hits:138119 misses:26560856 hit%:0
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PROPERTY: count:10000 hits:382901 misses:1660627 hit%:18
>  
> From: Noah Botimer [mailto:botimer at umich.edu] 
> Sent: Wednesday, September 04, 2013 12:14 PM
> To: Sobieralski, Damian Michael
> Cc: sakai-dev at collab.sakaiproject.org
> Subject: Re: [Building Sakai] Severe DB Performance Issues for 2.9
>  
> Pull these from the top section of the status page:
>  
> org.sakaiproject.site.impl.SiteCacheImpl.cache
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PROPERTY
>  
> Thanks,
> -Noah
>  
> On Sep 4, 2013, at 12:06 PM, Sobieralski, Damian Michael wrote:
> 
> 
> Is this the right stat?
> 
> HardCache: count:10000 hits:42596 misses:11992 hit%:78
> 
> ---
> Sam Ottenhoff ottenhoff at longsight.com
> Wed Sep 4 08:56:16 PDT 2013
> 
> If you're not running 2.9.2+ web tool, then SAK-23841 is not relevant.
> 
> Can you share your cache hit/missed stats from the Admin Workspace ->
> Memory tool?
> 
> -----Original Message-----
> From: Sobieralski, Damian Michael 
> Sent: Wednesday, September 04, 2013 11:36 AM
> To: sakai-dev at collab.sakaiproject.org
> Subject: Severe DB Performance Issues for 2.9
> 
> IU is into the 2nd week of classes and yesterday we saw heavy CPU load on database which then backed up the app servers.   Our users experienced System latency which then resulted in a lot of errors and very frustrated users.     As traffic slowed, the system recovered.   
> 
> We're running a base 2.9.0 with cherry picked enhancements and performance fixes.  We did apply KNL-1011.  
> 
> What we are noticing is under average to high load the DB is running at 100%.  We are noticing a LOT of calls such as:
> 
> SELECT TOOL_ID,  
>   REGISTRATION,  
>   TITLE,  
>   LAYOUT_HINTS,  
>   PAGE_ORDER  
> FROM SAKAI_SITE_TOOL  
> WHERE PAGE_ID = :1  
> ORDER BY PAGE_ORDER ASC
> 
> And
> 
> SELECT NAME,  
>   VALUE  
> FROM SAKAI_SITE_PAGE_PROPERTY  
> WHERE ( PAGE_ID = :1 )
> 
> Pre 2.9 upgrade we only had one query that ran over a million times. Both of these had over 140 million executions.
> 
> Any ideas what might be causing this behavior?  And, more importantly, what can be done to reduce this? This is a SEVERE problem for us as yesterday our system basically grinded to a halt w/ the DB locked at 100%.
> 
> We did find this: https://jira.sakaiproject.org/browse/SAK-23841 
> 
> But as far as I know we're not using XFrame.  But could this still be the issue?
> 
> Thanks.
> 
> ---
> Damian Sobieralski
> Indiana University
> 
> _______________________________________________
> 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"
>  

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/dadd9c47/attachment.html