[Building Sakai] Severe DB Performance Issues for 2.9

[Sam Ottenhoff]

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