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

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

    Details

    • Database:
      MySQL
    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Fixed Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-51514_master

      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.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                6 Vote for this issue
                Watchers:
                18 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  9/Nov/15