ECommerce Insights Blog

Stay on top of it all and get ahead with useful articles, how-tos, tips and tricks on e-commerce.

How To Clean Up Your Magento Store’s Database Logs For Faster Performance

Product List Page with Category Slider

Although, Magento is highly stable eCommerce system but it’s database is not as efficient as it should be. Every successful eCommerce store needs a sizable database to sell myriad or products and services online. We have gained major performance boost by cleaning logs regularly. This helps in reducing latency of query execution and hence improves performance. Regular database cleansing can tidy up Magento database and all unused, cluttered or over encumbered records are sorted out.

The technique i am about to share with you will work perfectly but still i will recommend to save your database backup before doing anything (in case something goes wrong. To take backup of your current Magento store you can do any of the following things:

  1. Use our Magik database backup extension – which will automatically backup your database
  2. Use phpmyadmin and take the entire dump on your system
  3. Use Magento’s built in profilers to export entire database.

Now, you have your database properly backed up lets move to manage logging tables. In Magento commerce open source community edition and even in enterprise edition there are several tables which captures logs of specific events like customer accesses, product comparisons and some other eventual logs. Although, there is a self cleansing mechanism pre-built in Magento which cleans all these logs periodically but the irony is that this important feature is disabled by default. Most of the store owners don’t even know about this feature and they never turn it ‘ON’. Since, the cleansing mechanism is turned OFF on a busy store, it will pile up huge sum of records in Magento store’s database which eventually slows the Magento store down.

There are two different options to clean up your logs:

  1. Clean your logs via Magento Admin Panel “Log Cleaning”
  2. Clean up your database tables directly via phpmyadmin or SSH MySQL.

The following tables are used & managed by Magento’s log cleaning functions:
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

Cleaning Logs via Magento Admin Panel

This method is easier for non technical store owners who don’t want’ to mess directly with the Magento store’s database. To activate log cleaning option in Magento just do the following:

  1. Log on to your Magento Admin Panel.
  2. Go to System => Configuration
  3. On the left under Advanced click on System (Advanced = > System)
  4. Under system you will see “Log Cleaning” option
  5. Fill the desired “Log Cleaning” option values and click Save.

Cleaning Logs via phpMyAdmin

If you are comfortable with mysql and queries then this method is more efficient and quicker than default Magento Log Cleaning tool. This method also allows your to clean whatever you like, you can even clean tables which aren’t included in default Magento’s Log Cleaning tool.

  1. Open the database in phpMyAdmin
  2. In the right frame, click on the boxes for the following tables:
  3. 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
  4. Look to the bottom of the page, then click the drop down box that says “with selected” and click empty.
  5. Click Yes on confirmation screen, and this will truncate all the selected tables.

Keep in mind that we are here to empty (Truncate) selected tables are not drop them. Be very careful when you do this.

Performing this regularly will definitely improve your Magento store’s performance and efficiency. You can setup up scripts to do this automatically at regular intervals too using “CRON”.

I would love to hear your thoughts and experiences of cleaning your Magento store’s logs and tell me how much speed improvement you have noticed. Please leave me a comment and let me know. Subscribe our RSS to receive latest updates on Magento development.