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

Performance improvement on Moodle Event table

    XMLWordPrintable

    Details

    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_310_STABLE
    • Pull from Repository:
    • Pull 3.9 Branch:
      MDL-69760-MOODLE_39_STABLE
    • Pull 3.10 Branch:
      MDL-69760-MOODLE_310_STABLE
    • Pull Master Branch:
      MDL-69760-master
    • Testing Instructions:
      Hide

      Before the upgrade is applied:

      1. Have a database with many events in the event table that will require calendar events to be refreshed as part of the adhoc_task core\task\refresh_mod_calendar_events_task.
      2. Run the task at admin/cli/adhoc_task.php --execute.
      3. Note the performance time.

      After the upgrade is applied:

      1. Have a database with many events in the event table that will require calendar events to be refreshed as part of the adhoc_task core\task\refresh_mod_calendar_events_task.
      2. Run the task at admin/cli/adhoc_task.php --execute.
      3. Note the performance time improvement.
      Show
      Before the upgrade is applied: Have a database with many events in the event table that will require calendar events to be refreshed as part of the adhoc_task core\task\refresh_mod_calendar_events_task. Run the task at admin/cli/adhoc_task.php --execute. Note the performance time. After the upgrade is applied: Have a database with many events in the event table that will require calendar events to be refreshed as part of the adhoc_task core\task\refresh_mod_calendar_events_task. Run the task at admin/cli/adhoc_task.php --execute. Note the performance time improvement.

      Description

      As adhoc tasks are processing calendar event updates during the core\task\refresh_mod_calendar_events_task task, DELETES and UPDATES are run on the event table that call the following fields:

      WHERE modulename = ? AND instance = ? AND eventtype = ?

      The performance on these update and delete statements can be improved with an index as the current implementation appears to be full table scanning and causing disk and CPU utilization to max during execution.

      I've added the following index to our instances and the execution plan has greatly improved during task runs.

      CREATE INDEX mdl_even_modinsevttype_ix ON mdl_event (modulename, instance, eventtype);


      Execution plan prior to index using where and performing full table evaluation:

      EXPLAIN UPDATE mdl_event set type=1 WHERE modulename = 'name' AND instance = 0 AND eventtype = 0;
      ------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
      | id   | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
      +------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
      |    1 | SIMPLE      | mdl_event | index | NULL          | PRIMARY | 8       | NULL | 6786 | Using where |
      +------+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
      

       

      Execution plan after adding the index using where and performing index evaluation with 1 row listed.

      EXPLAIN UPDATE mdl_event set type=1 WHERE modulename = 'name' AND instance = 0 AND eventtype = 0;
      +------+-------------+-----------+-------+---------------------------+---------------------------+---------+------+------+-------------+
      | id   | select_type | table     | type  | possible_keys             | key                       | key_len | ref  | rows | Extra       |
      +------+-------------+-----------+-------+---------------------------+---------------------------+---------+------+------+-------------+
      |    1 | SIMPLE      | mdl_event | range | mdl_even_modinsevttype_ix | mdl_even_modinsevttype_ix | 70      | NULL |    1 | Using where |
      +------+-------------+-----------+-------+---------------------------+---------------------------+---------+------+------+-------------+

      An example of execution time prior to the index:

       

       

      Execute adhoc task: core\task\refresh_mod_calendar_events_task
      ... started 08:08:18. Current memory use 13.2MB.
      Refreshing events for assign
      Refreshing events for assignment
      Refreshing events for attendance
      Refreshing events for book
      Refreshing events for chat
      Refreshing events for choice
      Refreshing events for courseevaluation
      Refreshing events for customcert
      Refreshing events for data
      Refreshing events for feedback
      Refreshing events for folder
      Refreshing events for forum
      ... used 192659 dbqueries
      ... used 2592.1909840107 second

       

      Once the index is added to the table we can see more queries executing in significantly less time:

       

      Execute adhoc task: core\task\refresh_mod_calendar_events_task
      ... started 12:54:43. Current memory use 13.2MB.
      Refreshing events for assign
      Refreshing events for assignment
      Refreshing events for attendance
      Refreshing events for book
      Refreshing events for chat
      Refreshing events for choice
      Refreshing events for courseevaluation
      Refreshing events for customcert
      Refreshing events for data
      Refreshing events for feedback
      Refreshing events for folder
      Refreshing events for forum
      ... used 341541 dbqueries
      ... used 270.89943790436 seconds

       

       

        Attachments

          Activity

            People

            Assignee:
            inkjet2000 Justin Merrill
            Reporter:
            inkjet2000 Justin Merrill
            Peer reviewer:
            cameron1729 cameron1729
            Integrator:
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Tester:
            Janelle Barcega Janelle Barcega
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona)
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              9/Nov/20

                Time Tracking

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours, 13 minutes
                2h 13m