Our New Contus Dart - Last Mile Delivery App is Just Out!Check it out

​​Magento Database Maintenance & Optimization By Log Cleaning For Better Performance

“Magento database maintenance paves way in outsmarting the shortcomings of the platform’s extensive logging for gathering behavioral data like compared products, recently viewed products, visitor information and many other.”

This is one of the key reasons for Magento being a front runner among eCommerce platforms but on the flipside it is a technical pain are which has to be addresses sincerely and failing will result in:

  • Latency of query execution
  • Pulls down performance of the store
  • Increase the usage of the deployed resources

Tidying up things in your Magento database can save more MySQL space and ultimately, a fly weight Magento DB yields better performance results.

From here it will be a walk-through on how Magento database cleanup, how Magento log cleaning can be done both manually and automatically and other Magento database maintenance ideas. Here are the 3 Magento database optimization and cleanup tips that guarantee blistering page loading speeds.

Magento Database Logs Cleaning

Cleaning logs from a Magento website’s DB can be done in three ways:

  • Log cleaning using Magento admin interface
  • Manual cleaning with phpMyAdmin or MySQL Manager
  • Through log.php in the (../shell directory)

All the three practices improve magento database performance.

Note: Among the three above mentioned Magento log cleaning practices, you’ll get to manage the following tables only when the cleanup is performed using Magento admin interface.

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

Magento Log cleaning through admin interface:

Step 1: Select System -> Configuration logging in from Magento admin Panel

log in to your magento admin panel

Step 2: Find the ‘Advanced’ menu in the left side and click ‘System’ from the dropdown

system advanced setting

Step 3: Under System locate and click ‘Log’

see log option

Step 4: Under Log option, locate Enable Log Cleaning drop-down list, select Yes

Cleaning Drop Down List

Step 5: Under “Log Cleaning”, change “Enable Log Cleaning” status to ‘YES’.

Step 6: Changes the ‘Save Log’ for 15 days or select the time you need to clean your log

Step 7: Click “Save Config”

This will help you clear Magento database cache or delete Magento database cache.

Magento Manual Log Cleaning Using phpMyAdmin or MySQL Manager

Magento database log cleaning by manual effort is considered as the most convenient way to clear Magento db cache by those who are familiar working with databases. Manual database optimization method is quicker as well and you’ll be able to clean any log you want. You can also clean other tables which are not included in default Magento log cleaning using manual cleaning method.

This procedure drastically decreases the database size (almost up to 95%) and reduces latency of query processing.

Step 1: From SiteWorx control panel, open the database in phpMyAdmin

Step 2: Check the following tables available in the frame located in the right side.

manual cleaning with php my admin

Step 3: Scroll to the bottom. Select the ‘With Selected’ drop down menu and click Empty.

Click Yes in the confirmation pop up that appears.

Step 4: Scroll to the top of the page and click the ‘Structure’ tab.

Step 5: Now repeat step 2 by selecting the same tables you selected previously. Go to ‘With Selected’ drop down menu and click ‘Optimize’.

That’s it! Magento system log cleaning is done.

Magento Database Log Cleaning Through log.php in the (../shell directory)

Magento enable log cleaning can be done using the log.php file within the Magento /shell folder. The Magento database logs cleaning can be run manually or through a cron job.

Step 1: From the Magento root directory, type the command: php -f shell/log.php clean

Step 2: Use the “–days” to mention the number of days of history to be saved.

Keeping in Par with MySQL Version Updates

Yet another simple but often overlooked method of Magento database cleanup is updating the MySQL version. Regular updates are being rolled on and as a website owner, it is your job to keep in track of the Magento version you are using and the appropriate MySQL database version. This is an indirect way which will help to improve Magento database performance.

Older versions of MySQL database for a newer version of Magento CE (Community Edition) or EE (Enterprise Edition) is obviously going to be less efficient in Magento database maintenance.

Magento CE and EE support the following MySQL versions:

  • Magento CE 1.9.1 and Magento EE 1.14.1 support MySQL versions 5.0.2 through 5.6.x.
  • Magento CE versions 1.8.0.0–1.9.0.x support MySQL versions 4.1.20–5.5.x.
  • MySQL 5.6 is supported only by Magento CE 1.9.1 and EE 1.14.1

Note: The system prerequisites for Magento CE and EE are different.

MySQL Performance Tuning Settings – “innodb_buffer_pool_size”

One of the first and foremost things to do in Magento database optimization using MySQL performance tuning is to optimize the innodb_buffer_pool_size. Though this is not helpful to clear Magento database, it indirectly helps in achieving better Magento database performance.

The purpose of innodb_buffer_pool_size is that, it caches the data and indexes happening in a website. So, having the buffer pool size as large as possible will save disk space by using memory for a majority of read operations.

The usually recommended values for innodb_buffer_pool_size for MySQL 5.7:

  • 5-6 GB Cache of data and indexes (8GB RAM)
  • 20-2 5GB Cache of data and indexes (32GB RAM)
  • 100-12 0GB Cache of data and indexes (128GB RAM)

Expert thought: We have experienced Magento databases getting reduced by quarter of their size after performing the above mentioned Magento Database Maintenance ideas. We recommend you to perform such operations on a regular basis to achieve blistering loading speeds.

Build eCommerce Website
Setup Your eCommerce Store

Leave a Reply

10 Comments on "​​Magento Database Maintenance & Optimization By Log Cleaning For Better Performance"


Guest
Anuj Karna
3 months 11 days ago

There are many things Magento does well, but database management isn’t one of them. Over the last couple of days I’ve searching for magento database maintenance and this post is clearly explained about magento database log cleaning. Thanks for your informative post.

Guest
Mathew Nowell
3 months 18 days ago

Magento maintains several log systems for tracking purpose like customer access, products viewed, products compared etc. This article explained clearly on how to perform magento database by log cleaning. Good information. Thanks.

Guest
Giles Teddy
3 months 24 days ago

Hi Masi, I read this article about maintaining and cleaning old Magento cache and log records. It’ll greatly improve Magento database performance. Very often Magento database grows big in size due to large log tables and it might be a good idea to regularly perform a maintenance clean up.

Guest
Nick Dell
4 months 1 day ago

Its really fantastic tips about magento database maintenance and the Innodb part is news to me. Good collective tips on magento database log cleaning. Thanks Masi.

Guest
Charles Russ
4 months 8 days ago

Magento is highly stable eCommerce system but it’s database is not as efficient as it should be. So I highly recommend, Whoever need magento database maintenance, this blog is a handy one. It has lessened my concerns about magento database log cleaning. Thank You. Keep it up.