Excessive db queries from permissions change in sites with provided users
Description
is related to
Activity
Neal Caidin August 14, 2017 at 2:02 PM
Bulk closing issues that have not been updated since 2014 and earlier. Please reopen if this is still an issue and you have new information.
Stephen Marquard October 3, 2007 at 1:41 AM
Changing priority to minor, as the id/eid caching introduced for 2.5.0 will eliminate most of the performance impact of this issue.
It may still be desireable to create a separate authz method only to update permissions (i.e. not make any membership changes).
Ray Davis July 20, 2007 at 1:27 PM
I added the User module to the task's component list due to the user-by-EID issue.
Stephen Marquard May 5, 2007 at 7:22 AM
There seem to be 2 problems here:
1. Lack of caching for SAKAI_USER_ID_MAP
2. The operation of changing tool permissions, e.g. as handled by
authz/authz-tool/tool/src/java/org/sakaiproject/authz/tool/PermissionsAction.java
is doing:
AuthzGroup edit = (AuthzGroup) state.getAttribute(STATE_REALM_EDIT);
// read the form, updating the edit
readForm(data, edit, state);
// commit the change
try
{
AuthzGroupService.save(edit);
}
and the save() method here is refreshing the authzgroup membership for the case where there is a provider id, hence the requirement to get userids from eids (returned by the provider).
This seems unnecessary in this instance, as all the user intends to do is change the set of permissions and there's no requirement to update membership (unlike for example in Site Info or Realms admin tool).
Sites with provided users generate 2 queries to SAKAI_USER_ID_MAP for every provided users whenever a permissions change is made in a tool (i.e. the authzgroup is updated).
For example a site with 100 users will generate 200 queries, of this form:
select EID from SAKAI_USER_ID_MAP where USER_ID=x'63376466303137302D663632332D346233662D303032612D303732313036373338666262'
select USER_ID from SAKAI_USER_ID_MAP where EID=x'73747564656E74313334'
These queries are not generated for non-provided users.
This is a serious scaleability issue for sites with large numbers of provided users, e.g. a site with 22K users will generate 44K queries every time a tool permissions change is made.