[Building Sakai] Severe DB Performance Issues for 2.9

Anthony Whyte arwhyte at umich.edu
Wed Sep 4 10:19:35 PDT 2013 

CTools memory status attached.




anthony whyte | its and mlibrary | university of michigan | arwhyte at umich.edu | 517-980-0228


On Sep 4, 2013, at 1:17 PM, Anthony Whyte wrote:

> 
>> If you have heap headroom, go 100x on the bottom two and 10x on the top one.
> 
> 
>> 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
> 
> Noah, are you sure it's not the reverse as regards the recommendation: 100x for SiteCacheImpl.cache and 10x for the properties (looking at CTools' current memory status (excerpts):
> 
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY: count:10000 hits:408249 misses:2339654 hit%:14
> org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PROPERTY: count:10000 hits:3226252 misses:549197 hit%:85
> org.sakaiproject.site.impl.SiteCacheImpl.cache: count:45932 hits:18975495 misses:5134093 hit%:78
> 
> [ name = org.sakaiproject.site.impl.SiteCacheImpl.cache status = STATUS_ALIVE eternal = false overflowToDisk = false maxEntriesLocalHeap = 100000 maxEntriesLocalDisk = 0 memoryStoreEvictionPolicy = LRU timeToLiveSeconds = 300 timeToIdleSeconds = 300 diskPersistent = false diskExpiryThreadIntervalSeconds = 120 cacheEventListeners: net.sf.ehcache.statistics.LiveCacheStatisticsWrapper org.sakaiproject.site.impl.SiteCacheImpl hitCount = 18975495 memoryStoreHitCount = 18975495 diskStoreHitCount = 0 missCountNotFound = 5134093 missCountExpired = 513047 maxBytesLocalHeap = 0 overflowToOffHeap = false maxBytesLocalOffHeap = 0 maxBytesLocalDisk = 0 pinned = false ]
> 
> [ name = org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY status = STATUS_ALIVE eternal = false overflowToDisk = false maxEntriesLocalHeap = 10000 maxEntriesLocalDisk = 10000000 memoryStoreEvictionPolicy = LRU timeToLiveSeconds = 120 timeToIdleSeconds = 120 diskPersistent = false diskExpiryThreadIntervalSeconds = 120 cacheEventListeners: net.sf.ehcache.statistics.LiveCacheStatisticsWrapper hitCount = 408267 memoryStoreHitCount = 408267 diskStoreHitCount = 0 missCountNotFound = 2339663 missCountExpired = 1010698 maxBytesLocalHeap = 0 overflowToOffHeap = false maxBytesLocalOffHeap = 0 maxBytesLocalDisk = 0 pinned = false ]
> 
> [ name = org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PROPERTY status = STATUS_ALIVE eternal = false overflowToDisk = false maxEntriesLocalHeap = 10000 maxEntriesLocalDisk = 10000000 memoryStoreEvictionPolicy = LRU timeToLiveSeconds = 120 timeToIdleSeconds = 120 diskPersistent = false diskExpiryThreadIntervalSeconds = 120 cacheEventListeners: net.sf.ehcache.statistics.LiveCacheStatisticsWrapper hitCount = 3226253 memoryStoreHitCount = 3226253 diskStoreHitCount = 0 missCountNotFound = 549198 missCountExpired = 454807 maxBytesLocalHeap = 0 overflowToOffHeap = false maxBytesLocalOffHeap = 0 maxBytesLocalDisk = 0 pinned = false ]
> 
> See attached for CTools current memory status (as of 5 mins ago).  Different environment the cache configuration values are worthy of review.
> 
> Cheers,
> 
> Anth
> 
> 
> anthony whyte | its and mlibrary | university of michigan | arwhyte at umich.edu | 517-980-0228
> 
> 
> On Sep 4, 2013, at 12:52 PM, Sobieralski, Damian Michael wrote:
> 
>> org.sakaiproject.site.api.SiteService.userSiteCache: count:10000 hits:818185 misses:365919 hit%:69
>>  
>> As soon as Greg gets back from lunch he’ll answer how KNL-1011 was applied (since he did it <grin>).
>> 
>> BTW – thank you Sam and Noah for your help here!
>>  
>> - Damian
>>  
>> From: Sam Ottenhoff [mailto:ottenhoff at longsight.com] 
>> Sent: Wednesday, September 04, 2013 12:46 PM
>> To: Noah Botimer
>> Cc: Sobieralski, Damian Michael; sakai-dev at collab.sakaiproject.org
>> Subject: Re: [Building Sakai] Severe DB Performance Issues for 2.9
>>  
>> I agree with Noah.  Your first step should be hugely increasing these maxed-out caches.  Look for any other caches that are maxed-out (look for count where it is 10000,50000,100000) and share those.  Also, KNL-1011 should create a cache called "org.sakaiproject.site.api.SiteService.userSiteCache".  Please share that cache %.  
>>  
>> 
>> On Wed, Sep 4, 2013 at 12:29 PM, Noah Botimer <botimer at umich.edu> wrote:
>> 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"
>>  
>>  
>> 
>> _______________________________________________
>> 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"
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/f98bc9c9/attachment.html 
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: umich-memory_status-20130904.txt
Url: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/f98bc9c9/attachment.txt 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/f98bc9c9/attachment-0001.html

More information about the sakai-dev mailing list