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

restore course: SQL query not optimized (scan full table) to Update the context id of any tags applied to any questions in these categories

    XMLWordPrintable

Details

    • MOODLE_401_STABLE, MOODLE_402_STABLE

    Description

      trying to restore a course, it contains a lot of questions with tags, and it takes a long time to restore, and it turns out that there is a bad performance query problem,
      the Course retrieval includes SQL Update with bad performance, does not use an index, scans thousands of records, and causes a delay about 40% of the  course  total retrieval time 
      ​ Moodle Ver:  Moodle 4.2 (Build: 20230424)     

      database: MariaDB Galera cluster 10.6

       
      ​the query from:     "moodle/backup/moodle2/restore_stepslib.php "
          class  restore_move_module_questions_categories 
          function define_execution():
       
      5390                 // Update the context id of any tags applied to any questions in these categories.
      ....
      5393                     $sqlupdate = "UPDATE {tag_instance}
      5394                                      SET contextid = :newcontext
      5395                                    WHERE component = :component
      5396                                          AND itemtype = :itemtype
      5397                                          AND itemid IN (SELECT DISTINCT bi.newitemid as questionid
      5398                                                           FROM {backup_ids_temp} bi
      5399                                                           JOIN {question} q ON q.id = bi.newitemid
      5400                                                           JOIN {question_versions} qv ON qv.questionid = q.id
      5401                                                           JOIN {question_bank_entries} qbe ON qbe.id = qv.questionbankentryid
      5402                                                          WHERE bi.backupid = :backupid AND bi.itemname = 'question_created'
      5403                                                                AND qbe.questioncategoryid {$categorysql}) ";

       
       
      The update Query is executed many times (about 650 times), each time  reads (full table scan ) 231282 records, and it consumes 100% of the CPU, high I/O disk load, and takes about 7 milliseconds each time.  
      the query from MariaDB  log" 
       

      1. Thread_id: 167  Schema: adminmoodle  QC_hit: No
      2. Query_time: 0.691205  Lock_time: 0.000150  Rows_sent: 0  Rows_examined: 231282
      3. Rows_affected: 0  Bytes_sent: 52
        SET timestamp=1682537401;
        UPDATE mdl_tag_instance
            SET contextid = '702'
            WHERE component = 'core_question'
            AND itemtype = 'question'     
            AND itemid IN 
            (SELECT DISTINCT bi.newitemid as questionid
            FROM mdl_backup_ids_temp bi
            JOIN mdl_question q ON q.id = bi.newitemid
            JOIN mdl_question_versions qv ON qv.questionid = q.id
            JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
            WHERE bi.backupid = 'c261c95a74d7bd3e500e42cc14966d5f' AND bi.itemname = 'question_created'
                AND qbe.questioncategoryid = '722');
         
         
         
        I have rewritten  the Update Query as following; so that, following the index, the execution time is greatly reduced, the process becomes fast, and it does not waste time:
         
         $sqlupdate = "UPDATE {tag_instance}  INNER JOIN
                                         (SELECT DISTINCT bi.newitemid as questionid
                                          FROM {backup_ids_temp} bi
                                          JOIN {question} q ON q.id = bi.newitemid
                                          JOIN {question_versions} qv ON qv.questionid = q.id
                                          JOIN {question_bank_entries} qbe ON qbe.id = qv.questionbankentryid
                                          WHERE bi.backupid = :backupid AND bi.itemname = 'question_created'
                                          AND qbe.questioncategoryid {$categorysql})
                                          AS backup_ids_t
                                          ON
                                          itemid = backup_ids_t.questionid
                                       SET contextid = :newcontext
                                       WHERE component = :component
                                             AND itemtype = :itemtype ";
         
         
        and here are the logs after changes from MariaDb :
         
      4. Query_time: 0.000342  Lock_time: 0.000087  Rows_sent: 0  Rows_examined: 0
      5. Rows_affected: 0  Bytes_sent: 52
        SET timestamp=1682586215;
            UPDATE mdl_tag_instance  INNER JOIN
                (SELECT DISTINCT bi.newitemid as questionid
                FROM mdl_backup_ids_temp bi
                JOIN mdl_question q ON q.id = bi.newitemid
                JOIN mdl_question_versions qv ON qv.questionid = q.id
                JOIN mdl_question_bank_entries qbe ON qbe.id = qv.questionbankentryid
                WHERE bi.backupid = '4aa5a6f405d34e4367f72739e9c0a5d3' AND bi.itemname = 'question_created'
                AND qbe.questioncategoryid = '3244')
            AS backup_ids_t
            ON
            itemid = backup_ids_t.questionid
            SET contextid = '3363'
            WHERE component = 'core_question'
            AND itemtype = 'question';
         
         
        I need to be sure about the modification that I made, in case it does the same result, or it is a wrong modification. In any case, update Query needs to be rewritten better, in order to prevent slow recovery of the course.
         
         
        https://moodle.org/mod/forum/discuss.php?d=446266

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            kflihan Khaled Flihan
            Votes:
            6 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Clockify

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