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

Share

Magik database backup extension – which will automatically backup your database

  • Use phpmyadmin and take the entire dump on your system
  • Use Magento’s built in profilers to export entire database.
  • How To Clean Up Your <a href=Magento Store’s Database Logs For Faster Performance ” width=”300″ height=”133″ />

    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.

    Leave a Reply

    You must be logged in to post a comment.