mdl_log table - how big's yours and how do you manage it?

mdl_log table - how big's yours and how do you manage it?

by Peter Roberts -
Number of replies: 7

Hope this is the right forum for this - it is a performance issue. I think this has been a recurring theme over the years, so I wondered what the current thinking was.

How do you manage your mdl_log table? We've passed 22 million rows, which is proving problematic.

Our current policy is to retain the mdl_log entries for 365 days, with the CRON deleting them periodically. This used to be ok but I doubt this is sustainable as our Moodle use increases.

To try and keep the size down, we remove entries from anything referring to course 1 by running something like this :

delete from mdl_log where course=1 and (time < unix_timestamp(20110301))

and then optimize table mdl_log

This used to take around 15 to 20 mins. However now the optimize is taking nearer 90 mins and I guess it will only get worse.

So - what do we do?

  • change the policy(!)
  • make copies of the mdl_log tables and truncate them?

We're using MyISAM - not sure if InnoDB would help particularly.

All suggestions welcome!

Average of ratings: -
In reply to Peter Roberts

Re: mdl_log table - how big's yours and how do you manage it?

by Taylor Judd -

Hi,

The main use of mdl_log, correct me if I'm wrong, is the reports function and activity log. In our case the growth of mdl_log (44 million currently exceeding 120 million by end of semester) has caused significant issues. We address this in a few ways.

1) we limit the access to the reports function professors have on the production system. If complex reports are needed they are run on a replicated database.

2) Every year we rotate the database ensuring that mdl_log doesn't become completly unmanagable. (there are other reasons for the rotating but this contributes)

Because of this setup almost all activity on the production database are inserts into mdl_log. The lack of selects, deletes, or other querries makes its large size less of an issue.

We also run INNODB on it. And while I reccomend INNODB in all cases for Moodle, I'm not sure there is value here. One possible value is that if you are running selects you can avoid table locking by using INNODB over MYISAM.

Finally, I'll say that since we don't get a lot of utility out of mdl_log on the main site, I've actually explored setting it up as a blackhole. (http://dev.mysql.com/doc/refman/5.0/en/blackhole-storage-engine.html) This would allow Moodle to function, but save significant storage. In our case we wouldn't lose data either as with replication the data would exist on our slave servers.

Average of ratings: -
In reply to Taylor Judd

Re: mdl_log table - how big's yours and how do you manage it?

by Miri Hefetz -

Using blackhole as a egine sounds like a great idea.

How do you handle the mdl_log.id column in the slave server?

Average of ratings: -
In reply to Peter Roberts

Re: mdl_log table - how big's yours and how do you manage it?

by sakai user -

Peter

Picked up on this thread late..

I'm running partitioning on the mdl_log table .. As of yet nothing has been deleted from mdl_log our moodle install is going on three years now (maybe 4 I forget). We run the moodle database using INNODB engine as Taylor suggested.. (I'd never run moodle with out using it)

You can see the details of my partitioning in this thread.

http://moodle.org/mod/forum/discuss.php?d=128781

I do need to run some out of band stats on just how many inserts there are / semester etc.. but so far there are 36,675,858 records in the table and it does not perform too badly on any given course lookup.

We don't run statistics inside moodle at all .. primarily used for our staff and instructors to look up activity etc.

-- Kevin

Average of ratings: -
In reply to sakai user

Re: mdl_log table - how big's yours and how do you manage it?

by Monica Bokos -

Hello everyone!

I have a question : does the timestamp in the database coincide with the 365 days setting? On my site it doesn't and I would like to figure out why.

 

Thanks,

 

Monica

Average of ratings: -
In reply to Monica Bokos

Re: mdl_log table - how big's yours and how do you manage it?

by Michael Trestianu -
I am currently using the innodb storage engine rather than MYISAM, as it is specifically designed to efficiently process large data volumes. It is also an ACID compliant storage engine that has commit, rollback, and crash-recovery capabilities. Meaning that your data is protected. I see these benefits are greater than MYISAM's Superior I/O speeds due to its simpler structure.
Average of ratings: -
In reply to Peter Roberts

Re: mdl_log table - how big's yours and how do you manage it?

by Dan Poltawski -

Hi Pete,

We're using MyISAM - not sure if InnoDB would help particularly.

InnoDB should help with the log table because it allows row level locks rather than table level. Which makes a difference on busy tables like log because everything doesnt need to wait on the one operation. See: http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

edit oops, realised this thread is quite old..

Average of ratings: -
In reply to Peter Roberts

Re: mdl_log table - how big's yours and how do you manage it?

by steve miley -

We use innodb table per file.  our log grew to 45 million records, and what I did recently was just delete all records where the id was less than 22 million.  That took about 10 minutes,  then i did an optimize table which took about 12 minutes.

What I had to worry about, was - is the free disk space enough to create the new table.  THe original mdl_log table file was 10 gigs.  After a successful optimize table, it dropped to about 5 gigs.  So it was critical that I have adequate space for that 5 gig table.  Once the optimize was done,  i had an extra 5 gigs of space on that file system.

I do this manually once a year or every 9 months or so.   I create and save a backup of the entire moodle database first, just to keep of copy of the logs in case I need them in the future. 

 

What i might do, is get the timestampe of the record in the middle and do a conversion @ 

http://www.onlineconversion.com/unix_time.htm

Or i might just do a delete from ... where timestamp <  a specific date 

 

I ALWAYS do this  on a test system before the production system so I know how long its going to take; and that I just then have to copy/paste the commands so I don't make a typo. 

 

I also ensure I have DOWNTIME for moodle during this process, I don't want anything writing to the logs. 

I don't have automated pruning of that table done.    

The other file you have to worry about is the grade_grades_history table.

it is the 2nd largest growing table in moodle. 

My suggestions would be - 

1. manually do these actions every certain 6 months or year. 

2. do these steps on a test server first

3. depending upon how much free disk space you have on your db server, will dictate how large you can let it grow.

4. if you have a good mysql dba, I would highly recommend using innodb  file per table config.

Average of ratings: Useful (1)