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

Retaking a quiz takes too long (db hangs)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.7.9
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Affected Branches:
      MOODLE_37_STABLE

      Description

      We are using moodle 3.7.2 and in some cases retaking a quiz takes too long to initialize. I have narrowed the issue on a specific query that takes too long to execute.

      The query in question is when a user tries to retake the quiz and it looks like mysql uses the wrong index for the same query just different user.

      //MariaDB [ssio_qa]> explain  SELECT q.id, (SELECT COUNT(1)
          ->                             FROM mdl_quiz_attempts quiza
          ->                 JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
          ->                            WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '207883' AND preview = 0 AND state IN ('finished', 'abandoned')
          ->                          ) AS previous_attempts
          ->                                             FROM mdl_question q
          ->                                            WHERE q.category IN ('9400','9483','9484','9401','9485','9486','9487')
          ->                AND q.parent = 0
          ->                AND q.hidden = 0  AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch'))
          ->                                         ORDER BY previous_attempts;
      +------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
      | id   | select_type        | table | type   | possible_keys                                                                       | key                 | key_len | ref                        | rows | Extra                                                               |
      +------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
      |    1 | PRIMARY            | q     | range  | mdl_ques_catidn_uix,category,mdl_ques_par_ix,mdl_ques_qty_ix                        | mdl_ques_catidn_uix | 8       | NULL                       |   45 | Using index condition; Using where; Using temporary; Using filesort |
      |    2 | DEPENDENT SUBQUERY | qa    | ref    | mdl_quesatte_queslo_uix,mdl_quesatte_que_ix,mdl_quesatte_que2_ix                    | mdl_quesatte_que_ix | 8       | ssio_qa.q.id               |  231 |                                                                     |
      |    2 | DEPENDENT SUBQUERY | quiza | eq_ref | uniqueid,mdl_quizatte_quiuseatt_uix,quiz,mdl_quizatte_use_ix,mdl_quizatte_statim_ix | uniqueid            | 8       | ssio_qa.qa.questionusageid |    1 | Using where                                                         |
      +------+--------------------+-------+--------+-------------------------------------------------------------------------------------+---------------------+---------+----------------------------+------+---------------------------------------------------------------------+
      3 rows in set (0.046 sec)
       
      MariaDB [ssio_qa]> explain SELECT q.id, (SELECT COUNT(1)
          ->                             FROM mdl_quiz_attempts quiza
          ->                 JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
          ->                            WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '247681' AND preview = 0 AND state IN ('finished', 'abandoned')
          ->                          ) AS previous_attempts
          ->                                             FROM mdl_question q
          ->                                            WHERE q.category IN ('9400','9483','9484','9401','9485','9486','9487')
          ->                AND q.parent = 0
          ->                AND q.hidden = 0  AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch'))
          ->                                         ORDER BY previous_attempts;;
      +------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
      | id   | select_type        | table | type  | possible_keys                                                                       | key                        | key_len | ref                    | rows | Extra                                              |
      +------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
      |    1 | PRIMARY            | q     | range | mdl_ques_catidn_uix,category,mdl_ques_par_ix,mdl_ques_qty_ix                        | mdl_ques_catidn_uix        | 8       | NULL                   |   45 | Using index condition; Using where; Using filesort |
      |    2 | DEPENDENT SUBQUERY | quiza | ref   | uniqueid,mdl_quizatte_quiuseatt_uix,quiz,mdl_quizatte_use_ix,mdl_quizatte_statim_ix | mdl_quizatte_quiuseatt_uix | 16      | const,const            |    2 | Using where                                        |
      |    2 | DEPENDENT SUBQUERY | qa    | ref   | mdl_quesatte_queslo_uix,mdl_quesatte_que_ix,mdl_quesatte_que2_ix                    | mdl_quesatte_queslo_uix    | 8       | ssio_qa.quiza.uniqueid |   11 | Using where                                        |
      +------+--------------------+-------+-------+-------------------------------------------------------------------------------------+----------------------------+---------+------------------------+------+----------------------------------------------------+
      3 rows in set (0.032 sec)
      
      

      The query for the first user returns results fast but for the second user it hanges.

      If i force the usage of the same index on a test query for the slow user query i get results right away.

      // SELECT q.id, (SELECT COUNT(1)
                                  FROM mdl_quiz_attempts quiza use index (mdl_quizatte_quiuseatt_uix)
                      JOIN mdl_question_attempts qa ON qa.questionusageid = quiza.uniqueid
                                 WHERE qa.questionid = q.id AND quiza.quiz = '5441' AND quiza.userid = '207883' AND preview = 0 AND state IN ('finished', 'abandoned')
                               ) AS previous_attempts
                                                  FROM mdl_question q
                                                 WHERE q.category IN ('9416','9536','9537','9538','9539','9540','9541')
                     AND q.parent = 0
                     AND q.hidden = 0  AND (q.qtype NOT IN ('description','missingtype','random','randomsamatch'))
                                              ORDER BY previous_attempts;
      

      Does anyone have an idea how to fix/optimize this error? Is it the moodle job to define usage of indexes or is this a bad mysql configuration.  This happens only for a small subset of users.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            matejk Matej Konobelj
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: