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

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

XMLWordPrintable

    • 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.

      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.

        1. MDL-51514_explains.txt
          0.9 kB

            sbc24 Sam Chaffee
            sbc24 Sam Chaffee
            Mark Nelson Mark Nelson
            David Monllaó David Monllaó
            Jun Pataleta Jun Pataleta
            Votes:
            6 Vote for this issue
            Watchers:
            18 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.