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

Grade history report hangs -- previous grade subquery

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Start with a non-upgraded site
      2. Make sure you have performance debugging info on
      3. Create a Medium tests course
      4. Use the single grade report to grade an activity, bulk inserting 100 for all grades (this will probably take a long time)
      5. Use the single grade report to grade an activity, bulk inserting 75 for all grades (this will probably take a long time)
      6. Use the single grade report to grade an activity, bulk inserting 50 for all grades (this will probably take a long time)
      7. Go to the grade history report. After it loads, note the DB Queries Time. I see in the order of 4.5s
      8. Run the upgrade in this ticket
      9. Go back to the grade history report and check the DB Queries Time, it should be significantly decreased. (You can compare with your stable to see the difference)
      Show
      Start with a non -upgraded site Make sure you have performance debugging info on Create a Medium tests course Use the single grade report to grade an activity, bulk inserting 100 for all grades (this will probably take a long time) Use the single grade report to grade an activity, bulk inserting 75 for all grades (this will probably take a long time) Use the single grade report to grade an activity, bulk inserting 50 for all grades (this will probably take a long time) Go to the grade history report. After it loads, note the DB Queries Time. I see in the order of 4.5s Run the upgrade in this ticket Go back to the grade history report and check the DB Queries Time, it should be significantly decreased. (You can compare with your stable to see the difference)
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • Fixed Branches:
      MOODLE_31_STABLE
    • Pull Master Branch:
      MDL-50385-master
    • Sprint:
      3.1 Sprint 4
    • Issue size:
      Large

      Description

      Given certain conditions (i.e. a large mdl_grade_grades_history table, and a few hundred students), the Grades history report will hang.

      Breaking the query down, I've determined that the correlated subquery that produces the 'prevgrade' value is the cause.

      On a small to medium site, the current query may work reasonably well enough; however, on our site where the mdl_grades_history table has 7M+ rows, mdl_grade_items has 100K+ rows, a metacourse (lab) with 274 students, the query will not return.

      It might be considered a bug with MySQL, but the fix can be made by improving the sub-query that generates the prevgrade value.

      The goal of the sub query is to retrieve the finalgrade value from the row immediately preceding the subject row, is it not?

      Firstly, rather than use the MAX aggregate function to flatten any possible multi-row results to a single value result, select a single row to begin with, and take that row's finalgrade value.

      Secondly, can we rely on the ordinality of the id value in mdl_grade_grades_history as much as the timemodified value? So, if the subject grades_history row has id = 10, then we can accept the row with the common oldid value and the highest id value less than 10?

      Thirdly, AND MOST IMPORTANTLY, can we rely on the oldid column value constituting the set of rows that make up a history for a grade? If so, that column should be used in the subquery to associate history rows, rather than the combination of itemid and userid, so MySQL can use an existing index (mdl_gradgradhist_old_ix), otherwise there is no composite index for the current query to use.

      In grade/report/history/classes/output/tablelog.php, line 395, I would suggest the subquery could be:
      $prevgrade = "SELECT finalgrade
      FROM

      {grade_grades_history} gh2
      WHERE gh2.id = (SELECT MAX(gh3.id)
      FROM {grade_grades_history}

      gh3
      WHERE gh3.oldid = ggh.oldid AND gh3.id < ggh.id)";

      I think it's a bit more straight forward than using a NOT EXISTS clause, and it takes advantage of the indexes that are present.

      Making this change, the Grade history report is returned in 1 second or so.

      The alternative, and a perfectly good one, is to create the composite index on mdl_grade_grades_history with the itemid and userid columns; with this approach the current query returns equally quickly as my edited query.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                17 Vote for this issue
                Watchers:
                31 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  23/May/16