Hello community, I am writing this time to ask you how to reduce the size of the mdl_log table. I have an environment (Moodle) that I have been updating for several years and this table has grown too much. This is a problem every time I have to update the version.
If someone knows something about this I appreciate your help.
Best regards.
Verónica.
Hi Rick, thank you very much for your answer, but I would like to keep the records until 2017 and the rest delete it. But it won't let me, the maximum is 1000 days, can that be changed?
On the other hand, I activate it and what happens, it just takes care of deleting the records in the mdl_log table?
Again thank you very much.
Best regards.
Verónica.
Thank you very much Gregor for your contribution!
Regards.
Verónica.
Hi Rick, I changed the "logstore_standard | loglifetime" option to 1000 days, 2 days have passed and the table has not reduced its size. Do I have to configure something in particular about the cron? Because it works correctly.
What could have happened? or what would I be missing to configure?
I wait your answer.
From already thank you very much.
Greetings.
Verónica.
... 2 days have passed and the table has not reduced its size.
How are you checking this? The scheduled task that removes old logs only runs for 5 minutes every day. If there are many old logs then it can take many days to reduce this to 1000 days.
What database software are you using? For MySQL or MariaDB you can see the date of the oldest log entry with the SQL query SELECT FROM_UNIXTIME(MIN(timecreated)) FROM mdl_logstore_standard_log, for example:
MariaDB [moodle]> SELECT FROM_UNIXTIME(MIN(timecreated)) FROM mdl_logstore_standard_log;
+---------------------------------+
| FROM_UNIXTIME(MIN(timecreated)) |
+---------------------------------+
| 2019-12-11 13:16:17 |
+---------------------------------+
This should change to a more recent date every day if the task is working.
I changed the "logstore_standard | loglifetime" option to 1000 days
This setting specifically affects the newer mdl_logstore_standard_log table (introduced in Moodle 2.7). If it's the old mdl_log table you want to manage you must change the loglifetime setting for the legacy log store.
Hello Leon, thank you very, very much for your reply. I'll answer you and detail the situation:
I have two Moodle:
MariaDB [ingreso]> SELECT FROM_UNIXTIME(MIN(timecreated)) FROM mdl_logstore_standard_log;
+---------------------------------+
| FROM_UNIXTIME(MIN(timecreated)) |
+---------------------------------+
| 2019-06-12 18:59:06 |
+---------------------------------+
1 row in set (0.022 sec)
MariaDB [ingreso]> select count(*) from mdl_logstore_standard_log;
+----------+
| count(*) |
+----------+
| 2469142 |
+----------+
1 row in set (3.100 sec)
rw-rw---- 1 mysql mysql 494927872 ene 2 17:20 mdl_logstore_standard_log.ibd
MariaDB [ingreso]> SELECT FROM_UNIXTIME(MIN(timecreated)) FROM mdl_logstore_standard_log;
+---------------------------------+
| FROM_UNIXTIME(MIN(timecreated)) |
+---------------------------------+
| 2021-07-30 04:57:47 |
+---------------------------------+
1 row in set (0,001 sec)
MariaDB [ingreso]> select count(*) from mdl_logstore_standard_log;
+----------+
| count(*) |
+----------+
| 1598573 |
+----------+
1 row in set (9,762 sec)
-rw-rw---- 1 mysql mysql 692060160 abr 25 18:45 mdl_logstore_standard_log.ibd
As far as I can see, the date is fine, the change made in ‘logstore_standard | loglifetime’ is working, but what about the size? Why has it grown so much despite having a deletion in between?
Best regards.
Verónica.
With database software typically if you add 10 rows then delete 10 rows the storage still keeps the original space and reuses this later if needed.
For MariaDB you can recover this space on demand with OPTIMIZE TABLE. For a production site this can cause performance issues while it's running so you would need to plan a maintenance window. So you would run:
MariaDB [ingreso]> OPTIMIZE TABLE mdl_logstore_standard_log;
I would suggest placing your site in maintenance mode while this running. I can't say how long it will take as it depends on your server's performance (maybe 10 minutes?).
Suggest installing MySQLtuner.pl - a pearl script - and run it with superuser (root for DB server) credentials.
https://github.com/major/MySQLTuner-perl
It will look at DB stats you don' t see in moodle and make recommendations for fine tuning your DB server. One of the items it detects is tables that are in need of optimizing and even shows the command to do so.
Moodle is backended by DB and the better your DB config, the better for moodle. I run tuner about once a week on busy sites. Not unusual for a table to be in need of optimizing if teachers have been doing clean ups of courses, etc.
'SoS', Ken
Thank you very much Mark for your reply!
How do you write your own plugin?
I'm going to look in detail at the tracker item, but it occurs to me to modify the code to add 2500 days in the dropdown list, for example What do you think?
Regards.
Verónica.
