[Building Sakai] Severe DB Performance Issues for 2.9
Sobieralski, Damian Michael
dsobiera at indiana.edu
Wed Sep 4 13:01:47 PDT 2013
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<mailto: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<mailto:maxElementsInMemory at db.cache.SAKAI_SITE_PAGE_PROPERTY=123456>
timeToLive at db.cache.SAKAI_SITE_PAGE_PROPERTY=999<mailto:timeToLive at db.cache.SAKAI_SITE_PAGE_PROPERTY=999>
timeToIdle at db.cache.SAKAI_SITE_PAGE_PROPERTY=999<mailto:timeToIdle at db.cache.SAKAI_SITE_PAGE_PROPERTY=999>
On Wed, Sep 4, 2013 at 3:19 PM, Noah Botimer <botimer at umich.edu<mailto: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]<mailto:[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<mailto: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<tel: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<mailto:arwhyte at umich.edu> | 517-980-0228<tel: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<mailto: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<mailto: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<tel: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<mailto:botimer at umich.edu>]
Sent: Wednesday, September 04, 2013 12:14 PM
To: Sobieralski, Damian Michael
Cc: sakai-dev at collab.sakaiproject.org<mailto: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<http://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<mailto: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<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto: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/e38ff2ad/attachment.html
More information about the sakai-dev
mailing list