Upgrading local_o365 from 4.5.2 to 5.0.1 is taking unacceptably long time on big sites

[fardinam]

Subject: Performance Bottleneck in local_o365 Upgrade Script (v5.0.1 2025040805) on Large Sites

Description:
We’ve come across a critical performance issue during the upgrade of the local_o365 plugin to version 5.0.1 (2025040805) from 4.5.2 (2024100710) on a large-scale Moodle site. The upgrade script includes a database cleanup step in upgrade.php that causes excessive downtime currently exceeding 72 hours

Environment: Moodle 4.5.7 to 5.0.3
component: local_o365
Version: 4.5.2 (2024100710) to 5.0.1 (2025040805)
Affected table: logstore_standard_log

Issue Details:
The upgrade script in /local/o365/db/upgrade.php (line 1307 onward) attempts to delete log entries using a subselect query:

if ($oldversion < 2024100711) {
// Delete config log entries created by mistake.
$confignames = ['apptokens', 'teamscacheupdated', 'calsyncinlastrun', 'task_usersync_lastdeltatoken',
'task_usersync_lastdelete', 'cal_site_lastsync', 'cal_course_lastsync', 'cal_user_lastsync'];

    foreach ($confignames as $configname) {
        // Delete logstore_standard_log records.
        $DB->delete_records_select('logstore_standard_log',
            'eventname = :eventname AND objectid IN (SELECT id FROM {config_log} WHERE plugin = :plugin AND name = :name)',
            ['eventname' => '\core\event\config_log_created', 'plugin' => 'local_o365', 'name' => $configname]);

        // Delete config_log records.
        $DB->delete_records('config_log', ['plugin' => 'local_o365', 'name' => $configname]);
    }

    // O365 savepoint reached.
    upgrade_plugin_savepoint(true, 2024100711, 'local', 'o365');
}

This query performs poorly on large datasets due to the subselect against mdl_logstore_standard_log.

Please review and consider optimising this upgrade step in the next release. I am happy to provide any further details.

[RayCoSector]

Hi @patmr7, @weilai-irl, and team — just adding some further details to the above.

We re-ran the upgrade after the initial attempt failed — our current assumption is that it hit the “MySQL server has gone away” error, as mentioned earlier when we noted it had been running for 72+ hours.

On the second run, the upgrade completed successfully in 18 hours 59 minutes, of which 16 hours 18 minutes were spent upgrading local_o365.

The remaining Moodle M4.5 → M5.0.3 upgrade steps completed in 2 hours 36 minutes.

These figures are from a like-for-like production environment for King’s College London.
A few useful details about their setup:

• They maintain a 6-month rolling mdl_logstore_standard_log (currently 164 GB).
• mdl_logstore_standard_log currently has 237,843,781 rows
• They have approximately 430,000 OIDC users in the database.
• They retain 10 years of course data.

Regards,
Ray Reid.

[amorrowbellarmine]

Just to add, I'm seeing the same issue in our (relatively small) environment when upgrading from 4.5.5+ to 5.0.3+. Not sure why, but the SQL queries timeout in our environment after roughly 5 minutes, causing the upgrade to never finish. I recreated the queries, manually ran them against the database, and they only took 20 minutes to finish. As a temporary workaround, I ran the queries, commented out the 3 lines of code, and completed the upgrade process. I haven't had the chance to test the updates from the pull request.

Manual queries ran:

DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'apptokens');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'teamscacheupdated');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'calsyncinlastrun');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'task_usersync_lastdeltatoken');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'task_usersync_lastdelete');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'cal_site_lastsync');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'cal_course_lastsync');
DELETE FROM mdl_logstore_standard_log WHERE eventname = '\core\event\config_log_created' AND objectid IN (SELECT id FROM mdl_config_log WHERE plugin = 'local_o365' AND name = 'cal_user_lastsync');

Code commented out in the db/upgrade.php file. file:

//            $DB->delete_records_select('logstore_standard_log',
//                'eventname = :eventname AND objectid IN (SELECT id FROM {config_log} WHERE plugin = :plugin AND name = :name)',
//                ['eventname' => '\core\event\config_log_created', 'plugin' => 'local_o365', 'name' => $configname]);