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

Failure to upgrade to 2.8 because of a very slow SQL query in assign module steps

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      You must be on latest MOODLE_27_STABLE version of Moodle and have MySQL database

      • Create multiple assignments activities in a course, some with options to have multiple attempts ("Attempts reopened" and "Maximum attempts" configurations)
      • As different students, submit assignments.
      • As a teacher, reopen the some assigments to let the students have a second attempt
      • As these students, submit again new assigments.
      • Checkout the MDL-51191-moodle28 branch and do the upgrade.
      • Assert in the database that the value of the column "latest" from table "assign_submission" is set to "1" for every last submission from students (regarding the "attemptnumber" column).

      Redo the previous instructions with Postgres.
      Redo the previous instructions with MariaDB.

      Show
      You must be on latest MOODLE_27_STABLE version of Moodle and have MySQL database Create multiple assignments activities in a course, some with options to have multiple attempts ("Attempts reopened" and "Maximum attempts" configurations) As different students, submit assignments. As a teacher, reopen the some assigments to let the students have a second attempt As these students, submit again new assigments. Checkout the MDL-51191 -moodle28 branch and do the upgrade. Assert in the database that the value of the column "latest" from table "assign_submission" is set to "1" for every last submission from students (regarding the "attemptnumber" column). Redo the previous instructions with Postgres. Redo the previous instructions with MariaDB.
    • Affected Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Fixed Branches:
      MOODLE_28_STABLE, MOODLE_29_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-51191-master

      Description

      Last Thursday August 20, we tried upgrading our Moodle installation from 2.7.3 to 2.8.6 without success because of a very slow SQL query in the assign module upgrade steps. The query finally finished it's processing more than one hour after it started but we also received a TCP socket error at the end. After that, the PHP upgrade script wasn't doing anything so we had to stop everything and roll back to 2.7.3. For the information, we are actually using MySQL 5.5

      I searched in the tracker and found that it was related to the following issue : MDL-46171.

      Here's the code the produce the SQL query from file "mod/assign/db/upgrade.php" :

      ...
                  // Mark the latest attempt for every submission in mod_assign.
                  $maxattemptsql = 'SELECT assignment, userid, groupid, max(attemptnumber) AS maxattempt
                                      FROM {assign_submission}
                                  GROUP BY assignment, groupid, userid';
       
                  // Note: souterouter looks redundant below, but it forces
                  // MySQL to use an in memory table to store the results of the
                  // inner query. Without this MySQL would complain that the UPDATE
                  // is operating on the same table as the FROM (which is true).
                  $maxattemptidssql = 'SELECT souterouter.id FROM (
                                          SELECT souter.id
                                             FROM {assign_submission} souter
                                             JOIN (' . $maxattemptsql . ') sinner
                                               ON souter.assignment = sinner.assignment
                                              AND souter.userid = sinner.userid
                                              AND souter.groupid = sinner.groupid
                                              AND souter.attemptnumber = sinner.maxattempt
                                      ) souterouter';
                  $select = 'id IN(' . $maxattemptidssql . ')';
                  $DB->set_field_select('assign_submission', 'latest', 1, $select);
      ...
      

      Here's the SQL query information in the slow query log :

      # Query_time: 5291.641908 Lock_time: 0.000134 Rows_sent: 0 Rows_examined: 118023849031
      SET timestamp=1440071732;
      UPDATE mdl_assign_submission SET latest = '1' WHERE id IN(SELECT souterouter.id FROM (
      SELECT souter.id
      FROM mdl_assign_submission souter
      JOIN (SELECT assignment, userid, groupid, max(attemptnumber) AS maxattempt
      FROM mdl_assign_submission
      GROUP BY assignment, groupid, userid) sinner
      ON souter.assignment = sinner.assignment
      AND souter.userid = sinner.userid
      AND souter.groupid = sinner.groupid
      AND souter.attemptnumber = sinner.maxattempt
      ) souterouter);
      

      And yes it's not a joke, The query examined 118 023 849 031 rows
      By the way, we have a pretty big database : More than 450 000 rows in the assign_submissions table.

      I did some tests and researches and find out that the problem is coming from using a "WHERE ... IN (SELECT ...)" clause and being on MySQL. MySQL seems really bad in dealing with this kind of clause. Tim Hunt already talk about it in the comments from task MDL-46171. I also found this link : http://makandracards.com/makandra/2681-mysql-do-not-use-where-id-in-select

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              gaudreaj Jean-Philippe Gaudreau
              Reporter:
              gaudreaj Jean-Philippe Gaudreau
              Peer reviewer:
              Damyon Wiese
              Integrator:
              David Monllaó
              Tester:
              Frédéric Massart
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

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