How do I prune mdl_log table?

How do I prune mdl_log table?

by Sean McKay -
Number of replies: 10
Hi All, Sorry for the crosspost but I'm I'm wondering if this is the better place to post this...

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.

Thanks,
Sean
Average of ratings: -
In reply to Sean McKay

Re: How do I prune mdl_log table?

by Martín Langhoff -
There's a configuration setting under /admin that will delete logs older than X days. Just set that, and Moodle will take care of it.
Average of ratings: Useful (1)
In reply to Martín Langhoff

Re: How do I prune mdl_log table?

by Sean McKay -
Martin, sorry I should have mentioned this -- unfortunately, due to the size of the logs (or some other bug), this does not work and actually brings down the server. So, I need a brute force method for doing this directly in MySQL.

So, to clarify, I need to delete everything in the mdl_log table but need some coaching on the safest way to do this.

Thanks!
Sean
Average of ratings: -
In reply to Sean McKay

Re: How do I prune mdl_log table?

by Martín Langhoff -

Ah! In mysql, the following SQL command will nuke your logs immediately:

TRUNCATE mdl_log;

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.

Average of ratings: Useful (1)
In reply to Martín Langhoff

Re: How do I prune mdl_log table?

by Sean McKay -
Martin, I decided to try to allow Moodle to prune the table before using the brute force method...I adjusted the prune logs setting from 365 days to 150 days. At what point in time should I see the logs pruned? Is the pruning process something that happens during the maintenance cron job? As of now, there is no change to the mdl_log table size.

Thanks!
--Sean
Average of ratings: -
In reply to Sean McKay

Re: How do I prune mdl_log table?

by Mitja Podreka -
Picture of Translators
I've used this MySQL statement:
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
Average of ratings: Useful (1)
In reply to Mitja Podreka

Re: How do I prune mdl_log table?

by Andrey Eroftiev -

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

Average of ratings: Useful (3)
In reply to Sean McKay

Re: How do I prune mdl_log table?

by Frank B -

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.

Average of ratings: -
In reply to Sean McKay

Re: How do I prune mdl_log table?

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

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/

Average of ratings: Useful (3)