Optimize Magento Database is one of a very important task of developer who maintain magento site. Let ‘s play around with the number relate to database size and performance because we need to know why we do cleaning magento database to get the best performance before we do it.
For example you are running a magento site have:
– 200 visitors/day.
– 500 page views/day.
– 200 accounts.
– 30 orders/month.
The question is how much can you save monthly in magento size when you optimize magento database by database cleaning? The answer is ~200MB. It ‘s quite big number for me, and how cool is it if we can save it monthly what will release the database engine a lot for every query to database.
Another question is why magento save a lot of database what we don’t need? The answer is because of the tracking. The ecommerce system need to have a very good tracking module what can show everything for user if something wrong happen with their order. And many other reasons why magento have to log a lot. But our topic is not for log, let ‘s talk about that later, now we try to do optimize magento database by clean all of log database to release resource and size of database.
As always, let ‘s do the back up before doing anything relate to database. Here is step by step to clean database log.
Optimize magento database Log Cleaning
Magento maintains several tables for logging. These tables log things such as customer accesses and which products have been compared. Magento has a mechanism for cleaning these logs regularly, but unfortunately this feature is disabled by default and most customers do not turn it on. There are three ways to clean out these tables: via Log Cleaning in the Magento Admin, via log.php in the ../shell directory, and manually via phpMyAdmin or mysql client.
The following tables are managed by Magento’s Log Cleaning function:
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
Let ‘s execute this SQL to clear and optimize them
SET foreign_key_checks = 0; TRUNCATE dataflow_batch_export; TRUNCATE dataflow_batch_import; TRUNCATE log_customer; TRUNCATE log_quote; TRUNCATE log_summary; TRUNCATE log_summary_type; TRUNCATE log_url; TRUNCATE log_url_info; TRUNCATE log_visitor; TRUNCATE log_visitor_info; TRUNCATE log_visitor_online; TRUNCATE report_viewed_product_index; TRUNCATE report_compared_product_index; TRUNCATE report_event; TRUNCATE index_event; TRUNCATE catalog_compare_item; SET foreign_key_checks = 1;
Optimize magento database Log Cleaning via Admin
- In the Magento Admin, go to System > Configuration.
- In the left menu under Advanced click on System.
- Under “Log Cleaning”, change “Enable Log Cleaning” to YES and configure the Save Log for 15 days:
- Click “Save Config”.
Optimize magento database Log.php
The shell utility log.php can be configured as a cron job or run manually to clean on-the-fly.
From the Magento root directory, type:
php -f shell/log.php clean
The –days switch can be used to specify how many days back to save.
Manual Cleaning via PhpMyAdmin.
This is the most efficient way to clean the logs for those more comfortable working with databases. It is faster than the built-in Magento tools, and it allows you to clean a couple of other tables not included in those tools.
- Open the database in PhpMyAdmin via the Siteworx Control Panel.
- In the right (main) frame, select the check box for the following tables:
dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_viewed_product_index report_compared_product_index report_event
- At the bottom of the page, click the drop-down box that says “With Selected:” and select “Empty”.
- A confirmation screen will appear. Click “Yes”. This will truncate all of the selected tables.
Perform Regularly
It is very important that this sort of maintenance is performed regularly. Particularly if your time-to-first-byte latency starts growing larger, and you’ve implemented the other performance tweaks. I already seen a 4GB+ database drop down 2GB+ after cleaning log. It ‘s very nice to get your magento site faster a lot with this simple tasks.
Anyway, optimize magento database by cleaning log is only one way to optimize magento. You can check Speed up Magento post to see all of ways to improve performance.
Notice: Some statistic information will be lost if you clean these log tables (like visitor info, visit URL info…), if you think they are useless with you then feel free to clear. If you are not sure what you delete you need to do the backup before doing these steps.
Can’t believe it, it helped me save 2GB database size and make my magento site work very fast here. I never think I meet this problem and solve it too easy. Thanks for the nice article.
You are welcome, kaka. If you meet any more issue with performance, you can check other post from our blog.
Thanks for the very useful help, you save my time very much. Other than that, I set up a cron job of magento to delete log auto. I think you can add more instruction about that to your article to make it more complete.
Magento already support schedule auto clear log, so we don’t need to set up cronjob.
Nice to know a very easy way to optimize magento by cleaning log. Thanks author.
You are welcome. We are happy to hear that
Excellent thanks – always escaped my notice that magento log cleaning function!
it should be mentioned that by cleaning log/report tables the one looses the visitors history that might be useful for sales analytics
Updated the article, thanks Konstantin G
Although Magento has best, out-standing and terrific e commerce solutions, Magento conjointly take responsibility to boost performance at each level. Not a couple of even most of the folks distressed regarding their site’s performance obtaining worse day by day. within the starting website workgood however currently it take too long to load contents. For more information go to http://magentomonsters.com/how-to-clear-magento-log-data-for-performance-optimization/
very very usefull tutorials …i’m very happy to use this and increased mine website super fast than previous one
thank you so much author
Very usefull thanks
Roulement
Thank you so much.
I had this problem for 1 year and you make my database so clean and fast.
Our problem was solved thanks.
Saved us few seconds of loading time.
What data will be missing after the cleaning? Historical data?
Where is the shell directory for Magento 2.1 or 2.2?
First try to find the tables with most rows & size by using SQL:
SELECT
TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows #",
ROUND(
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
2
) AS "Size (MB)"
FROM
information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name'
AND (
TABLE_NAME LIKE 'log_%'
OR TABLE_NAME LIKE 'report_%'
OR TABLE_NAME LIKE 'dataflow_%'
OR TABLE_NAME = 'catalog_compare_item'
)
ORDER BY TABLE_ROWS DESC
And you can run the truncate operation on the log tables.
For more, please visit: http://bit.ly/30F21Ge