Modlog Pruning - Revision history

Miqrogroove at 00:12, 3 October 2020

2020-10-03T00:12:31Z

← Older revision		Revision as of 17:12, 2 October 2020
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>

Miqrogroove at 00:15, 27 December 2017

2017-12-27T00:15:30Z

← Older revision		Revision as of 17:15, 26 December 2017
Line 4:		Line 4:

	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:		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:
	~~{{highlight\|~~<~~nowiki~~>SELECT COUNT(*) FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000</~~nowiki~~>}}
			<code>SELECT COUNT(*) FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000</code>

	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:		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:
	~~{{highlight\|~~<~~nowiki~~>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</~~nowiki~~>}}
			<code>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</code>

	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:		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:
	~~{{highlight\|~~OPTIMIZE TABLE $table_logs}}
			<code>OPTIMIZE TABLE $table_logs</code>

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

Miqrogroove: Created page with "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..."

2017-12-27T00:12:52Z

Created page with "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..."

New page

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:
{{highlight|<nowiki>SELECT COUNT(*) FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000</nowiki>}}

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:
{{highlight|<nowiki>DELETE FROM $table_logs WHERE tid != 0 AND `date` < UNIX_TIMESTAMP() - 15768000 LIMIT 1000</nowiki>}}

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:
{{highlight|OPTIMIZE TABLE $table_logs}}

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