Excessive Moodle Logs – Part 2

In Excessive Moodle Logs Part 1, we looked at one of the reasons that your Moodle database can grow seemingly out of control. When first installed, Moodle does not trim back its logs regularly. Log entries are continuously added, quickly growing the logstore_standard_log table, especially if your Moodle LMS is hosting web services.

This can not only slow down your site but cause your backups to take long and consume a lot of disk space. Hopefully, by now, you have configured your instance of Moodle to prevent the problem from getting worse. If not, go back and read Part 1 to complete the preventative steps.

Free the disk space

We will now look at a technique that will not only delete old standard log records quickly, it will also shrink the file size of your database table. Depending on how long you have been living without this maintenance, you could potentially be looking at recovering many gigabytes of disk space.

Note: If you are using a database other than MySQL or MariaDB, you may be to write some SQL code that provides similar functionality. Either way, you will very likely need to modify the code below for your environment. See your database administrator if you need help. Do not attempt this if you don’t understand SQL.

Your first instinct might be to delete the old records. This can be an extremely time-consuming process, not only because of the sheer volume of records to be deleted but because the table indexes need to be updated too. And once done, there would be fewer records in the table but little or no space recovered. You would then need to optimize (MySQL) or vacuum (PostgreSQL) on your database table to recover the disk space.

IMPORTANT: It is highly recommended that you have a fully restorable backup of your Moodle site including the database, moodledata and Moodle application files before you begin the following process. Your backup should have been created with the site offline, not just in maintenance mode. One mistake could potentially break your Moodle site. THIS PROCESS IS PERMANENT! The only way to undo is to restore the database from the backup you made.

We like things that make us go faster

The following advanced technique will not only quickly get rid of all the old records, but you will also eliminate the need to perform optimize (MySQL) or vacuum (PostgreSQL) your database table to shrink the file size afterwards – two operations that can be time-consuming depending on the number of log entries in the database table and the speed of the database infrastructure.

Because there are likely more old log records to get rid of than recent ones you to be kept, the optimized process can be completed in 6 steps. You will need access to your database from a shell command prompt. It is not recommended to perform these steps from within Moodle using a plugin like Adminer as Moodle websites often have a 5 or 10-minute timeout when executing PHP script from a web browser.

1. Before you begin, put your Moodle site in offline (CLI Maintenance) mode.
2. Create a new table by cloning the prefix_logstore_standard_log table (prefix is usually mdl on most installations) without any records.
3. Copy just the recent records you want to keep from the old table into the new one.
4. Delete the old table.
5. Rename the new table to the prefix_logstore_standard_log table (prefix is usually mdl on most installations).
6. Put your Moodle site back online.

That’s it! A database of several hundred gigabytes can be reduced significantly in less than 30 minutes instead of hours. It really can make that much of a difference. The performance will vary depending on the performance of your database server and the size of your logstore_standard_log table.

The following is a sample MySQL script to clean up your logstore_standard_log table.

Note: If you are not using a MySQL/MariaDB database called moodledb, have tables with the prefix mdl_ or want to retain records for a different number of days than 180, you will need to modify the syntax to meet the requirements of your needs.

If your Moodle LMS site IS NOT providing web services

STEP 1 – Put your Moodle site in offline mode.

# STEP 2 – Clone the logstore_standard_log table.
USE moodledb;
CREATE TABLE new_log LIKE mdl_logstore_standard_log;

# STEP 3 – Copy just the recent records from the old table to the new one you just created.
INSERT INTO new_log
  SELECT * FROM mdl_logstore_standard_log
    WHERE ((timecreated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY)));

# STEP 4 – Delete the old table (you can optionally rename it instead if you want to keep it temporarily).
DROP TABLE mdl_logstore_standard_log;

# STEP 5 – Rename the temporary table.
ALTER TABLE new_log RENAME mdl_logstore_standard_log;

STEP 6 – Put your Moodle site back online.

If your Moodle LMS site IS providing web services

If a Web Service has been filling your logs, you may optionally want to use the following script that has a different WHERE clause. Be sure to replace both instances of “21” with the user id of the Moodle user that you use for your web services. Also, update the “2” to reflect the number of days you want to keep logs for your web service user:

STEP 1 – Put your Moodle site in offline mode.

# STEP 2 – Clone the logstore_standard_log table.
USE moodledb;
CREATE TABLE new_log LIKE mdl_logstore_standard_log;

# STEP 3 – Copy just the recent records from the old table to the new one you just created.
INSERT INTO new_log
  SELECT * FROM mdl_logstore_standard_log
    WHERE ((timecreated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))) AND (userid != 21))
      OR ((timecreated > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY))) AND (userid = 21));

# STEP 4 – Delete the old table (you can optionally rename it instead if you want to keep it temporarily).
DROP TABLE mdl_logstore_standard_log;

# STEP 5 – Rename the temporary table.
ALTER TABLE new_log RENAME mdl_logstore_standard_log;

STEP 6 – Put your Moodle site back online.

Testing

Depending on how big your log table was, you should see a general performance increase 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 before your web server times out. If it does, you may need to do a little more cleaning of the logs to get it working.

Hope you found something useful in all of this. See you next month!

Michael Milette

• About
• Latest Posts

Follow me

Michael Milette

Consultant at TNG Consulting

Michael Milette is the owner and an independent consultant with TNG Consulting Inc. in Canada. He works with government, non-profit organizations, businesses and educational institutions on Moodle-related projects. Michael writes about implementing Moodle LMS, developing in Moodle, Moodle administration, using the FilterCodes plugin (his own project), creating multi-language Moodle implementations and courses, and WCAG 2.1 accessibility.

Follow me

Latest posts by Michael Milette (see all)

• Moodle LMS Email Deliverability in 2024: Best Practices, Authentication Standards and Troubleshooting – 23rd February 2024
• Terminology for Those New to Moodle LMS – 20th January 2024
• Enhancing E-Learning in Moodle LMS with an AI Chat Block and FilterCodes – 20th November 2023