Site: US UK AU |

How to perform Magento database maintenance

How to perform Magento database maintenance

This article examines the various ways to maintain an efficient Magento database, even when large in 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 database. Sadly, this is not the only way your database can become large and sluggish. Maintaining the 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 database.

Log cleaning

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.

You can 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 with administrator interface

  1. From the Magento administrator interface, go to System > Configuration.

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

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

  4. Click Save Config.


You can either configure the shell utility log.php  as a cron job or run manually to clean on-the-fly. 

  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 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.

  1. Open the database in phpMyAdmin via the SiteWorx control panel.

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


  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.

Regular maintenance

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.  

Article Rating (23 Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
What is the “Client denied by server configuration app/etc/local.xml” error?
Added on Thu, Sep 10, 2015
How to improve Magento maintenance mode
Added on Mon, Feb 24, 2014
How to configure CDN access for Magento
Added on Thu, Sep 17, 2015
How to improve the security of your Magento store
Added on Wed, Nov 12, 2014
How to configure the Magento Product Importer (Magmi)
Added on Mon, Jan 26, 2015
How to set the return-path email within Magento
Added on Thu, Oct 30, 2014
What is Magento?
Added on Thu, Sep 10, 2015
What is Turpentine?
Added on Thu, Aug 28, 2014
How to change Magento base URLs
Added on Fri, Jun 28, 2013
How to configure the Nexcess CDN with Magento
Added on Fri, Aug 2, 2013