-
Bug
-
Resolution: Fixed
-
Minor
-
3.1 regressions, 3.8.5, 3.9.2, 3.10
-
MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
-
MOODLE_310_STABLE
-
MDL-69760-master -
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
|
