#6324 Slow project_role queries due to many unnamed roles per project
closed
General
2015-08-20
2013-06-03
No
The "forge" project (where we have support tickets) had thousands of unnamed roles:
> db.getSiblingDB('pyforge').project_role.find({project_id:ObjectId('4bfaab391be1ce08a2000026'), name:null}).count()
4299
Which causes slow lookups for anonymous browsing of the project (which is common, of course).
Mon Jun 3 18:31:43 [conn6267309] query pyforge.project_role query: { project_id: { $in: [ ObjectId('50a2cd1e2718461589e3795e') ] }, name: "*anonymous" } ntoreturn:0 ntoskip:0 nscanned:4645 keyUpdates:0 numYields: 24 locks(micros) r:42538 nreturned:1 reslen:108 1238ms
Are all the unnamed roles really necessary? If they actually are, maybe the best we can do is change the (project_id) index to (project_id, name). Or make use of the existing (user_id, project_id, name) index by adding user_id:null to the *anonymous query.
allura:cj/6324A few small changes, mostly just adding explicit
user_id: Noneorname: Noneto forceProjectRolequeries to use the index.Unfortunately, the
Credentialscache is cleared on each request due to theallura_globals_middlewareand it's not at all obvious how to persist it longer due to concerns about how large it could grow and when / how to expire entries (there is no cache clearing when permissions are updated, for example).Regarding the cache size concern,
User.project_role()will create aProjectRolefor the user ID / project ID pair whenever a permissions check is done for that user against that project. This means there are potentiallyproject.count() * user.count()number of unnamedProjectRolesthat could be created in mongo and the memory cache. If every current user visited every current project, that would create 1,980,464,408,910 unnamedProjectRoles.