Magento database maintenance

Magento database maintenance

Overview
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 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:

log_customer
log_visitor
log_visitor_info
log_url
log_url_info
log_quote
report_viewed_product_index
report_compared_product_index
report_event
catalog_compare_item

Log cleaning via admin

  1. In the Magento Admin, 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.

Log.php

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

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

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

    dataflow_batch_export
    dataflow_batch_import
    log_customer
    log_quote
    log_summary
    log_summary_type
    log_url
    log_url_info
    log_visitor
    log_visitor_info
    log_visitor_online
    report_viewed_product_index
    report_compared_product_index
    report_event


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

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

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 (10 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 Magento?
Added on Wed, Sep 11, 2013
Running a Magento reindex
Added on Fri, Nov 15, 2013
What are some useful Magento scripts?
Added on Mon, Oct 13, 2014
What is Turpentine?
Added on Thu, Aug 28, 2014
Improving the Magento Maintenance Mode
Added on Mon, Feb 24, 2014
Multiple storefronts with Magento
Added on Mon, Jul 29, 2013
What is Varnish?
Added on Thu, Aug 28, 2014
How to disable caching within Magento
Added on Tue, Nov 5, 2013
Upgrading to Magento 1.8.0.0
Added on Mon, Dec 9, 2013
Setting up Nexcess CDN with Magento
Added on Fri, Aug 2, 2013