Dec 8, 2021 Val Kelmuts 7 min read
A website performance is judged by how quickly it can load and render content in a browser, as well as respond to user interactions. Better performance makes a website more accessible, creates a stress-free shopping experience, and helps achieve high rankings on Google.
Magento’s robust performance and flexible architecture make it the ideal choice for small and large businesses alike. The credit for its performance and flexibility is owed in part to its database system that employs an Entity-Attribute-Value (EAV) architecture. This EAV data model allows for efficient data storage and facilitates the introduction of custom attributes to stores without risking damaging the database structure.
However, Magento’s database system can sometimes become a hurdle to its performance. In this post, we’ll give you the lowdown on Magento 2 database optimization to help you understand when you need it and how to go about it.
Magento is designed to support ecommerce stores with large catalogs and high order volumes. Everything from its modular architecture to database model ensures extensibility without compromising performance.
If you’re struggling with a slow Magento store, it’s unlikely that there’s something wrong with the platform itself. Often, the reason for a sluggish Magento store is a lack of server resources, including the following:
Suppose you’ve ensured that your store has adequate resources available. In that case, the next place you should look is its caching configuration. Magento stores work best when using Varnish cash for full-page caching. If you’re still using the in-built caching system, you’re guaranteed to struggle with a slow website.
If you find that your website performance doesn’t improve even after providing adequate resources and configuring caching correctly, the bottleneck is probably either PHP or your website’s database.
In theory, identifying and resolving a bottleneck is how you achieve a high-performance store. However, practically, the pursuit of ultimate website performance isn’t as straightforward. You will find that as you resolve one issue, the bottleneck shifts to a different aspect and the cycle keeps repeating itself.
The best approach to tackling this is outsourcing the task to a company that offers ongoing Magento support services to ensure you’re able to identify and tackle bottlenecks proactively.
Alternatively, you could also take it on yourself to optimize your store performance by installing a monitoring tool like New Relic on your server to help you analyze your entire software stack and identify bottlenecks.
The Magento database is rarely the primary bottleneck in its performance. It usually only leads to performance issues when it starts pulling processing power away from PHP or cannot process queries fast enough. This might occur when your website caching is flawless and you’re experiencing large volumes of site visitors and orders.
It would also be useful to review the database to make sure there are no tables left from the previously uninstalled extensions. If you don’t uninstall the Magento extension with all associated tables in the database completely, they continue to consume system memory and place, therefore leading to slow performance.
When you’re confident that your database is limiting your site performance, you can use a free tool like MySQLTuner to check your database for performance and security issues. Using this tool is a great way to quickly analyze nearly 300 vital metrics like table_open_cache and table_cache hit rate that strongly influence your database performance.
MySQLTuner also provides recommendations based on your database configuration to help you optimize performance using the my.cnf file. Although this is an excellent way to approach.
Magento 2 database optimization initially, hiring a Magento support company and leaving it to the experts is a much safer way to optimize your website performance.
1. Check your hardware
Before you start looking into making software-related changes in your setup, you should ensure your hardware isn’t the bottleneck leading to a slow database. This is because all the software optimization in the world won’t compensate for hardware issues.
Frequent hardware-related problems that lead to poor performance include:
The recent rise in the popularity of containerization has resulted in merchants seeking to implement a split database system in their stores and expecting it to reduce the load on a single database, thereby improving efficiency.
However, this approach only works out of the box for Magento Commerce users. Even then, something as simple as a synchronization issue between the Slave and Master database can slow performance significantly.
2. Consider using a different database management software
MySQL is one of the most utilized database solutions on the market today. It uses a client-server architecture that’s perfect for environments where an application and its database reside on the same system.
It’s capable of supporting heavy-load systems and is therefore used by leading companies like Netflix, Uber, and Amazon. However, if you find that MySQL isn’t cutting it for your store, you can opt for alternatives like MariaDB and Percona.
MariaDB
MariaDB is an open-source fork of MySQL that offers an identical database structure. Its developers also carry out a monthly code merge with MySQL to maintain drop-in compatibility. Even Magento supports using MariaDB as its database system.
Adobe provides a detailed list of all compatible versions for merchants to reference when considering upgrading or building a new store. However, it’s worth mentioning that while you can switch from MySQL to MariaDB without risking downtime, you’re likely to run into compatibility issues if you plan on switching from MariaDB to MySQL.
Percona
Although Percona isn’t as popular as MariaDB, it is also a fork of MySQL that offers superior performance capabilities for stores tackling large-scale datasets. It provides performance-oriented features like XtraDB in place of InnoDB and offers numerous ways to monitor performance.
Percona’s base code offers more similarity to MySQL, reducing the risk of running into compatibility issues. It also includes a set of open-source MySQL tools that simplify database administration for developers.
3. Optimize flat catalog for your Magento version
Magento’s use of the EAV model to store catalog data allows its database tables to grow vertically instead of horizontally. However, this model had a reputation for creating long and complex SQL queries that slowed down websites.Therefore, Magento allowed merchants to switch to using a flat catalog model from the admin panel.
The flat catalog model creates new tables on the go, reducing the number of calls Magento requires to fetch data, improving the efficiency of indexing data and the processing speed of price rules. However, knowing when to use a flat catalog is crucial to leveraging its benefits.
The use of a flat catalog to boost Magento performance has been the go-to strategy for many Magento developers and merchants for years. However, since the release of Magento version 2.1.X, Adobe advises against using a flat catalog as it leads to performance degradation and indexation issues.
Suppose you’re using a Magento version that’s under 2.1.x, you can enable the flat catalog to improve your website performance. To do this, open the Magento admin panel and navigate to Stores > Settings > Configuration > Catalog > Catalog. Then, expand the Storefront section and update the following values and click Save Config:
However, if you’re running Magento version 2.1.x and above, you must disable the flat catalog on your store. This is because the primary advantage of using a flat catalog comes from the performance benefits it offers when filtering category product lists. However, recent versions of Magento use its search engine to execute this task, rendering the flat catalog model ineffective.
Moreover, using a flat catalog on the latest versions of Magento is likely to result in long-running cron jobs that end up getting stuck, increasing the load on the database and causing severe performance issues. Therefore, if you’re running any version over 2.1.x, you should disable the flat catalog to improve Magento performance.
To do this, first, log in to the Magento admin panel and navigate to Stores > Settings > Configuration > Catalog > Catalog. Next, expand the Storefront section and update the following values and click Save Config:
4. Choose Elasticsearch over the default database search functionality
As of Magento 2.4, Adobe requires all merchants to install and configure Elasticsearch as the default catalog search engine. While some view this move as an added complexity to running a Magento 2 store, the performance benefits and functionality Elasticsearch offers make switching over worth the effort.
Using the default database search functionality works fine when running a small store with a few hundred products. However, once the number of products begins to exceed five figures, your site performance is guaranteed to take a hit. Your database is likely to grind to a screeching halt, especially if you’re running a heavy-traffic website that’s serving several concurrent users searching for products.
Additionally, using Elasticsearch allows merchants to introduce advanced functionality like layered navigation filters on category and search results pages. It also offers support for multiple languages, using stop words and synonyms, and allows stores to scale horizontally.
Elasticsearch offloads all search-related queries from the website database freeing it up to perform more vital tasks. It also provides store admins with insights into real-time search data and doesn’t affect store performance when it reindexes.
Magento provides its users with everything they need to create feature-rich, attractive, and fast loading websites. It offers a modular architecture and supports some of the world’s most robust caching technologies like Redis and Varnish, allowing merchants to introduce custom functionality without hurting performance.
Optimizing a Magento store requires a thorough understanding of the platform and its associated technologies. There’s no one-size-fits-all type approach that’s suitable for Magento 2 database optimization.
While using the tips in this post can help you get started, we strongly recommend leaving it to the experts to do what they do best, so you can focus on running your store and growing your business.
Accounting for the time and costs of optimizing your store yourself and analyzing your return on investment after outsourcing the task will help you make an informed decision that’s best for your business.
Val Kelmuts is the Chief Executive Officer and Co-Founder at Staylime, an ecommerce design and development company headquartered in Redwood City, California. Val has 10 years of experience in custom software development, sales management, and business development. He is the Adobe Commerce sales accredited specialist, Shopify business certified expert, and PMI member.
