[Building Sakai] Severe DB Performance Issues for 2.9

Matthew Jones matthew at longsight.com
Wed Sep 4 12:47:13 PDT 2013 

Yea, I think these properties and what to use are still pretty confusing
and inconsistent. I know that in 2.9 maxElementsInMemory was changed to
maxEntriesLocalHeap (KNL-1008). And they all should work with the pattern,
but I didn't have any luck with those BaseDbFlatStorage ones either.

(This below didn't seem to do anything)
memory.org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY=maxEntriesLocalHeap=50000

On Wed, Sep 4, 2013 at 3:33 PM, Sam Ottenhoff <ottenhoff at longsight.com>wrote:

> I tested on trunk and couldn't alter the values in sakai.properties with
> this style.... I thought it would work.  Here is what did work from
> sakai.properties:
>
> maxElementsInMemory at db.cache.SAKAI_SITE_PAGE_PROPERTY=123456
> timeToLive at db.cache.SAKAI_SITE_PAGE_PROPERTY=999
> timeToIdle at db.cache.SAKAI_SITE_PAGE_PROPERTY=999
>
>
>
> On Wed, Sep 4, 2013 at 3:19 PM, Noah Botimer <botimer at umich.edu> wrote:
>
>> There is a way to set them from sakai.properties. See here:
>>
>> https://jira.sakaiproject.org/browse/SAK-14120
>> https://jira.sakaiproject.org/browse/KNL-293
>>
>> I think this is the default for the site cache (from
>> config/.../default.sakai.properties):
>>
>>
>> memory.org.sakaiproject.site.impl.SiteCacheImpl.cache=timeToLiveSeconds=300,timeToIdleSeconds=300,maxElementsInMemory=10000
>>
>> Note the "memory" prefix, which you don't see in the status page. I think
>> it's required for the values to map over.
>>
>> Thanks,
>> -Noah
>>
>> On Sep 4, 2013, at 3:04 PM, Sobieralski, Damian Michael wrote:
>>
>> Oops…I mean
>>
>> kernel/kernel-component/src/main/webapp/WEB-INF/db-components.xml****
>> ** **
>> *From:* Sobieralski, Damian Michael
>> *Sent:* Wednesday, September 04, 2013 3:03 PM
>> *To:* 'Noah Botimer'; 'Anthony Whyte'
>> *Cc:* 'Sam Ottenhoff'; 'Developers Sakai-Dev'
>> *Subject:* RE: [Building Sakai] Severe DB Performance Issues for 2.9****
>> ** **
>> I see they are in:
>>
>>
>> kernel/kernel-component/target/sakai-kernel-component-1.3.0-ONCOURSE/WEB-INF/db-components.xml
>>
>>
>> http://collab.sakaiproject.org/pipermail/sakai-dev/2012-February/016040.html
>> ****
>> ** **
>> It seems like we'd add a property maxElements in memory to set a value.
>> As far as I can tell we currently don't have it set to I assume we are
>> using default. Is this how you set these? Or is there a property?
>>
>> I don’t see how to set org.sakaiproject.site.impl.SiteCacheImpl.cache.***
>> *
>>
>> ** **
>> *From:* Sobieralski, Damian Michael
>> *Sent:* Wednesday, September 04, 2013 2:38 PM
>> *To:* 'Noah Botimer'; Anthony Whyte
>> *Cc:* Sam Ottenhoff; Developers Sakai-Dev
>> *Subject:* RE: [Building Sakai] Severe DB Performance Issues for 2.9****
>> ** **
>> This is probably a silly question but where are these set?
>>
>> I’ve never set these before.****
>> ** **
>> *From:* Noah Botimer [mailto:botimer at umich.edu]
>> *Sent:* Wednesday, September 04, 2013 1:47 PM
>> *To:* Anthony Whyte
>> *Cc:* Sobieralski, Damian Michael; Sam Ottenhoff; Developers Sakai-Dev
>> *Subject:* Re: [Building Sakai] Severe DB Performance Issues for 2.9****
>> ** **
>> Yes, I'm reasonably confident in my suggestion, but thanks for helping
>> sanity check it.****
>> ** **
>> I think raising the site cache would relieve some pressure on the page
>> property cache, but all of these should be sized up and target 75%+. My
>> factor suggestions are based on the items in the site cache being much
>> larger and its hit% much better. (Smaller change for the better behaved,
>> more expensive thing.)
>>
>> Thanks,****
>> -Noah****
>>
>>
>> On Sep 4, 2013, at 1:17 PM, Anthony Whyte <arwhyte at umich.edu> 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<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"****
>> ** **
>>
>>
>>
>
> _______________________________________________
> 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/d385b7a0/attachment.html

More information about the sakai-dev mailing list