I need to prune an unusually large mdl_log table but am unsure how to best accomplish this. Performance is clearly suffering due to the overhead on this particular table, and running check and repair on a table this size is getting to be untenable.
So, to clarify, I need to delete everything in the mdl_log table but need some coaching on the safest way to do this.
Ah! In mysql, the following SQL command will nuke your logs immediately:
If your are using a different $CFG->prefix, replace the mdl_ above with your prefix.
However, if using the delete old logs thingy is bringing your server down, then the problem you have is a corrupt index. Search this forum for hints on how to run repair tables -- and do that instead.
If you don't have any index corruption, and this still brings your moodle install down... something crazy is going on.
Check out the MySQL docs at http://dev.mysql.com/doc/refman/5.0/en/repair.html for options etc...
delete from log where time < 1191124800;In English: delete from table log all lines made before 1191124800 (09 / 30 / 07 @ 12:00am) which is the end of study year at our faculty.
This way I've kept the logs for current year.
You can calculate the Unix time stamp here
Or maybe just use more human-friendly MySQL syntax?
DELETE FROM mdl_log WHERE time < UNIX_TIMESTAMP() will delete all rows older then now (just like TRUNCATE works)
DELETE FROM mdl_log WHERE time < UNIX_TIMESTAMP(YYYYMMDD) will delete all rows older then YYYYMMDD (such as 20110830, notice there are no quotes).
For more info on this function see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
What I did so as not to tie up the box for too long was run:
DELETE FROM mdl_log WHERE time < UNIX_TIMESTAMP(YYYYMMDD)
for the last week in the year, just to guage how long I was going to tie the server up by multiplying the time by 4. Which would be running it for a month. So I determined my machine was robust enough to run it for an entire month with a minimal 2 minute slowdown to the users. So I spaced those out every half an hour and within 4 hours I had trimmed down 8 months worth of logs without having to stay up and run the whole thing at one shot a 2am. Sure beats dragging your rear around the next morning.
Running cron.php cleans up the mdl_log table.
One of the things I do is to create an archive log table for each academic year, and do a select on the log table copying those entries to the new archive log table, then deleting those entries from the original.
So, something along these lines:
CREATE TABLE mdl_logstore_standard_log_2014 AS SELECT * FROM mdl_logstore_standard_log WHERE timecreated < 1441065600 AND timecreated > 1409529600;
This will create a new table with the logs from 1st Sept 2014 to 1st Sept 2015. Then you just delete those entries from the original log table:
DELETE FROM mdl_logstore_standard_log WHERE timecreated < 1441065600 AND timecreated > 1409529600;
The new archive table won't be accessible from within Moodle, but you'll still be able to query it. And you're probably going to want to keep log tables in the future as Learning Analytics gets more important.
If you're using MariaDB another thing you may have access to is partitioned tables. This will allow you to partition tables automatically. I've not tried it yet, but it's definitely something I'm looking at. https://mariadb.com/kb/en/mariadb/partitioning-overview/