Magento Database Maintenance

Overview
This article examines the various ways to maintain a efficient Magento database, even when large in size.

Magento does many things well, but maintaining a efficient database is not one of them. has many products are a good reason to a large database. Sadly, this isn't the only-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 have a mechanism for cleaning these logs regularly, but unfortunately, this feature was disabled by default and most Customers does not enable it. There is 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 is managed by Magento ' s log cleaning function:

Log_customerlog_visitorlog_visitor_infolog_urllog_url_infolog_quotereport_viewed_product_indexreport_compared_ Product_indexreport_eventcatalog_compare_item

Log Cleaning via 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:
   

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

The most efficient-from-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 isn't included in those tools. This procedure would 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:

   dataflow_batch_exportda Taflow_batch_importlog_customerlog_quotelog_summarylog_summary_typelog_urllog_url_infolog_visitorlog_visitor_ Infolog_visitor_onlinereport_viewed_product_indexreport_compared_product_indexreport_event 

   
   

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

4. A Confirmation screen would appear. click  Yes . This would truncate all of the selected tables.

5. Click the  structure  tab at the top of the page.
6. Select the same tables as you do in step 2, then under the  with selected  drop-down Me nu, 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 be Gins increasing and you are already implemented the other performance tweaks.

-See more At:http://docs.nexcess.net/article/magento-database-maintenance.html#sthash.vqetkae8.dpuf

Magento Database Maintenance