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

Badly performing MySQL queries when editing questions

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.1, 2.2
    • Fix Version/s: 2.6.6, 2.7.3
    • Component/s: Questions
    • Labels:
    • Testing Instructions:
      Hide

      You need to test both a real DB (e.g. Postgres) to ensure no regressions, and MySQL, to see if we have given it enough of a clue.

      In an ideal world, the MySQL testing would be on a system with a large question_attempts table to see if we have nailed the performance issue. Remote Learner have already tested this in production, and it seems that we have.

      1. Create a quiz & add a couple of question. (T/F is normally easiest). Or just find an existing quiz with no attempts.
      2. On the Edit quiz page, change the Max mark for one of the questions.
      3. Verify that works.
      4. Now, either make some attempts at that quiz as a student, or find a quiz with at least two questions, and some attempts by students, ideally many attempts.
      5. On the Edit quiz page, change the Max mark for one of the questions.
      6. Verify that works.
      7. Go to Results -> Grades in the Quiz administration, and verify that shows the marks updated correctly.
      Show
      You need to test both a real DB (e.g. Postgres) to ensure no regressions, and MySQL, to see if we have given it enough of a clue. In an ideal world, the MySQL testing would be on a system with a large question_attempts table to see if we have nailed the performance issue. Remote Learner have already tested this in production, and it seems that we have. Create a quiz & add a couple of question. (T/F is normally easiest). Or just find an existing quiz with no attempts. On the Edit quiz page, change the Max mark for one of the questions. Verify that works. Now, either make some attempts at that quiz as a student, or find a quiz with at least two questions, and some attempts by students, ideally many attempts. On the Edit quiz page, change the Max mark for one of the questions. Verify that works. Go to Results -> Grades in the Quiz administration, and verify that shows the marks updated correctly.
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_26_STABLE, MOODLE_27_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      The SQL query made from function set_max_mark_in_attempts() in file question/engine/datalib.php line 876 is taking about 15-20 seconds in our Moodle installation (~ 80 000 users).

      Interesting is that, the original SQL query:

      UPDATE question_attempts SET maxmark = 6.7 WHERE questionusageid IN (SELECT quiza.uniqueid FROM quiz_attempts quiza WHERE quiza.quiz = '10001') AND slot = '14';
      

      takes about 15 seconds to run, but when i separate the sub-query:

      SELECT quiza.uniqueid FROM quiz_attempts quiza WHERE quiza.quiz = '10001';
      UPDATE question_attempts SET maxmark = 6.7 WHERE questionusageid IN (<result here>) AND slot = '14';
      

      then the code takes only some milliseconds total.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              timhunt Tim Hunt
              Reporter:
              mangus Mart Mangus
              Peer reviewer:
              Martín Langhoff
              Integrator:
              Damyon Wiese
              Tester:
              Michael de Raadt
              Participants:
              Component watchers:
              Tim Hunt, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              10 Vote for this issue
              Watchers:
              20 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                10/Nov/14