-
Task
-
Resolution: Deferred
-
Minor
-
None
-
3.9.19
-
MOODLE_39_STABLE
Running 3.9.19 on Windows with PHP 7.4 and MSSQL 2016, large numbers of simultaneous quiz attempts drive the DB CPU to 100%. I ran a MSSQL management query to identify slow queries, and for roughly 100-200 users attempting to take a quiz there were over 150 rows from the management query identifying a Moodle query similar to the one shown below. The results of one of these queries returned rows with modulename feedback and quiz, eventtype close and open, timestarted, etc.
I looked through the PHP code for the distinctive "min(ev.priority)" string from the slow queries and found it in only one place:
moodle\calendar\classes\local\event\strategies\raw_event_retrieval_strategy.php
which defines a function get_raw_events_legacy_implementation that is called by a function get_raw_events in the same file
which is called by a function get_events in moodle\calendar\classes\local\event\data_access\event_vault.php
which then called by functions calendar_get_legacy_events and calendar_get_view in moodle\calendar\lib.php. calendar_get_legacy_events is defined for web services which are not used by our site, and calendar_get_view is used throughout the calendar.
Searching through the code didn't give me any indication of how the calendar relates to quiz activity, however the "timestart + timeduration" part makes me guess that it's looking for a quiz time allotment. Query:
SELECT e.* FROM mdl_event e INNER JOIN (SELECT ev.modulename, ev.instance, ev.eventtype, MIN(ev.priority) as priority FROM (SELECT modulename, instance, eventtype, priority FROM mdl_event ev WHERE (ev.userid = '(redacted)' AND ev.courseid = 0 AND ev.groupid = 0 AND ev.categoryid = 0) UNION SELECT modulename, instance, eventtype, priority FROM mdl_event ev WHERE (ev.groupid = '(redacted)') UNION SELECT modulename, instance, eventtype, priority FROM mdl_event ev WHERE (ev.groupid = 0 AND ev.courseid IN ('(redacted)','1') AND ev.categoryid = 0) UNION SELECT modulename, instance, eventtype, priority FROM mdl_event ev WHERE (ev.groupid = 0 AND ev.courseid = 0 AND ev.categoryid IN ('(redacted)','(redacted)','(redacted)','(redacted)'))) ev GROUP BY ev.modulename, ev.instance, ev.eventtype) fe ON e.modulename = fe.modulename AND e.instance = fe.instance AND e.eventtype = fe.eventtype AND (e.priority = fe.priority OR (e.priority IS NULL AND fe.priority IS NULL)) LEFT JOIN mdl_modules m ON e.modulename = m.name WHERE (m.visible = 1 OR m.visible IS NULL) AND (timestart >= '1677646800' OR timestart + timeduration > '1677646800') AND timestart <= '1680321599' AND ((e.userid = '(redacted)' AND e.courseid = 0 AND e.groupid = 0 AND e.categoryid = 0) OR e.groupid = '(redacted)' OR (e.groupid = 0 AND e.courseid IN ('(redacted)','1')) OR (e.groupid = 0 AND e.courseid = 0 AND e.categoryid IN ('(redacted)','(redacted)','(redacted)','(redacted)'))) AND (e.visible = 1) ORDER BY COALESCE(e.timesort, e.timestart) ASC, e.id ASC