Nexcess Logo

How to perform Magento database maintenance

Knowledge Base Home

Notice anything different?

We've enhanced the appearance of our portal and we're working on updating screenshots. Things might look different, but the functionality remains the same.
September 10, 2019

Learn the basics of how to maintain an efficient Magento database (Magento 1) of any size.

Magento does many things well, but maintaining an efficient database is not one of them. Having many products is a good reason to have a large Magento database. Sadly, this is not the only way your database can become large and sluggish. 

Maintaining the Magento database through log cleaning can result in a dramatic improvement in site performance and latency.This guide explains how to optimize a large and potentially inefficient Magento database.

ATTENTION: Always backup your data before performing any operations on the Magento database.

 Read Magento Maintenance: Five Tasks That Will Keep Your Store Running Smoothly.

Log cleaning for your Magento database

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 this feature is disabled by default and most customers do not enable it.

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.  

You can clean these logs by using one of the three following methods:

  • Log cleaning in the Magento Admin
  • log.php in the ../shell directory
  • phpMyAdmin or MySQL client

The following tables are managed by Magento's log cleaning function:


Log cleaning from the Admin Panel

  1. From the Magento Admin Panel, select System > Configuration.
  2. On the left, locate the Advanced menu and click System.
  3. From the System panel, click Log, and from the Enable Log Cleaning drop-down list, select Yes.
  4. In the Save Log Days field, enter 15 (see above).

  5. Click Save Config.


You can either configure the shell utility log.php as a cron job or run it manually. 

  1. From the Magento root directory, type the command:
    php -f shell/log.php clean
  2.  Use the –days switch to specify how many days of history to save.

Manual cleaning with phpMyAdmin

This is the most efficient way to clean the logs for those more comfortable working with Magento 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 the Magento database size by as much as 95% and greatly reducing query times.

  1. Open the Magento database in phpMyAdmin from the SiteWorx control panel.
  2. In the right frame, select the check box for the following tables:dataflow_batch_export

  3. At the bottom of the page, from the With Selected list, and select Empty.

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

  5. Click the Structure tab at the top of the page.

  6. Select the same tables as you did in Step 2, then under the With Selected list, select Optimize.

For 24-hour assistance any day of the year, contact our support team by email or through your Client Portal.

Jason Dobry
We use cookies to understand how you interact with our site, to personalize and streamline your experience, and to tailor advertising. By continuing to use our site, you accept our use of cookies and accept our Privacy Policy.