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

Badly performing MySQL queries connected to questions

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.1.1, 2.2
    • Fix Version/s: 2.1.2
    • Component/s: Questions, Quiz
    • Labels:
    • Environment:
      MySQL server version: 5.0.77
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      You need a database that already contains many quiz attempts. See some of the table sizes quoted in comments below.

      You also need to test this with MySQL (to verify the new code) and with another DB (to verify there are no regressions).

      1. Create a quiz
      2. Attempt it several times as different students.
      3. Go to the quiz reports, select all attempts, and delete them.
      4. Verify that it does not take too long, and that only the right quiz attempts are deleted. (You can probably do that by doing a row count of all the question_attempt* tables, and verifying that that after creating a lot of quiz attempts, and then deleting them, you get back to exactly the same number of rows. Of course, this requires no one else to be using quizzes on the server at the same time.)

      Show
      You need a database that already contains many quiz attempts. See some of the table sizes quoted in comments below. You also need to test this with MySQL (to verify the new code) and with another DB (to verify there are no regressions). 1. Create a quiz 2. Attempt it several times as different students. 3. Go to the quiz reports, select all attempts, and delete them. 4. Verify that it does not take too long, and that only the right quiz attempts are deleted. (You can probably do that by doing a row count of all the question_attempt* tables, and verifying that that after creating a lot of quiz attempts, and then deleting them, you get back to exactly the same number of rows. Of course, this requires no one else to be using quizzes on the server at the same time.)
    • Workaround:
      Hide

      1. Use a real database like Postgres.

      2. Or this code change will fix the problem for MySQL users, although there are issues with the code that we can't use it in the standard Moodle release.

      The problem is in code /question/engine/datalib.php

      For example starting at line 662:

      $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
      SELECT qas.id
      FROM

      {question_attempts} qa
      JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
      WHERE $where)", $params);

      Why this MySQL query is slow: http://forums.mysql.com/read.php?24,5240,5240
      Updated code, that works much faster:

      $stepids = $this->db->get_fieldset_sql("
      SELECT qas.id
      FROM {question_attempts}

      qa
      JOIN

      {question_attempt_steps}

      qas ON qas.questionattemptid = qa.id
      WHERE $where", $params);
      if (!empty($stepids))

      { list($sqlin, $delparams) = $this->db->get_in_or_equal($stepids); $this->db->delete_records_select('question_attempt_step_data', "attemptstepid $sqlin", $delparams); }

      Similar problems also at lines:
      668-671,
      696-699.

      datalib.php file, with this fix: https://github.com/mangus/moodle/blob/7259b04e27ec4e5d03539f3355791ab451e3fe0c/question/engine/datalib.php

      Show
      1. Use a real database like Postgres. 2. Or this code change will fix the problem for MySQL users, although there are issues with the code that we can't use it in the standard Moodle release. The problem is in code /question/engine/datalib.php For example starting at line 662: $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN ( SELECT qas.id FROM {question_attempts} qa JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id WHERE $where)", $params); Why this MySQL query is slow: http://forums.mysql.com/read.php?24,5240,5240 Updated code, that works much faster: $stepids = $this->db->get_fieldset_sql(" SELECT qas.id FROM {question_attempts} qa JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id WHERE $where", $params); if (!empty($stepids)) { list($sqlin, $delparams) = $this->db->get_in_or_equal($stepids); $this->db->delete_records_select('question_attempt_step_data', "attemptstepid $sqlin", $delparams); } Similar problems also at lines: 668-671, 696-699. datalib.php file, with this fix: https://github.com/mangus/moodle/blob/7259b04e27ec4e5d03539f3355791ab451e3fe0c/question/engine/datalib.php
    • Affected Branches:
      MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_21_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      Example: deleting question attempts on large MySQL database can take up to 10 minutes and is wasting database server resources.

      Large database in this case means:
      question_attempts table – ~ 1.5M rows
      question_attempt_steps table – ~ 4M rows
      question_attempt_step_data table – ~ 9M rows

      The fix described in Workaround makes this action to take less then some seconds.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  3 Vote for this issue
                  Watchers:
                  9 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    10/Oct/11