|
[
Permlink
| « Hide
]
Stephen Marquard added a comment - 15-Feb-2007 06:27
mysql logs from resetting Roster tool with above configuration.
These 5 queries are being called for _every_ user in a Roster. So to display a roster of 1000 users generates 5000 SQL queries (without any groups defined):
select NAME, VALUE from SAKAI_REALM_PROPERTY where ( REALM_KEY = 22 ) SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME FROM SAKAI_REALM_ROLE, SAKAI_REALM_FUNCTION,SAKAI_REALM_RL_FN,SAKAI_REALM WHERE SAKAI_REALM.REALM_ID = x'2F736974652F726F7374657274657374' AND SAKAI_REALM.REALM_KEY = SAKAI_REALM_RL_FN.REALM_KEY and SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY and SAKAI_REALM_FUNCTION.FUNCTION_KEY = SAKAI_REALM_RL_FN.FUNCTION_KEY select ROLE_NAME, DESCRIPTION, PROVIDER_ONLY from SAKAI_REALM_ROLE inner join SAKAI_REALM_ROLE_DESC inner join SAKAI_REALM on SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_ROLE_DESC.ROLE_KEY and SAKAI_REALM.REALM_KEY = SAKAI_REALM_ROLE_DESC.REALM_KEY where REALM_ID = x'2F736974652F726F7374657274657374' select ROLE_NAME, USER_ID, ACTIVE, PROVIDED from SAKAI_REALM_RL_GR A inner join SAKAI_REALM B inner join SAKAI_REALM_ROLE C on A.REALM_KEY = B.REALM_KEY and A.ROLE_KEY = C.ROLE_KEY where B.REALM_ID = x'2F736974652F726F7374657274657374' select SAKAI_REALM.REALM_ID,SAKAI_REALM.PROVIDER_ID,(select MAX(ROLE_NAME) from SAKAI_REALM_ROLE where ROLE_KEY = MAINTAIN_ROLE),SAKAI_REALM.CREATEDBY,SAKAI_REALM.MODIFIEDBY,SAKAI_REALM.CREATEDON,SAKAI_REALM.MODIFIEDON,SAKAI_REALM.REALM_KEY from SAKAI_REALM where ( REALM_ID = x'2F736974652F726F7374657274657374' ) at least part of the probelm could be this method:
private String getUserRoleTitle(User user) { try { AuthzGroup realm = AuthzGroupService.getAuthzGroup(getContextSiteId()); Role userRole = realm.getUserRole(user.getId()); return userRole.getId(); } catch(GroupNotDefinedException e) { LOG.error("GroupNotDefinedException", e); } return ""; } The code should rather be: private String getUserRoleTitle(User user) { try { return AuthzGroupService.getUserRole(user.getId(),getContextSiteId()); } catch(GroupNotDefinedException e) { LOG.error("GroupNotDefinedException", e); } return ""; } otherwise the entire realm is loaded each time for each user in the roster just to get their role Megan,
If not fixed, this will kill servers. It's of the same order of severity as the Resources problem a while back. So don't assume this can make it into 2.4 without being fixed. Fortunately, the fixes are relatively easy - the one above, and one other which Josh mailed to me & I'll add as a comment below. They need committing and some further testing. Here's the other change required (from Josh H):
In /profile/profile-component-shared/src/java/org/sakaiproject/component/app/profile/ProfileManagerImpl.java Line 449 (in my somewhat out-of-date branch): realm = AuthzGroupService.getAuthzGroup("/site/" + getCurrentSiteId()); return realm.getUsers().contains(uid); This is called for every user. It shouldn't be, because this is an expensive call. Instead, I would change this to something like AuthzGroupService.isAllowed(uid, "site.visit", siteReference); Greg - see if you can resolve this issue. Thanks, L
Stephen,
I've made the suggested changes above and committed the files in r22196 and r22197. Everything seemed to work ok in Profile and Roster on my local instance, so hopefully it does not break anything else. If you could run your load test on this again, that would be highly appreciated. Thanks, Greg It seems better, but still slow. I created a test site with 1100 users, 27 groups.
If you reset the tool, it takes about 40-60s to render the page, which is an improvement but still in the realms of unacceptable for a production system, so presumably there is more to optimise somewhere. I will take a look at the queries that it generates from database logs when I have a chance to investigate further. Flagging as affecting a released version, 2.4.0.001, rather than just Nightly, so it is recognized as bug against 2.4. Removing Fix Version, as it is an unresolved issue.
Test with the lastest trunk code (r28840) shows that to view the roster in a site with 1100 members and 20 groups of 40 members each takes:
- Between 1 and 2 minutes to render - 152,012 db queries (of which 149,741 are realm queries, i.e. authz or membership checks) This tool is not usable in production, as this level of db activity will kill production servers. It should be fixed or moved back to provisional with a prominent warning not to run this in production. Ryan, please see if you can knock this out before the next tag. Megan can help QA the fix once you have it ready. Thanks, L
Lance,
All of the suggested patches have been put into place by Greg. I noticed a slight typo in one of the patches that was missing the return statement in /profile/profile-component-shared/src/java/org/sakaiproject/component/app/profile/ProfileManagerImpl.java that Greg put in from what Josh H. recommended. But that should not affect the performance issue at hand. On that note for Stephen, I think I am missing something because I am not sure why this method this fix in this method in the ProfileManagerImpl was recommended for this issue. I could be completely wrong, but it does not seem that the " isSiteMember() " method is ever called during the loading of the Roster. The fix is a good fix and should be done, but how does it affect Roster? Any other directions or recommendations out there for where next to proceed with this issue? All the suggestions here have been done. I will keep poking through the code to see where else we might be able to streamline the number of queries run. Some of these changes came up when we were working with both profile & Roster in 2-3-x, so may not be exactly relevant to the problem here. I don't have other suggestions other than to look at the authz queries which seems to be the largest issue, probably group membership. The 2-3-x version of Roster without group membership didn't have this problem. Probably the site & group membership for all groups should get read in one operation and everything else thereafter from memory.
The UCB branch at https://source.sakaiproject.org/svn/roster/branches/ucb/ does not exhibit the same behaviour so it may be worth looking at that. The current trunk Roster destined to be in 2.5 doesn't have the same issues, so this is probably still a 2.4 issue but won't be a 2.5 issue.
We have a performance fix for 2.4 that will improve performance. Will merge into 2-4-x.
Verified that performance in 2-5-x is acceptable in sites with a large number of participants (965) and groups (70+).
Not tested on 2-4-x; if that version of Roster (different code to 2.5 Roster) still shows performance issues, a separate JIRA should be opened. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Critical
Severe performance issues with group-aware Roster
