mdl_logstore_standard_log size problem

mdl_logstore_standard_log size problem

by Amr Elkhedewy -
Number of replies: 5
Hello,

  • What version of Moodle
    3.0.3
  • What is your operating system, what web server are you using, what database and what are the versions of everything (including PHP)?
    Linux, Apache2.2, Mysql 5.6, php 5.6

I've a problem that this table is 40G and keep growing. This table causing performance issues for all database transaction such as backup, etc.
So i need answers please to below questions:
  • What is this table used for?
  • What is the risk of TRUNCATE this table?
  • How to automatically keep 3 months records only from this table?


Average of ratings: -
In reply to Amr Elkhedewy

Re: mdl_logstore_standard_log size problem

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers
  1. This table is used to provide the reports shown in Site administration → Reports → Logs. Some third party plugins may also use this information.
  2. The risk of a TRUNCATE TABLE is to your records-keeping and data retention requirements. For example if you wanted to check the logs for something that happened last week the information would be gone.
  3. You can set a retention period for these logs under Site administration → Plugins → Logging → Standard log and changing Keep logs for to the required value.

If you make that last change the site cron will delete older records but it won't do this in one go (it will delete as much as it can for five minutes then wait till the next run) but the table size will reduce over time.

You could delete the older records manually, the statement below should work but make a backup of the database first:

DELETE FROM mdl_logstore_standard_log WHERE timecreated < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY));

Then you could optionally run OPTIMIZE TABLE mdl_logstore_standard_log to recover space on the filesystem (assuming innodb_file_per_table is enabled). Note that both the DELETE FROM and OPTIMIZE TABLE statements may impact the site's performance when they're running.

(Support for Moodle 3.0 ended in 2015, you should plan to upgrade your site to a supported version. Moodle 3.0.3 is seven updates behind the final update 3.0.10).

Average of ratings: Useful (6)
In reply to Leon Stringer

Re: mdl_logstore_standard_log size problem

by Amr Elkhedewy -

Many thanks for you reply and clarification.

in point number 3:

You can set a retention period for these logs under Site administration → Plugins → Logging → Standard log and changing Keep logs for to the required value.

I configured it as below:

Log guest access NO

Keep logs for 90 Days
Write buffer size 50

- But it never remove old records automatically, so how could i check if the cron configured and working.
- Is this option needs optimizing the table or it will automatically optimize it?


Average of ratings: -
In reply to Amr Elkhedewy

Re: mdl_logstore_standard_log size problem

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You can check if the cron is working by going to Site administration → Notifications. If it is not running you'll see a message like "The cli/cron.php maintenance script has not been run for at least 24 hours". If you see this message you need to set up the site cron (your site will not work properly without it).

You can check the cron is completing successfully by running it at the command line.

If your cron appears to be working you can see check the oldest entry in the log table with the query:

SELECT FROM_UNIXTIME(MIN(timecreated)) FROM mdl_logstore_standard_log;

If old records are being removed, this date should move forward every 24 hours.

The cron does not perform OPTIMIZE TABLE, you would need to do this manually. However, this operation can only recover space on the database server, it will not change the size of the database backup.

Average of ratings: -
In reply to Leon Stringer

Re: mdl_logstore_standard_log size problem

by Amr Elkhedewy -
One more thing i would like to know, i need to decrease the log level, how could i do that?
if the log is configured to log everything and i need to choose some specific things to log, how could this be done?
Average of ratings: -
In reply to Amr Elkhedewy

Re: mdl_logstore_standard_log size problem

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

you can also put log in an external database.

admin/settings.php?section=logsettingdatabase
admin/settings.php?section=analyticssettings (analytics | logstore)


Dominique.
Average of ratings: Useful (1)