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

Grade history report hangs -- previous grade subquery

    XMLWordPrintable

Details

    • MOODLE_28_STABLE, MOODLE_29_STABLE, MOODLE_30_STABLE
    • MOODLE_31_STABLE
    • MDL-50385-master
    • 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)
    • 3.1 Sprint 4
    • 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

              emerrill Eric Merrill
              woolardfa@appstate.edu Fred Woolard
              Simey Lameze Simey Lameze
              David Monllaó David Monllaó
              Jun Pataleta Jun Pataleta
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
              Votes:
              17 Vote for this issue
              Watchers:
              31 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                23/May/16