[Deploying Sakai] Sakai 2.7.1 performance issues

[Martin B. Smith]

On 9/25/2010 2:39 PM, Tom Hall wrote:
>   Good day everyone
> 
> We are having some severe performance issues on our Sakai 2.7.1 system.  
> We are using MySQL and Java 1.6
> 
> About the only thing we are seeing in a process list are queries like
> [...]
> These queries are taking long periods of time (>2 minutes).
> 
> Has anyone seen anything like this in 2.7.1?
> 
> Tom

Hi Tom,

Our 2.6.x branch of Sakai at UF includes the msgcntr tool from 2.7.x,
and we saw many of those poor performing 'select count(*)...' queries
similar to the one you included.

The worst one for us was was resolved by an index on:
SAKAI.MFR_MESSAGE_T (SURROGATEKEY, DELETED, DRAFT );

You may be interested that we considered some indexes from:

http://jira.sakaiproject.org/browse/SAK-8421
http://bugs.sakaiproject.org/jira/browse/SAK-12754

We're looking at a few more potential indexes derived from actual
performance issues in our production system, and some of those indexes
may overlap with the above links.

Here are some of the tables and columns we're considering for an index:
SAKAI.MFR_TOPIC_T("OF_SURROGATEKEY");
SAKAI.MFR_MEMBERSHIP_ITEM_T("T_SURROGATEKEY");
SAKAI.MFR_OPEN_FORUM_T("SURROGATEKEY");
SAKAI.MFR_AREA_T("CONTEXT_ID","ID");
SAKAI.MFR_PRIVATE_FORUM_T("OWNER","SURROGATEKEY","ID");
SAKAI.MFR_TOPIC_T("PF_SURROGATEKEY");
SAKAI.MFR_TOPIC_T("PT_SURROGATEKEY");
SAKAI.MFR_TOPIC_T("TOPIC_DTYPE","TITLE","PF_SURROGATEKEY","UUID") ;

These are from an Oracle 11g R2 system, so your mileage may vary.

Hope that helps,
-- 
Martin B. Smith
smithmb at ufl.edu - (352) 273-1374
CNS/Open Systems Group
University of Florida

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5497 bytes
Desc: S/MIME Cryptographic Signature
Url : http://collab.sakaiproject.org/pipermail/production/attachments/20100926/017de1d9/attachment-0001.bin