Modlog Pruning: Difference between revisions

From XMBdocs
No edit summary
mNo edit summary
 
Line 9: Line 9:
UNIX_TIMESTAMP() is the MySQL way of saying "fetch the current time in XMB format", and 15768000 is the number of seconds in half of a non-leap year.
UNIX_TIMESTAMP() is the MySQL way of saying "fetch the current time in XMB format", and 15768000 is the number of seconds in half of a non-leap year.


xmbforum.com operates a medium size forum, and it is common to have many thousands of modlog items that are over 6 months old. We don't want to delete them all at once due to performance concerns. Any time a DELETE query runs against a MyISAM type table, the entire table is locked until the query finishes running. So to keep things sane, the LIMIT clause is added:
xmbforum2.com operates a medium size forum, and it is common to have many thousands of modlog items that are over 6 months old. We don't want to delete them all at once due to performance concerns. Any time a DELETE query runs against a MyISAM type table, the entire table is locked until the query finishes running. So to keep things sane, the LIMIT clause is added:


<code>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</code>
<code>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</code>

Latest revision as of 17:12, 2 October 2020

XMB does not have a built-in pruning feature for the new modlog data feature. Modlogs are those messages like "thread closed", "thread moved" etc. The lack of a pruning option is by design. Originally, it was thought that these data should be preserved forever and nobody needed an easy way to delete them.

However, after some time spent behind the wheel of version 1.9.11, the XMB staff decided that the modlog data do become unimportant when they are very old. At XMB, we adopted a policy of pruning our own modlogs when they are over 6 months old.

As you may already know, Super Administrators can run any SQL code from the Admin Panel. This code will display the number of modlog items that are older than 6 months:

SELECT COUNT(*) FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000

UNIX_TIMESTAMP() is the MySQL way of saying "fetch the current time in XMB format", and 15768000 is the number of seconds in half of a non-leap year.

xmbforum2.com operates a medium size forum, and it is common to have many thousands of modlog items that are over 6 months old. We don't want to delete them all at once due to performance concerns. Any time a DELETE query runs against a MyISAM type table, the entire table is locked until the query finishes running. So to keep things sane, the LIMIT clause is added:

DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000

After running this query repeatedly (and giving the server a moment to recover in between runs) we are left with only the newest items. And finally:

OPTIMIZE TABLE $table_logs

This helps to clean up the unused parts of the table and defragment the file it is stored in.