Magento Database Maintenance

Magento Database Maintenance

Magento does many things well, but maintaining an efficient database is not one of them. Having a lot of products is a good reason to have a large database. Sadly, this isn't the only way your database can become big and sluggish. Maintaining the database through log cleaning can result in a dramatic improvement in site performance and latency. This guide explains how to clean up a Magento database that has grown too large for its own good.

  • NOTE: Before performing any operations on the database, please make a backup.

Log Cleaning

Magento maintains several tables for logging. These tables log things such as customer accesses and which products have been compared. Magento has a mechanism for cleaning these logs regularly, but unfortunately this feature is disabled by default and most customers do not turn it on. 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:


Log Cleaning via Admin

  1. In the Magento Admin, go to System > Configuration.

  2. In the left menu under Advanced click on System.

  3. Under "Log Cleaning", change "Enable Log Cleaning" to YES and configure the Save Log for 15 days:

  4. Click "Save Config"


The shell utility log.php can be configured as a cron job or run manually to clean on-the-fly.

  1. From the Magento root directory, type:

    php -f shell/log.php clean
  • the –days switch can be used to specify how many days back to save.

Manual Cleaning via PhpMyAdmin

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 a couple of other tables not included in those tools.

  1. Open the database in PhpMyAdmin via the Siteworx Control Panel.

  2. In the right (main) frame, select the check box for the following tables:


  3. At the bottom of the page, click the drop-down box that says "With Selected:" and select "Empty".

  4. A confirmation screen will appear. Click "Yes". This will truncate all of the selected tables.

Perform Regularly

We've seen 2GB+ databases drop to a quarter of their size after cleaning the logs, so it is very important that this sort of maintenance is performed regularly. Particularly if your time-to-first-byte latency starts growing larger, and you've implemented the other performance tweaks.  

Article Rating (8 Votes)
Rate this article
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Multiple Storefronts with Magento
Added on Mon, Jul 29, 2013
Finding the Status of Magento Cron Jobs/Tasks
Added on Tue, Aug 6, 2013
Magento Connect Extensions
Added on Wed, Sep 11, 2013
Upgrading to Magento
Added on Mon, Dec 9, 2013
Optimizing Magento performance
Added on Mon, Jul 29, 2013
Changing Magento Base URLs
Added on Fri, Jun 28, 2013
Running a Magento Reindex
Added on Fri, Nov 15, 2013
Setting Up Nexcess CDN With Magento
Added on Fri, Aug 2, 2013
How to Disable Caching within Magento
Added on Tue, Nov 5, 2013
Improving the Magento Maintenance Mode
Added on Mon, Feb 24, 2014