Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-77613

Database CPU spike with large number of simultaneous quiz attempts

XMLWordPrintable

    • 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

            Unassigned Unassigned
            mhenry79 Mike Henry
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.