[Building Sakai] Severe DB Performance Issues for 2.9

Matthew Jones matthew at longsight.com
Wed Sep 4 13:08:28 PDT 2013 

If the count *is* the same then that means the cache is full (or was full
at one point) and you should increase the size of the cache.

Most of the counts shouldn't be the same as maxEntries.

ehcache isn't configured to doesn't do any periodic eviction of expired
elements by default, so the count might be maxed and using up memory. There
might be stuff in there that's expired just waiting to get bumped out for
something else. You can configure it to evict if you're worried about
memory though, but that will increase your CPU time. [1] That's why you
have to see if stuff is getting bumped because of expiration or because of
a miss. If it's a miss because it's not found, it might have been bumped
previously because it was full or it might have never been there in the
first place, no way to tell unless you increase the size and see your hit %
go up.

Every one of those hits though is one less call to the database, which is
great. And the memory isn't used until it's needed.

[1]
https://code.google.com/p/ehcache-spring-annotations/wiki/EvictExpiredElements


On Wed, Sep 4, 2013 at 4:01 PM, Sobieralski, Damian Michael <
dsobiera at indiana.edu> wrote:

>  So if I go into the UI Superuser tools->Memory the “count” for a cache
> is the same as max entries? ****
>
> ** **
>
> *From:* Matthew Jones [mailto:matthew at longsight.com]
> *Sent:* Wednesday, September 04, 2013 3:47 PM
> *To:* Sam Ottenhoff
> *Cc:* Noah Botimer; Sobieralski, Damian Michael; Developers Sakai-Dev
>
> *Subject:* Re: [Building Sakai] Severe DB Performance Issues for 2.9****
>
> ** **
>
> 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/01595b9c/attachment.html

More information about the sakai-dev mailing list