Maintaining large Log table

Maintaining large Log table

by Richard Flurey -
Number of replies: 6
Hi,

We have been asked to keep logs enabled and never to delete any entries for at least the duration of an academic year. However, after about 6 months some of our modules which either have large enrolments or are heavily used take up to 3 minutes to load and in some cases if many users jump on the same module at the same time, it has reached up to 15 minutes.

I have noticed there is a SELECT query against the mdl_logstore_standard_log table every time a module is viewed. This query seems to delay the loading of the module and seems it is just getting worse because our log is now about 18 Gb in size plus indexes.  I've also noticed that in some cases when visiting a particular module that the index key 'mdl_logstanlog_useconconr_ix is used with a 'range' scan which seems to be faster. However, upon entering another module, although the underlying SQL query is much the same the other key 'mdl_logsstanlog_couanotim_ix is used with a 'ref' type scan and that is usually when the slowness occurs.

I realise I am most likely unable to determine which one of the keys is used in a query and we are not in a position to attempt to optimise the table since it will grow the InnoDB data file significantly and we don't have the benefit of space. I've already cleared out over 30 days worth of entries now and it seems to have made a difference. However, we've been instructed now to delete any more as it affects the various reports and analytics so we know the problem is just going to get worse.

Is there anyone who can help give guidance on how to run large log tables in Moodle that grow indefinately? Is it worth looking at memcache, adding more memory to MySQL and the innodb buffer, database partitioning, changing to Postgres or is it better to offload the data and externalise the analytics with a 3rd party tool like Qlikview?

Thanks,
Richard


Average of ratings: -
In reply to Richard Flurey

Re: Maintaining large Log table

by Zabelle Motte -
Picture of Particularly helpful Moodlers

Hello,

We exactly get the same question.

We have a 4000 courses/35000 users Moodle 3.1 instance with global MySQL database that as 70GB size.
And the logstore_standard_log table is more than 38GB size.

We already find 2 ways to have beter performance :

- limit the log conservation to 365 days  (log table shifted from 49GB to 38GB)
  https://docs.moodle.org/dev/Logging_2

- store the sessions through MEMCACHED
  https://docs.moodle.org/31/en/Session_handling#Memcached_session_driver

But database backup takes a long time due to log table and performance are altered due to database log access while a lot of users play the same ressource (online exam for example).

What is the next step to have better performance ?

- boosting MySQL server ?
- changing to Postgres ?
- implementing external database log storage ?
   https://docs.moodle.org/dev/Logging_2#External_database_log_storage_plugin_.28logstore_database.29
- offload the data ?


Many thanks for answer.


Zabelle

Average of ratings: -
In reply to Zabelle Motte

Re: Maintaining large Log table

by Chris Fryer -
I guess you're using mysqldump to back up the database. I'd recommend you look at other tools like MySQL Enterprise Backup (paid) or XtraBackup (free).
Average of ratings: Useful (1)
In reply to Richard Flurey

Re: Maintaining large Log table

by Chris Fryer -

You should definitely look to optimize MySQL's InnoDB buffer pool. Although the queries you mention don't sound particularly smart, having data and indexes in memory will definitely improve performance.

Here's a guide to figuring out how much memory is required:

https://www.percona.com/blog/2014/12/29/how-well-does-your-table-fit-in-innodb-buffer-pool-56/

Average of ratings: Useful (1)
In reply to Chris Fryer

Re: Maintaining large Log table

by Zabelle Motte -
Picture of Particularly helpful Moodlers

Hello Chris,

Many thanks for this suggestions.

We will have a look at and come back to post observed improvements in this topic.

Kisses

Zabelle

Average of ratings: -
In reply to Richard Flurey

Re: Maintaining large Log table

by Mark Sharp -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers

We found our log table getting incredibly large too, though I suppose MYSQL copes if you have enough resources.

Anyway, we wanted to keep all our logs so that we can do analytics on them at a later date, so what I've taken to doing is archiving the log table off into a separate database so that we still have access to the data, but the data doesn't interfere with day to day running. (Also the backups are much smaller now).

I use a stored procedure to archive on a year/month basis, so I end up with a DB (moodle_archive) with the following tables:

  • mdl_logstore_standard_log_201508
  • mdl_logstore_standard_log_201509
  • mdl_logstore_standard_log_201510
  • ...

I've attached the stored procedure I use, you are free to use it, if it's of use.

I have a cronjob that runs the script periodically, so it's all self-maintaining.

Average of ratings: Useful (2)
In reply to Mark Sharp

Re: Maintaining large Log table

by Zabelle Motte -
Picture of Particularly helpful Moodlers

Many thanks Mark for this suggestion.

We already thought about it.

Thanks for your storage procedure.

We also will analyze this proposition and come back on this post to let inform community on the solution we will implement.

Kisses

Zabelle


Average of ratings: -