Performing a backup on our courses in Moodle takes upto 1.5 hours + per course.
Following the thread I opened here some months ago (Click https://moodle.org/mod/forum/discuss.php?d=327600 ) I would like to raise this as a bug as well.
We noticed the problem when our automated backups where taking to long to finish. After some investigation, we realized that querying the database for the logs of the course was the problem.
Since then we have changed our Moodle settings to keep less logs in the db. This improved the performance but not much. Courses are taking around 40-45 minutes to backup. Considering the number of Courses we have, automated backups takes up to 15 days to finish.
Then we thought to make a change to the setting backup | loglifetime from Home / ► Site administration / ► Courses / ► Backups / ► General backup defaults. We have set Moodle to save only logs that are 180 days old to the backup file. No performance change after this change.
My questions and possible solutions are
1. When I perform a backup I see that the query below is executed
SELECT * FROM mdl_logstore_standard_log WHERE contextid = ?
Shouldn't this query use timestamp as well to limit results? Since I changed the backup | loglifetime value? eg
SELECT * FROM mdl_logstore_standard_log WHERE contextid = ? AND timecreated < 'xxxxxxxxxx'
2. I checked the indexes of the logstore table and there is none for the contextid, if one was to be added could it increase performance?
I also enabled profiling on mysql instance, here are the results
Sorry for the long text, I tried to explain the problem as much as possible. I am not expert in the field, and I don't know what is happening behind the scenes, i.e in the backup.php file, I am just trying to solve this issue somehow.
Thank you for your time