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

Grade item and grade category optimisations

    XMLWordPrintable

Details

    • MySQL
    • MOODLE_34_STABLE, MOODLE_35_STABLE
    • master_MDL-64325_grade_sql_optimisations
    • Moderate
    • Hide

      Checking performance improvements manually is quite complicated because the following are required:

      • an instance with very large grade_items and grade_grades tables.
      • a quiz with a large number of quiz attempts.

      The compute (grade_item.php) and generate_grades (grade_category.php) methods are covered by PHPUnit tests.

       

       

      Show
      Checking performance improvements manually is quite complicated because the following are required: an instance with very large grade_items and grade_grades tables. a quiz with a large number of quiz attempts. The  compute (grade_item.php) and generate_grades (grade_category.php) methods are covered by PHPUnit tests.    

    Description

      There is a significant performance bottle neck  in the compute function in grade_items 

      https://github.com/moodle/moodle/blob/2cea0bd6c66300813457a2f18dc9a4a075efbc93/lib/grade/grade_item.php#L2119

       

      The query looks like this

      $sql = "SELECT $fields
      FROM {grade_grades} g, {grade_items} gi
      WHERE gi.id = g.itemid AND gi.id $usql $usersql AND gi.courseid=?
      ORDER BY g.userid";

      and $usql contains a list of gradeitems

      No gradeitem fields are returned from the query and there appears to be no benefit from filtering by gi.courseid because
      we are already filtering by grade_items - the method that builds the list of grade_items ("depends_on") already
      filters the list of grade items to a specific course.

      If the query is reformulated as:

      $sql = "SELECT $fields
      FROM {grade_grades} g
      WHERE g.itemid $usql $usersql
      ORDER BY g.userid";

      then the execution time drops by a huge amount. This was discovered when calling the 
      quiz_delete_attempt($attempt, $quiz);
      In a utility and the performance made it unusable. 

       

      In grade_category.php we see exactly the same issue with the following SQL:

      $sql = "SELECT $fields
      FROM {grade_grades} g, {grade_items} gi
      WHERE gi.id = g.itemid AND gi.id $usql $usersql
      ORDER BY g.userid";

       

      Another optimisation - in grade_category.php, the generate_grades method makes a call to
      get grade items from a specific set of ids:

      $sql = "SELECT *
      FROM {grade_items}
      WHERE id $usql";
      $items = $DB->get_records_sql($sql, $params);
      foreach ($items as $id => $item)

      { $items[$id] = new grade_item($item, false); }

      The function quiz_delete_previews in mod/quiz/locallib.php calls quiz_delete_attempt in a
      loop.
      Since quiz_delete_attempt ultimately results in the 'generate_grades' method being called
      there is a risk of the same DB query being made repeatedly - this can be fixed using
      static caching where the ids haven't changed from the previous call.

      Outcome

      These optimisations were tested against a system with 2.5 million grade_grades and 648 grade_items. Deleting attempts was exceptionally slow, and caused timeouts on machines with low ram (4Gb). Deletion can be done via

      /mod/quiz/report.php?id=5&mode=responses

      These optimisations reduced the time from around 90 seconds to under 10 seconds.

       
       

       

       

      Attachments

        Activity

          People

            marcusgreen Marcus Green
            marcusgreen Marcus Green
            Víctor Déniz Falcón Víctor Déniz Falcón
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 minutes
                2m