[Building Sakai] Severe DB Performance Issues for 2.9

Sam Ottenhoff ottenhoff at longsight.com
Wed Sep 4 12:33:10 PDT 2013 

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"****
> ** **
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/9659fe69/attachment.html

More information about the sakai-dev mailing list