[Building Sakai] Severe DB Performance Issues for 2.9

[Sobieralski, Damian Michael]

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