Magento Database Optimization [Complete Guide]

Table of Content

flush Magento cache

Magento database optimization is the step you should start from on the way to your website performance enhancement. Working with this complex e-commerce platform, you know that it has a strong events logging system that stores collected info in Magento 2 module version database tables.

Now imagine that you run a huge store and all the logs haven’t been cleaned up for ages. Will this influence your website performance? A definite yes, we think.

In this post, we’ll cover how to optimize your Magento database in 3 simple ways.

 Back up your database before launching any optimization process.

There's a lot to be said about Magento website optimization. But we have one answer to all Google Page Speed Insights suggestions - M2 Google Page Speed Optimizer: minifies/merges JS, CSS, HTML implements lazy loading support webP speeds up loading on mobiles and desktop. To learn more about Magento 2 database optimization, check out this guide.

Fast fact-checking

Мagento 2 doesn’t log data as Magento 1 largely due to the fact that more and more merchants use Google Analytics 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.

First things first, so, 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. For this, go to System>Configuration>Advanced>Developer>Log Settings and choose Yes under the Enabled drop-down setting, click on Save Config:

Now 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 customer’s activities. The data is saved in Magento CE database tables we list 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

As you may have guessed, the Magento user tables contain lots of useful info like a customer's log in/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, and so on.

Magento 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.

#1 Cleaning Magento Database Logs: Use Default Settings in Admin Panel

Step 1: Log in your admin panel.

Step 2: Go to System>Configuration.

Step 3: Go to the left sidebar, find Advanced menu and click on System.

Step 4: Choose the Log tab from the menu and set Enable Log to Yes and set up a desired automatic log cleaning frequency:

Step 5: Click on Save Config.

#2 Cleaning Magento Database Logs: Use MySQL Client or CLI

Via MySQL Client

Step 1: Enter the phpMyAdmin panel from your hosting control panel.

Step 2: 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

+ enterprise_logging_event and enterprise_logging_event_changes if use EE.

Step 3: Go to the action drop-down With Selected menu at the bottom of the page and choose Empty. Then click Yes in the appeared confirmation page. Thus, you’ve just truncated the selected tables.

Step 4: 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. That’s it.

#3 Cleaning Magento Database Logs: Use shell/log.php

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

If running the cleaning manually, then...

Step 1: Go to the root directory and use the command: php -f shell/log.php clean

Step 2: In order to specify the number of days of logging history to be saved, use the ‘--days’ line:

$ php -f shell/log.php help 

Usage: php -f log.php -- [options]
php -f log.php -- clean --days 1

clean Clean Logs
--days <days> Save log, days. (Minimum 1 day, if defined - ignoring system value)
status Display statistics per log tables
help This help

Step 3: See the results php -f shell/log.php status. It may take some time to process your request, it depends on how long ago you did it last time.

Note that we can’t 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 (a) don’t want to decrease your Magento performance, (b) don’t need the customers’ data gathered by Magento, (c) don’t want to check the Magento 2 alter table logs statuses all the time, you can disable logging to the database.

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

Step 1: Open the app/etc/local.xml file;

Step 2: Paste the next patch before the tag:

<frontend>
<events>
<controller_action_predispatch>
<observers><log><type>disabled</type></log></observers>
</controller_action_predispatch>
<controller_action_postdispatch>
<observers><log><type>disabled</type></log></observers>
</controller_action_postdispatch>
<customer_login>
<observers><log><type>disabled</type></log></observers>
</customer_login>
<customer_logout>
<observers><log><type>disabled</type></log></observers>
</customer_logout>
<sales_quote_save_after>
<observers><log><type>disabled</type></log></observers>
</sales_quote_save_after>
<checkout_quote_destroy>
<observers><log><type>disabled</type></log></observers>
</checkout_quote_destroy>
</events>
</frontend>

And save the local.xml file.

Step 3: Navigate to System>Configuration>Advanced>Disable Modules Output and choose Disable for Mage_Log:

Step 4: Flush your Magento Cache:

You may even turn to more radical methods and say, hack the core code.

WARNING: Disabling your Magento logging is not always the way out. Here is a simple example, our extension Magento Automatic Related Products uses the history of viewed and compared products as one of the main sources for its proper functioning, though it also provides a possibility to use other sources. So, having decided to disable the logging, please, take into account the work of your extensions.

Sum-up

Remember that database log cleaning is a must-have for your Magento good performance. You can always migrate from MySQL to any other database engine, say, it can be Magento 2 and MariaDB, Amazon Aurora, Magento Percona or anything else. The only thing to remember is that if you haven’t configured automatic deletion of logs, do not forget to do this manually.

Still have questions?

Feel free to pose them below! Or check our server configuration service.

P.S. Special thanks to Dmitry Smolik and Alexander Seravin for the expertise that helped us bring this post to life.

June 20, 2019
June 21, 2019
June 19, 2019
Comments
George
July 16, 2019
Really well presented article as usual. Just so happened I was researching this topic and low and behold it drops into my inbox. Great advice and very simplified for idiots like me.
Reply
Polina Litreyeva
July 29, 2019
Hello, George! Thanks for reading and leaving your comment. We are glad to be helpful.
Shri
December 7, 2020
Even though this is tagged as Magento 2, the screenshots are of Magento 1 and I came here from Google search for Magento 2 Database optimization. So, if above doesnt apply completely to Magento 2, it might cause issues for viewers who try it out for Magento 2
Reply
Polina Kratovich
December 23, 2020
Hi, Shri. Thanks for your careful reading and valuable notice. Pity the article hasn't met your expectations. To get more info about Magento 2 optimization, please, check out this <a href="https://amasty.com/blog/optimize-your-magento-1-and-2-speed/">guide</a>.
Leave your comment

Your email address will not be published

This blog was created with Amasty Blog Pro

This blog was created with Amasty Blog Pro