[Building Sakai] Severe DB Performance Issues for 2.9

Wed Sep 4 10:47:08 PDT 2013

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/a82da9d5/attachment.html 