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

Performance issue in grade_category::set_usedinaggregation() query in MySQL

    XMLWordPrintable

Details

    • MySQL
    • MOODLE_28_STABLE, MOODLE_29_STABLE
    • MOODLE_28_STABLE, MOODLE_29_STABLE
    • MDL-51514_master
    • Hide

      Test on MySQL and MariaDB

      Test 1
      1. Create a new course
      2. Enroll some students in the course
      3. Create several graded assignments
      4. Create at least 3 grade categories
      5. Put a couple of each of the assignments into each category.
      6. Grade several students in one of the assignments that is in a category other than the course category
      7. Move the assignment that you graded from its current category to a new category.

      Ensure that the results are consistent with expectations and that there are no errors during the move.

      Test 2
      1. Wait for the CI server to say all is good in behat land.
      Show
      Test on MySQL and MariaDB Test 1 Create a new course Enroll some students in the course Create several graded assignments Create at least 3 grade categories Put a couple of each of the assignments into each category. Grade several students in one of the assignments that is in a category other than the course category Move the assignment that you graded from its current category to a new category. Ensure that the results are consistent with expectations and that there are no errors during the move. Test 2 Wait for the CI server to say all is good in behat land.

    Description

      We've run into an issue where the following query in grade_category::set_usedinaggregation() is causing deadlocks in MySQL using Innodb:

      $sql = "UPDATE {grade_grades}
                          SET aggregationstatus = 'unknown',
                                  aggregationweight = 0
                    WHERE userid = :userid
                          AND itemid IN ($itemssql)";
      

      This method can be called many times during a regrade and therefore optimizing the query can result in a performance gain. We're providing a patch that optimizes the query in MySQL. According to my tests, Postgres' query planner already optimizes the original query.

      Attachments

        Activity

          People

            sbc24 Sam Chaffee
            sbc24 Sam Chaffee
            Mark Nelson Mark Nelson
            David Monllaó David Monllaó
            Jun Pataleta Jun Pataleta
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
            Votes:
            6 Vote for this issue
            Watchers:
            18 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              9/Nov/15