[Building Sakai] Severe DB Performance Issues for 2.9

[Sam Ottenhoff]

It looks like Greg did it correctly because the 69% hit ratio is good (but
not great).  Now you just need to boost all of your maxed-out caches
especially the ones referenced earlier.

--Sam


On Wed, Sep 4, 2013 at 12:52 PM, Sobieralski, Damian Michael <
dsobiera at indiana.edu> 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
>
> *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"****
>
> ** **
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130904/cc4bc690/attachment.html