-
Bug
-
Resolution: Unresolved
-
Major
-
None
-
3.9.10
-
MOODLE_39_STABLE
In messagelib.php, in function message_get_providers_for_user() there is a SQL query:
$sql = "SELECT DISTINCT rc.capability, 1 |
FROM {role_assignments} ra
|
JOIN {context} actx ON actx.id = ra.contextid
|
JOIN {role_capabilities} rc ON rc.roleid = ra.roleid
|
JOIN {context} cctx ON cctx.id = rc.contextid
|
WHERE ra.userid = :userid
|
AND rc.capability $capcondition |
AND rc.permission > 0
|
AND (".$DB->sql_concat('actx.path', "'/'")." LIKE ".$DB->sql_concat('cctx.path', "'/%'"). |
" OR ".$DB->sql_concat('cctx.path', "'/'")." LIKE ".$DB->sql_concat('actx.path', "'/%'").")"; |
|
See in github: https://github.com/moodle/moodle/blob/MOODLE_39_STABLE/lib/messagelib.php#L628
This query will perform poorly for Moodle instances with a lot of enrollments and lot of permission overrides.
To reproduce the problem:
- Install clean Moodle 3.9.10
- Create empty course called course1
- Create quiz activity.
- Edit quiz permissions.
In Advanced role override for role teacher set:
mod/quiz:emailnotifysubmission
to non-default value (allow)
Save changes. - Duplicate quiz 20 times.
- Create user called teacher1.
- Enroll teacher1 as editing teacher in course1
- Backup the course. Include user's data.
- Run the query, notice it's super quick.
SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3' |
AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0; |
- Restore the course 200 times:
$ for r in {1..200} ; do moosh course-restore backup-moodle2-course-2-course1-20210914-1855.mbz 1; done - Run the query again:
mysql> SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3' |
AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0; |
+--------------------------------+---+ |
| capability | 1 |
|
+--------------------------------+---+ |
| mod/quiz:emailnotifysubmission | 1 |
|
+--------------------------------+---+ |
1 row in set (0.93 sec) |
- Restore the course 200 times more. Run the query again:
mysql> SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3' |
AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0; |
+--------------------------------+---+ |
| capability | 1 |
|
+--------------------------------+---+ |
| mod/quiz:emailnotifysubmission | 1 |
|
+--------------------------------+---+ |
1 row in set (3.11 sec) |
The impact of this query is visible when setting up quiz notifications. When a student finishes quiz attempt, notifications are sent to teachers. And this is when this check kicks in - synchronously, during submission.
We have a production system where this query takes about 2 minutes. And in the course, there are 4 teachers assigned, meaning that student quiz submission takes about 8 minutes.