mdl_logstore_standard_log size problem 2

mdl_logstore_standard_log size problem 2

by Chris Swinney -
Number of replies: 1

Hi All (and even perhaps @Leon Stringer),

There is a thread (now locked) here - https://moodle.org/mod/forum/discuss.php?d=384451

I have the same issue in that the `mdl_logstore_standard_log size` file is approaching 10GB. The logs were set to never delete, thus we have logged in there dating back a few years. I have cleaned up the table using :

DELETE FROM mdl_logstore_standard_log WHERE timecreated < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY));
However, the file is large, so I tried to optimise using:

OPTIMIZE TABLE mdl_logstore_standard_log
but get the following error:

+------------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                                    | Op       | Msg_type | Msg_text                                                          |
+------------------------------------------+----------+----------+-------------------------------------------------------------------+
| bitnami_moodle.mdl_logstore_standard_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| bitnami_moodle.mdl_logstore_standard_log | optimize | error    | The table 'mdl_logstore_standard_log' is full                     |
| bitnami_moodle.mdl_logstore_standard_log | optimize | status   | Operation failed                                                  |
+------------------------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (13 min 44.848 sec)

I suspect this might be something to do with what @Leon Stringer) mentions, i.e. "assuming innodb_file_per_table is enabled", but TBH, I am unsure that this is.



Average of ratings: -
In reply to Chris Swinney

Re: mdl_logstore_standard_log size problem 2

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

I've never seen The table 'mdl_logstore_standard_log' is full before. But it looks like the most likely cause is that the database server is out of disk space. On Linux servers MySQL and MariaDB store files in /var/lib/mysql so check that, for example with df -h /var/lib/mysql. Presumably there would need to be at least 10 GB free for OPTIMIZE TABLE to complete, i.e. enough working space to temporarily copy the file.

To explain innodb_file_per_table: if this is OFF then all data for all tables for all databases is stored in a single file, normally /var/lib/mysql/ibdata1. If it's ON then data for each table is stored in a separate file, for example for mdl_logstore_standard_log in database bitnami_moodle this might be /var/lib/mysql/bitnami_moodle/mdl_logstore_standard_log.ibd. As the database tables contain more data over time these files grow, but if you delete data (DELETE FROM) these files do not shrink, the space becomes available in the file for new data. So to shrink the files you must run OPTIMIZE TABLE but this only reclaims space if innodb_file_per_table is ON. To check the setting for your database run:

SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+ 
| Variable_name         | Value | 
+-----------------------+-------+ 
| innodb_file_per_table | ON    | 
+-----------------------+-------+ 

The default is ON.

If you still need help, please share the Moodle version and the database type (MySQL or MariaDB), and version.

Average of ratings: -