[WG: Production / Deploying Sakai] jforums performance

[Charles Hedrick]

This weekend we had serious performance problems, with mysql using 95%  
of the CPU and general system slowness. It appears to me that the  
problem was jforum. Here's what I did:

create index jforum_course_categories_course_id on  
jforum_sakai_course_categories(course_id);
create index jforum_user_sakai_user jforum_users(sakai_user_id);

jforum_user is the list of users. It's got 60,000 entries at Rutgers.  
It's indexed on the jforum user ID, but the lookups were by Sakai user  
ID, which is certainly to be expected. That column wasn't indexed. Oops.

There was also a complex query being done that looked up all the  
categories for a course. Unfortunately the category table wasn't  
indexed at all. You wouldn't think the list of categories would be big  
enough to be an issue, but we have 11,000 entries in that table. It  
needs to be indexed.

After adding the second index, load went from 95% to 12%. Of course  
that could be coincidence.

Am I missing some database setup for jforum?