XenForo Article "General Cleaning" XenForo Forum 2.3

"General Cleaning" XenForo Forum 2.3 - Clean Database and File System periodically​

Over time, your XenForo forum, like a house, will gradually accumulate invisible "dust" and "garbage". It's the log tables that are getting bigger and bigger, the temporary data that's no longer needed, the "orphan" attachments. Periodic cleaning not only frees up storage space but also helps the database operate more efficiently, increases page loading speed and makes the backup process faster.

This article will guide you through a safe and effective "general cleaning" process for both XenForo's database and file system.

Part 1: Cleaning the Database

This is where the most junk data accumulates. We will use a database management tool like phpMyAdmin to execute SQL commands.

Cleaning Log tables is not important

Log tables record admin activities, moderators, system errors... very useful to look up but will be very large over time and rarely need to review data that is too old.
Action: Run the following SQL commands in phpMyAdmin to wipe the data in these tables. Orders TRUNCATE will reset the table to empty state, faster DELETE.

Remove Redirect Threads

When you move a theme, XenForo creates a "ghost" theme to redirect. They don't need to last forever.
Action: Run the following SQL command to delete redirected themes older than 30 days.

Clean up expired search sessions

Action: Run the following SQL command to delete old search data.

Optimize Tables

Once you've deleted a lot of data, you should "defragment" the tables to rearrange the physical structure, making queries faster.
Action:

1. In phpMyAdmin, select your database.
2. Scroll to the bottom of the list of tables, click Check all.
3. In the dropdown box "With selected:", select Optimize table.
4. Wait for the process to complete.

Part 2: Cleaning the File System

Physical files also need attention, mainly located in folders data and internal_data.

Delete "orphaned Attachments"

Sometimes, attachments remain on the server even though the article containing them has been deleted. XenForo has a tool to find and delete them.
Action:

1. Log in to AdminCP.
2. Direct access to the following URL: your-domain.com/admin.php?tools/run-job&job=XF%3AFileCheck
3. Click the button Run job now.
4. The system will scan and report files that are no longer in use. You can delete them from this interface.

Clean temporary folders

Directory internal_data/temp contains temporary files such as incomplete image uploads.
Action:

1. Use FTP or File Manager.
2. Directory Access internal_data/temp.
3. Delete all files with creation date older than 1 week. Do not delete files that are too new because they may be in use.

]Part 3: Schedule Automatic Maintenance (Advanced)

To avoid having to do it manually, you can automate database cleaning by Cron Job.

1. Create an SQL file: Create a text file, name it cleanup.sql and paste all the commands TRUNCATE and DELETE in Part 1 enter there. Upload this file to a folder on your hosting.
2. Creation of Cron Job: In cPanel of hosting, find the Cron Jobs section.
3. Command setup: Create a new cron job to run at a fixed time (e.g. 3 a.m. on the first day of each month). The command to execute will take the form:

(Please replace your database information in the command above.)

Conclusion

Maintaining the forum is a job not to be missed. By taking steps to clean your database and file system periodically every few months, you will ensure your "shared home" is always in the best condition: neat, fast and stable. And always remember the golden rule: Always backup before doing any operations!