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

High CPU on DB when running a SQL query (missing db index)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.8.6, 2.9.2
    • Fix Version/s: None
    • Component/s: Gradebook, Performance
    • Labels:
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE

      Description

      We experience a problem with the mdl_grade_grades_history table witch causes high CPU usage on DB server.

      SELECT ggh.id, ggh.timemodified, ggh.itemid, ggh.userid, ggh.finalgrade, ggh.usermodified,
      ggh.source, ggh.overridden, ggh.locked, ggh.excluded, ggh.feedback, ggh.feedbackformat,
      gi.itemtype, gi.itemmodule, gi.iteminstance, gi.itemnumber, u.email, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname, ug.firstnamephonetic AS graderfirstnamephonetic,ug.lastnamephonetic AS graderlastnamephonetic,ug.middlename AS gradermiddlename,ug.alternatename AS graderalternatename,ug.firstname AS graderfirstname,ug.lastname AS graderlastname, (SELECT MAX(finalgrade)
      FROM mdl_grade_grades_history h
      WHERE h.itemid = ggh.itemid
      AND h.userid = ggh.userid
      AND h.timemodified < ggh.timemodified
      AND NOT EXISTS (
      SELECT 1
      FROM mdl_grade_grades_history h2
      WHERE h2.itemid = ggh.itemid
      AND h2.userid = ggh.userid
      AND h2.timemodified < ggh.timemodified
      AND h.timemodified < h2.timemodified)) AS prevgrade,
      CASE WHEN gi.itemname IS NULL THEN gi.itemtype ELSE gi.itemname END AS itemname
      FROM mdl_grade_grades_history ggh
      JOIN mdl_grade_items gi ON gi.id = ggh.itemid
      JOIN mdl_user u ON u.id = ggh.userid
      LEFT JOIN mdl_user ug ON ug.id = ggh.usermodified
      WHERE gi.courseid = '737' ORDER BY timemodified DESC, id DESC LIMIT 0, 100
      

      See also https://moodle.org/mod/forum/discuss.php?d=308516

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                9 Vote for this issue
                Watchers:
                14 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: