How to perform Magento database maintenance
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.
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:
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 with administrator interface
- From the Magento Admin Panel, select System > Configuration (Figure 1).
Figure 1. Magento Admin Panel; System > Configuration.
- On the left, locate the Advanced menu and click System (Figure 2).
Figure 2. Advanced menu; System selection.
- From the System panel, click Log, and from the Enable Log Cleaning drop-down list, select Yes (Figure 3).and configure the Save Log for 15 days:
Figure 3. System panel; Log > Enable Log Cleaning > Yes.
- In the Save Log Days field, enter 15 (Figure 3).
- Click Save Config (Figure 4).
Figure 4. Save Config button.
You can either configure the shell utility log.php as a cron job or run manually to clean on-the-fly.
- From the Magento root directory, type the command:
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.
- Open the database in phpMyAdmin from the SiteWorx control panel.
- 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
- At the bottom of the page, from the With Selected list, and select Empty.
- A confirmation screen will appear. Click Yes. This will truncate all of the selected tables.
- Click the Structure tab at the top of the page.
- Select the same tables as you did in Step 2, then under the With Selected list, select Optimize.