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:
- — Servers located far from the users
- — Slow CPUs
- — Low bandwidth servers
- — Limited RAM
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 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.
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:
- — Update Use Flat Catalog Category to Yes. (If required, uncheck the Use system value checkbox.)
- — Update Use Flat Catalog Product to Yes.
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:
- — Update Use Flat Catalog Category to No. (If required, uncheck the Use system value checkbox.)
- — Update Use Flat Catalog Product to No.
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.