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

Fix Course gradebook slow query due to cross join on full user table (backport of MDL-69190)

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Enable debugging
      2. Enable perf debugging
      3. Edit config.php and add the following line above require_once(_DIR_ . '/lib/setup.php');

        ini_set('memory_limit', '-1');

      4. Create a Large course (Site admin -> Development -> Make test course)
      5. Navigate to the course -> Grades
      6. Refresh the page a couple of times
      7. Note the DB query time in the footer
      8. Open a terminal and find the commit:

        git log --oneline --grep="MDL-69641"
        

      9. Grab the commit hash and revert the commit, i.e.

        git revert 68115bf9870
        

      10. Navigate to the course -> Grades again
      11. Refresh the page a couple of times
        1. Confirm that the times are slower than the times noted earlier

      Please test in all supported databases._

      Show
      Enable debugging Enable perf debugging Edit config.php and add the following line above  require_once(_ DIR _ . '/lib/setup.php'); ini_set('memory_limit', '-1'); Create a Large course (Site admin -> Development -> Make test course) Navigate to the course -> Grades Refresh the page a couple of times Note the DB query time in the footer Open a terminal and find the commit: git log --oneline --grep="MDL-69641" Grab the commit hash and revert the commit, i.e. git revert 68115bf9870 Navigate to the course -> Grades again Refresh the page a couple of times Confirm that the times are slower than the times noted earlier Please test in all supported databases._
    • Affected Branches:
      MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_38_STABLE, MOODLE_39_STABLE
    • Pull from Repository:
    • Pull 3.8 Branch:
      MDL-69641-MOODLE_38_STABLE
    • Pull 3.9 Branch:
      MDL-69641-MOODLE_39_STABLE

      Description

      Moodle 3.9 is LTS and most of the big instances will be on this version for some time and won't upgrade to 3.10 or 3.11. Would be good to help them to improve the performance by backporting MDL-69190.

      Also it's still a kind of mystery why it wasn't integrated before. Seems like during the integration Andrew Nicols had seen some issues with the patch. However, there is no actual description of what those issues were.  Would be good to figure it out and backport SQL improvement for earlier versions.

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dmitriim Dmitrii Metelkin
              Reporter:
              dmitriim Dmitrii Metelkin
              Peer reviewer:
              Dan Marsden
              Integrator:
              Sara Arjona (@sarjona)
              Tester:
              Janelle Barcega
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                9/Nov/20

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours, 30 minutes
                  3h 30m