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.