How to prune your moodle log and grade_items_history tables...

This table can also get large over a period of time.  I am trying to reduce my DB size ahead of my next attempt to upgrade my moodle from version 2.2.  I have had trouble, so one of the primary means of reducing changes of more trouble is REDUCING the size of the DB.

Using this script, you can get a report of the size, in MB, of each table in your moodle DB.

SELECT table_name , table_rows, data_length,
ROUND(((data_length+index_length)/1024/1024),2)  AS "Size in MB"
 FROM information_schema.tables
         
 WHERE table_schema = "db name" 
 ORDER BY data_length desc

First off, let moodle try to reduce the size of the mdl_log table by limiting how long it stored the records.  In moodle 2.2, Site Admin | Server | Cleanup

Notice the Keeps logs for setting.  Opps - that is changing to 365 days.

Then, using MySQL Workbench, I will remove all records older than 365 days using this script.

DELETE FROM mdl_log
WHERE time < unix_timestamp(20130730)

This query dumps all records prior to july 30st 2013 (that keeps about 1 year worth of records)

After pruning this table, it reduced from 168 MB to 114.

Additionally, I ran this script on the another large moodle table, mdl_grade_items_history.  I used the same criteria as above and removed records older than 1 year.  Upon completion of the script, the size of the table dropped about 30 MB.

Upon completion of pruning records from both tables, mdl_log and mdl_grade_items_history, I reduced the size of the moodle DB from about 380 MB to 320.  Ill take it.

Useful moodle forum relating to this post.