Magento database maintenance
Article Number: 603 | Rating: 4.8/5 from 19 votes | Last Updated: Tue, Oct 28, 2014 at 8:42 AM
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 unfortunately, this feature is disabled by default and most customers do not enable it. 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.
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 via administrator interface
You can either configure the shell utility log.php as a cron job or run manually to clean on-the-fly.
2. Use the –days switch to specify how many days of history 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 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.
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 begins increasing and you have already implemented the other performance tweaks.
There are no attachments for this article.
How to configure CDN access for Magento
Added on Tue, Jul 16, 2013
Setting up Nexcess CDN with Magento
Added on Fri, Aug 2, 2013
How to disable caching within Magento
Added on Tue, Nov 5, 2013
How to find and remove hanging Magento cron jobs with SSH
Added on Thu, Oct 30, 2014
What are some useful Magento scripts?
Added on Mon, Oct 13, 2014
Optimizing Magento performance
Added on Mon, Jul 29, 2013
How to configure the Magento Product Importer (Magmi)
Added on Mon, Jan 26, 2015
What is Magento?
Added on Wed, Sep 11, 2013
How to set Magento file permissions and ownership
Added on Mon, Nov 3, 2014
Running a Magento reindex
Added on Fri, Nov 15, 2013