Periodically checking moodle DB table sizes and trimming them...

On a somewhat regular basis, I check the size of the moodle DB backups that I have automatically generated.  When I notice a DB increasing in size, or just being a bit bulky - I like to take a little closer look at the table sizes.

For example.  Today, I checked one of my moodle servers and noticed the DB size has approached 300 MB.














 I opened MySQL Workbench, connected to the server, selected the DB and executed this statement

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 = "batavia"
           ORDER BY data_length desc

This is the result - notice the ORDER BY clause above.









200 of the 300 MB of data is in the mdl_log table.  66 % of the total DB size consisted of the mdl_log table.

So - I trimmed that table using this SQL - only keeping 365 days. (2014 - 02 - 08)

DELETE FROM mdl_log
WHERE time < unix_timestamp(20140208) #where converted time is less than the time specified

 
I doubled checked the setting on the moodle server, just to see what that was set at.
 








Notice the Log file setting, which explains why the were not removed.