Have you noticed that the backup of your database seems to have grown excessively over the years and perhaps even seems to be out of control? I feel for, my friend. Whether you are just installing a brand new Moodle site or maintaining one, the information in this article is for you. I have seen Moodle sites that have become sluggish and even unresponsive, and database backups that become so huge that it is no longer feasible to perform backups within the regular maintenance window.
Your problem could be too many logs. Not the pile of wood out back (my wife says that I need to chop more of that), we are talking about Moodle logs.
By default, Moodle LMS keeps logs forever and, without maintenance, can accumulate hundreds of gigabytes or even terabytes of log entries. This can not only have a major negative impact on your site’s responsiveness but Moodle administrators then avoid going to Site Administration > Reports > Logs to consult this useful reporting system because, in extreme cases, it can take your whole site offline resulting in you and your users may experience the dreaded 500 Server Error. If you are using Web Services, the growth rate of your Moodle log may go through the roof.
Fortunately, those great Moodle developers realized this problem and I am here to share their secret solution. I call it a secret because you might assume that automated maintenance would be enabled for such things. Here is how you can stop the problem from getting worse and you can make the change in just 5 minutes.
IMPORTANT: Always ensure that you have a good recent backup of your Moodle website which includes the Moodle application files, moodledata and the database. Practice the following process on a sandbox site that can be easily re-created if something goes wrong. Start and finish this complete process in one day as maintenance tasks usually run in the wee hours of the morning. Until then, you have time to correct any mistakes. If you are not comfortable performing the following tasks, find someone who is. Note that this only applies if you are using the Moodle Standard log store (most sites since Moodle 2.7 use this). It is not applicable if you are using the Legacy logstore.
Now that we have that out of the way, let’s get started. The first thing to do is to log in as a Moodle administrator. Then:
Step 1 – Verify whether your Moodle site is automatically trimming the log
To see if the scheduled task called Log Table Cleanup is enabled and working:
- Navigate to Site Administration > Server > Scheduled Tasks.
- Scroll down the page to Log Table Cleanup.
- If it shows Never run or Task disabled, you will need to enable it by clicking on the related gear (cog) icon and unchecking the Disabled box. While you are there, make sure that the task is not set to run at the same time as your daily backups.
- Select the Save Changes button.
Step 2 – Check and set the Moodle logs retention period
Even if the Log Table Cleanup is enabled, it is not going to make any difference if Moodle is configured to keep the logs indefinitely. To check this:
- Navigate to Site Administration > Plugins > Logging > Standard Log.
- Set the Keep logs for value to a more reasonable value than the default Never Delete Logs. How long should this be? Check with your organization to see if there are policies already in place for such things. Also, consider the duration of your courses. If they typically last 4 months, you may want to choose a retention period of 150 days (about 5 months) or 180 days (about 6 months) to ensure that you have time to run any reports and gather analytics and statistics that may rely on the logs. This will also likely affect plugins and reports that do time-based reporting such as estimating the time spent by students in a course or on the site.
- Select the Save Changes button.
If your log table is huge, it might take a few days, weeks or even months for the task to delete all the outdated log entries but at least it will now eventually sort itself out.
Step 3 – Optionally keep a shorter retention period for Web Services log entries
If you are using Web Services on your Moodle site and it is filling your logs at light speed, take a look at the FilterLog plugin. It works in a very similar way to the Log Table Cleanup plugin mentioned above but enables you to set a different retention period and specify the id of the user whose logs should be cleaned out regularly.
Note that this plugin is in its ALPHA – initial public release stage. However, I found that it seem to work well in my testing and usage. The default setting of Keep Logs For = Always OR User ID = 0 will prevent this plugin from affecting the standard log table. You need to set them both. Feel free to leave feedback on GitHub if you have any concerns or suggestions
Step 4 – Shrink the log table file
The steps so far should have taken you relatively little time and can be completed without scheduling any maintenance downtime for your Moodle site.
As mentioned earlier, enabling these maintenance tasks will prevent the problem from growing. Remember that it can take weeks or even months for the automated maintenance tasks to delete all of the obsolete log entries and make this space available for re-use (longer if cron is not configured!). Be aware that those steps may not automatically shrink your huge database log table files so your backups may be just as big.
For information on how to shrink the log table file, see the Database Performance page in Moodle Docs. Keep in mind that using the OPTIMIZE command in MySQL or VACUUM in PostgreSQL on very large tables can initially be a slow process taking a very long time to complete (post your time and file size, before and after, in the comments below). Fortunately, this can be done later during a scheduled maintenance window.
However, if you have some knowledge of SQL and are willing to wait to do this step until next month, I will tell you how to remove obsolete log entries and shrink your log table file in a small fraction of the time using SQL commands in MySQL or MariaDB. With some SQL knowledge, you should be able to apply a similar approach in PostgreSQL.
Step 5 – Testing
Depending on how big the log table was, you should see a general performance increase of your Moodle site as your database server can now breathe again. The real test will be to navigate to Site Administration > Reports > Logs and see if your logs will appear in seconds or still times out when you click the Get these logs button. If this report still doesn’t work, you may need to trim your logs back a little further.
Hope you found something useful in all of this. See you next month!