Purge mdl_logstore_standard_log mysql table

Purge mdl_logstore_standard_log mysql table

by Carlo Gandolfi -
Number of replies: 8

My MySQL database is about 387.3 MiB but half of it (177.4 MiB for 1776308 rows) is moodle.mdl_logstore_standard_log table.

Seems that it most contain a log history of all login since 2004. Is there a way to decrease this table size?

Thanks in advance for your help.

moodle 3.5+, MySQL 5.6.20, PHP 7.1.10

Average of ratings: -
In reply to Carlo Gandolfi

Re: Purge mdl_logstore_standard_log mysql table

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Go to Site administration > Plugins > Logging > Manage logstores

Click 'Settings...' next to Standard logstore. Change 'Keep logs for' to something more appropriate and save.

Wait for Cron to run and to delete the log entries that exceed that time. 

Average of ratings: Useful (1)
In reply to Howard Miller

Re: Purge mdl_logstore_standard_log mysql table

by Carlo Gandolfi -

Thanks Howard, I needed your help to find the right page among 247 administrator links.

I set 'Keep logs for' it from 'Never delete logs' to '365 days'.

moodle.mdl_logstore_standard_log table now is 42.1 MiB for 413541 rows. My database now is 126 Mb, gaining about 260 Mb. My backup is very happy.

Average of ratings: -
In reply to Howard Miller

Re: Purge mdl_logstore_standard_log mysql table

by Augusto Luiz da Costa Schnorr -
Hi!

What about deleting records directly in the DB? Does it have any cons?


We have a HUGE mdl_logstore_standard_log (~50GB) because we have integrated our Moodle to a external system that syncronizes around 12k users 3x a day and there are a lot of logs of that updates (even when there is nothing to update at all, it calls the WS to update, since the integration doesn't compare things before updating).


So we are willing to delete those update records on the mdl_logstore_standard_log table from time to time to reduce its size. Is it safe to do so?


Thanks!
Average of ratings: -
In reply to Augusto Luiz da Costa Schnorr

Re: Purge mdl_logstore_standard_log mysql table

by Abdul Ghaffar -

Hi Augusto,

Were you able to find any thing?....we are facing same issue.

Average of ratings: -
In reply to Abdul Ghaffar

Re:

by Augusto Luiz da Costa Schnorr -
Hi!

Sorry for the delayed response.

Yes. I've deleted most records of WS logging and managed to reduce the table by half its size after a full vacuum.

Augusto.
Average of ratings: -
In reply to Augusto Luiz da Costa Schnorr

Re:

by John Joubert -

So, Augusto - you were able to run deletes directly from <moodle>_logstore_standard_log via sql with no issues?

I have about 5 years of data, and I don't want to delete everything past 365 days to 5 years in one shot, as I'm worried I'll overload the database, and make the system slow/unresponsive until it's finished.

So I'd like to take maybe a month of data of the oldest data off, then see the load.  Then try 3 mos, 6 mos, etc.  Of course, there's no way to do that in the form, so, like yourself, I just wanted to run a "delete from ... where .." directly against the <moodle>_logstore_standard_log table.

Any help appreciated.

John

Average of ratings: -
In reply to John Joubert

Re:

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
There is... that's exactly what it does. Whoever implemented that feature actually thought about that...
Average of ratings: Useful (1)