Magento maintains several tables for logging. These tables log things such as customer accesses and frequently-compared products. Magento has a mechanism for cleaning these logs regularly, but unfortunately, this feature is disabled by default and most customers do not enable it. There are three ways to clean out these tables: via log cleaning in the Magento Admin, via log.php in the ../shell directory, and manually via phpMyAdmin or MySQL client.
The following tables are managed by Magento’s log cleaning function:
- From the Magento administrator interface, go to System > Configuration.
- In the left menu under Advanced, click System.
- Under Log Cleaning, change Enable Log Cleaning to Yes and configure the Save Log for 15 days:
- Click Save Config
This is the most efficient way to clean the logs for those more comfortable working with databases. It is faster than the built-in Magento tools and it allows you to clean other tables not included in those tools. This procedure will consolidate the data inside those tables, often decreasing database size by as much as 95% and greatly reducing query times.
- Open the database in phpMyAdmin via the SiteWorx control panel.
- In the right frame, select the check box for the following tables:
- At the bottom of the page, click the drop-down box With Selected and select Empty.
- A confirmation screen will appear. Click Yes. This will truncate all of the selected tables.
- Click the Structure tab at the top of the page.
- Select the same tables as you did in step 2, then under the With Selected drop-down menu, select Optimize.
It is not unusual to see 2GB+ databases decrease their size by 75% after cleaning the logs. It is therefore critical to regularly perform this sort of maintenance, particularly if your time-to-first-byte latency begins increasing and you have already implemented the other performance tweaks.