It’s quite easy to build and manage an online business store with e-commerce platforms like Magento. And, website speed becomes a key factor for success.
Luckily, Magento provide different ways to enhance the performance and efficiency of Magento website. A popular one is to optimize Magento database.
That’s why, we often get requests from our customers to “optimize Magento database” as part of our Server Management Services.
Today, we’ll see how our Support Engineers optimize database in Magento based on the frequent request from our clients.
Reasons on why to optimize the database?
Like any dynamic web application, Magento also depends heavily on the database.
When it comes to large databases, one of the most common cause is its impact on site performance.
However, it’s good to optimize Magento database due to the following reasons including:
– Clean database log tables.
– Manage the database size.
– Improve site performance.
– Enhance access speed to retrieve data.
From our experience in managing websites, we always see that Magento website with optimized database always perform better.
Top 3 ways to optimize Magento database
Now, let’s see how we execute the steps to optimize Magento database.
Recently one of our customers approached us claiming that he uses Magento version 1.9.2.1 and have 30000 products in his store so that his database size raises approximately to 3 GB. In order to improve site performance, he wanted to optimize his database.
There are different ways to optimize Magento database. In any case, our Support Team always takes a website and database backup, This comes handy in case of any failures.
Let’s see how our Technical Engineers took the necessary steps to optimize database one by one.
1. Log cleaning from the Admin Panel
The first step was to do log cleaning. To optimize log settings for Magento store, we follow the below steps.
1. First, we log in to Magento Admin Panel and select System > Configuration.
2. Then we click System under the Advanced menu.
3. And under the System panel > Log, we select Yes in Enable Log Cleaning field.
4. Next, we enter value 15 in the Save Log Days field. It saves log file for every 15 days.
5. Finally, click Save Config.
2. Using log_cleaning script
Similarly, we often set manual cron script that runs periodically for log cleaning.
For this, we took the following steps.
1. First, we log in to the account using SSH.
2. Then, change the directory in which Magento is installed.
cd ~/public_html/shell3. We execute the following command to clean log files.
php -f log.php clean --days 10Thus, it keeps log information in the database and deletes the rest. We also suggest to set this a cron job so that server executes this at regular intervals.
3. Manual cleaning with phpMyAdmin
Likewise, this is also one of the most efficient ways to clean log files.
This procedure truncates the data inside tables, thus decreasing database size and reducing query times.
To do this we follow the necessary steps below.
1. First, we log in to the control panel and open the database in phpMyAdmin.
2. In the right menu, we select the checkbox 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_event3. At the bottom, select Empty under With Selected list.
4. Next, click Yes. This truncates all selected tables.
5. Then we click the Structure tab and repeat step 2.
6. Finally, under the With Selected list, choose Optimize.
That fixes the problem and the customer’s database size reduced. As a result, his site performance also increased.
[Having problems to optimize Magento database? We’ll fix it for you.]
Conclusion
In short, Magento logs several items such as customer details, quote information, etc. in the database. Over a period of time, these logs become larger and affect database performance. So, its really important and a must to optimize Magento database. Today, we saw how our Support Engineers optimize Magento database for maximum speed and performance.
0 Comments