Magento Database Maintenance(Magento的数据库维护)

Magento does many things well, but maintaining an efficient database is not one of them. Having a lot of products is a good reason to have a large database. Sadly, this isn't the only way your database can become big and sluggish. Maintaining the database through log cleaning can result in a dramatic improvement in site performance and latency. This guide explains how to clean up a Magento database that has grown too large for its own good.  



NOTE: Before performing any operations on the database, please make a backup.(注意:数据库执行任何操作之前做好备份。)

Log Cleaning(登陆清除)

Magento maintains several tables for logging. These tables log things such as customer accesses and which products have been compared. Magento has a mechanism for cleaning these logs regularly, but unfortunately this feature is disabled by default and most customers do not turn it on. 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.
(Magento的维护多个表用于记录这些记录的东西顾客访问和哪些产品进行了比较。 Magento的机制定期清洗这些日志不幸的是这个功能被禁用默认情况下,大多数客户不打开有三种方式通过清洁Magento管理通过log.php.. / shell目录日志通过phpMyAdmin或MySQL客户端手动清理这些表。)

The following tables are managed by Magento's Log Cleaning function:下面的表格管理Magento的日志清洗功能:)



Log Cleaning via Admin(通过管理员登陆清除)

  1. In the Magento Admin, go to System > Configuration.
  2. In the left menu under Advanced click on System.
  3. Under "Log Cleaning", change "Enable Log Cleaning" to YES and configure the Save Log for 15 days:
  4. Click "Save Config"


The shell utility log.php can be configured as a cron job or run manually to clean on-the-fly.

  1. From the Magento root directory, type:
    php -f shell/log.php clean
  • the –days switch can be used to specify how many days back 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 a couple of other tables not included in those tools.

  1. Open the database in PhpMyAdmin via the Siteworx Control Panel.
  2. In the right (main) 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 that says "With Selected:" and select "Empty".
  4. A confirmation screen will appear. Click "Yes". This will truncate all of the selected tables.

Perform Regularly(定期执行)

We've seen 2GB+ databases drop to a quarter of their size after cleaning the logs, so it is very important that this sort of maintenance is performed regularly. Particularly if your time-to-first-byte latency starts growing larger, and you've implemented the other performance tweaks.

(我们已经看到2GB +的数据库下降季度它们的大小清洗后的日志,所以是非常重要的这种维护定期进行特别是如果时间首字节延迟开始越来越大已经实现了其他性能调整。)