​​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

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

Step 3: Under System locate and click ‘Log’

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

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.

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.