How to Optimize Your Magento Database Log for Faster Loading

Updated on Oct 6, 2022

Your eCommerce store's success is partially reliant on the enormous databases that enable you to offer goods and services online. These database logs will also be used by the Magento-powered eCommerce website you run. Magento is a complex eCommerce platform and working with it is related to having a strong events logging system that stores all of the collected information into database tables. Your Magento store's speed will start to deteriorate over time as it accumulates a few GB worth of logs, and the utilization of the deployed resources will rise.

Therefore, it's critical that you regularly clear out these logs and optimize the store for quicker loading. This not only greatly improves the efficiency of Magento stores but also lessens query execution delays.

In this post, we will cover three ways in which you can optimize your Magento database.

Note:
Don’t forget to back up your database before launching any kind of optimization process.

Table of Contents:

About Magento logging

For a variety of reasons, such as when a store has a lot of products, Magento databases can become too large. But Magento's logging feature is one of the most typical sources of big databases and performance concerns. Magento records a number of things in the database, including customer and quote data. These logs may balloon in size over time, impacting database performance.

You may greatly reduce database sizes and enhance site speed by cleaning the database log tables and tweaking Magento's log settings. You use Magento's administration interface to optimize log settings. Additionally, Magento comes with a PHP script that can be used from the command line to clean database log tables and determine their size.

Magento Log Types

Мagento 2 doesn’t log data as Magento 1 largely due to the fact that more and more merchants use Google Analytics (GA) for gathering and processing stats, so running your store on it read how to add GA to your Magento 2, and this optimization guide instead.

There are two log types in Magento. The first one is System and Exception log files. They’re disabled by default. So, if you want to have a detailed report on any system events, you’ll need to enable them.

Go to System → Configuration → Advanced → Developer → Log Settings and choose Yes under the Enabled drop-down setting, and click on Save Config:

The logs will be saved in the ‘var/log’ directory, don’t forget to check and clean them periodically, as well as logs from ‘var/report’.

The second type is what we talk about today saves logs on any events related to customers’ activities. The data is saved in Magento Community Edition (CE) database tables listed below:

log_customer
log_visitor
log_visitor_info
log_visitor_online
log_summary
log_summary_type
log_url
log_url_info
log_quote
Index_event
report_event
report_viewed_product_index
report_compared_product_index
catalog_compare_item
dataflow_batch_export
dataflow_batch_import

The tables contain lots of useful info like a customer’s login/out date and time, the URLs they visited as a part of a session, the products they compared, the quotes they made, their actions in-store, in general, etc.

Magento Enterprise Edition (EE) has two more tables, namely enterprise_logging_event and enterprise_logging_event_changes.

All this data accumulates and hampers your Magento if not cleaned timely. Thus, the bigger the store, the more unnecessary info overloads your website. So, here are three ways to solve this.

Clean Magento Database Logs via the Admin Panel

Non-technical store owners who don't want to directly alter the Magento stores database might benefit from this simple solution. To execute a thorough log cleansing for the Magento database, follow these steps:

The steps you need to take here are:

Log in to your Admin panel.
Go to System → Configuration.
Go to the left sidebar, find the Advanced menu and click on System.
Choose the Log tab from the menu and set Enable Log to Yes, and set up a desired automatic log cleaning frequency.
Click on Save Config.

Clean Magento Database Logs via MySQL Client

For individuals who are more accustomed to working with databases, this is the most effective method of cleaning the logs. It is quicker than the native Magento tools and lets you clean additional tables that are not supported by those tools. This process will consolidate the data in those tables, frequently reducing the size of the database by up to 95% and significantly cutting query times.

Enter the phpMyAdmin panel from your hosting control panel.
Enable the checkboxes for the next tables with logs:

log_customer
log_visitor
log_visitor_info
log_visitor_online
log_summary
log_summary_type
log_url
log_url_info
log_quote
Index_event
report_event
report_viewed_product_index
report_compared_product_index
catalog_compare_item
dataflow_batch_export
dataflow_batch_import

Add enterprise_logging_event and enterprise_logging_event_changes if you use EE.

Go to the action drop-down With the Selected menu at the bottom of the page and choose Empty.
Then click Yes on the appeared confirmation page. You now truncated the selected tables.
Now go to the top of the page and click on the Structure tab. Tick the same tables you’ve just truncated, and under the With Selected list, click on Optimize.

Clean Magento Database Log Tables Using the Command Line

You can make the Magento database cleanup through the log.php file in Magento /shell. It can be run both manually and by using a cron job.

When you run the cleaning manually, do the following:

Go to the root directory (terminal) and use the command:

php -f shell/log.php clean

See the results:

php -f shell/log.php status

It may take some time to process your request, depending on how long ago it took the last time.

Note:
You cannot remove data from all the log tables using this method, as it removes the visitors’ chosen data by their IDs only.

Disable Magento Logging

If you don’t want to decrease your Magento performance, don’t need the customers’ data gathered by Magento, or don’t want to check the table logs statuses all the time, you can disable logging to the database.

By the way, if you stop logging in from the Magento admin panel, it doesn’t solve the problem in full. To stop this completely, follow the next steps.

Open the app/etc/local.xml file.

Paste the next patch before the </config> tag, and then save the local.xml file.

Navigate to System → Configuration → Advanced → Disable Modules Output and choose Disable for Mage_Log.

Flush your Magento cache.

Note:

Disabling your Magento logging is not always the way out. When you decide to disable the logging, please, take into consideration the work of your extensions.

Conclusion

Database log cleaning is a must-have for your Magento good performance. Even if you decide to migrate from MySQL to another database engine, what you need to remember is that if you haven’t configured the automatic deletion of logs, you should do it manually.

We hope you find this article useful. Discover more about FastCloud - the top-rated Hosting Solutions for personal and small business websites in four consecutive years by the HostAdvice Community!

Magento Hosting

Free Installation
24/7 Magento Support
Hack-free Protection
Fast SSD Storage
Free Domain Transfer
Free Magento Transfer
Immediate Activation
Free Cloudflare CDN