Hope this is the right forum for this - it is a performance issue. I think this has been a recurring theme over the years, so I wondered what the current thinking was.
How do you manage your mdl_log table? We've passed 22 million rows, which is proving problematic.
Our current policy is to retain the mdl_log entries for 365 days, with the CRON deleting them periodically. This used to be ok but I doubt this is sustainable as our Moodle use increases.
To try and keep the size down, we remove entries from anything referring to course 1 by running something like this :
delete from mdl_log where course=1 and (time < unix_timestamp(20110301))
and then optimize table mdl_log
This used to take around 15 to 20 mins. However now the optimize is taking nearer 90 mins and I guess it will only get worse.
So - what do we do?
- change the policy(!)
- make copies of the mdl_log tables and truncate them?
We're using MyISAM - not sure if InnoDB would help particularly.
All suggestions welcome!