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

Badly performing MySQL queries when editing questions

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Critical Critical
    • 2.6.6, 2.7.3
    • 2.1, 2.2
    • Questions
    • MOODLE_21_STABLE, MOODLE_22_STABLE
    • MOODLE_26_STABLE, MOODLE_27_STABLE
    • 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.

      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.

            timhunt Tim Hunt
            mangus Mart Mangus
            Martín Langhoff Martín Langhoff
            Damyon Wiese Damyon Wiese
            Michael de Raadt Michael de Raadt
            Votes:
            10 Vote for this issue
            Watchers:
            20 Start watching this issue

              Created:
              Updated:
              Resolved:

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