Why MySQL Is a Bad Choice for Your Magento Store?

When we are looking into Magento optimization, we are not discussing database optimization as much as we should. One of the reasons – your Magento speed is rarely bottlenecked by database performance. Most of the time it’s something else: inadequate server environment, underpowered hosting capabilities, unoptimized frontend or backend.

Does MySQL Really Impact Magento Performance?

Database issues are not a common problem for Magento store owners because Magento often bottlenecks on CPU or RAM. Sure, if you have an HDD-built database, the database performance will become an issue. But most of the time it’s not.

Why then are we talking about Magento database optimization? Shouldn’t we focus on something else instead? Well, sometimes Magento does have database issues. If database queries take too long or create issues with search performance, MySQL becomes a real bottleneck.

Let’s take a look at the two most common challenges that affect MySQL performance.

MySQL Is Bad at Magento Search Performance

By default, Magento uses MySQL as a search engine which means your MySQL engine will need to handle both search requests and day-to-day DB queries. Initially, this load is fine but it becomes an issue with scaling.

Overall, MySQL performance as a default search engine is okay if we are talking about stores with moderate foot traffic. It's enough for traffic to hit higher numbers to make Magento MySQL search too slow .

Why MySQL search is slow? Multiple reasons:

• the sheer size of data your engine needs to process impacts its performance,
• MySQL doesn’t know how to scale in high load situations (multiple simultaneous searches will degrade search performance for everyone involved),
• MySQL needs to balance search load and Magento store queries,
• MySQL can’t handle fancy stuff well like suggestions, third-party extensions, searching complex queries or parts of strings within the whole string.

Another issue with the MySQL search engine is that it’s a barebones tool. If you want your customers to have smart and fast suggestions, search queries with typos, and prioritization features, MySQL is not the best choice.

We suggest that you switch to Elasticsearch for more complete user experience. This is also a good idea if a significant part of your customers actually use the store search to find what they need. Imagine how a smarter search could improve your sales!

In order to change search settings in Magento 2 Admin Panel, go to Stores > Settings > Configuration > Catalog > Catalog Search and find the Search Engine field. Switch to Elasticsearch of the appropriate version.

MySQL Struggles to Scale Well for Large Stores

Large Magento store owners (say, 50,000+ SKUs or more) will find out that while MySQL was a solid choice once, right now it seems to struggle to give them the same level of performance as before. MySQL is a standard choice because of how robust and well-documented it is but once you hit a certain threshold in dataset sizes and load values, MySQL begins to show its limits.

It’s no longer the best choice for stores that experience an influx of users during peak hours, campaign events, or spikes in paid ad traffic. Basically anything unexpected or extreme can make your MySQL engine croak. MySQL suffers from severe concurrency performance drops, write stalls in certain buffer pool-created scenarios, log capacity issues, synchronous flushing, etc.

In this situation, it would be wise to see what kind of alternatives are available for your unique load challenges.

In general, the majority of Magento store owners lean either towards MariaDB or Percona. Most of the time the choice of one platform or the other is dictated by the features and advantages each database engine has. So how do you speed up Magento database performance ?

Should You Go With MariaDB or Percona?

MariaDB specializes in advanced Magento sharding features such as Spider storage and Galera clusters support (for XtraDB and InnoDB only). For example, the Galera cluster offers 0 transactions lost feature – a vital advantage for Magento stores which require high reliability to function properly even under heavy loads. Galera cluster offers minimal client latency and decent read/write scalability, too – awesome perks for scale-conscious Magento owners.

Another prominent feature of MariaDB is horizontal replication between multiple sources. Horizontal replication allows the engine to write data to multiple instances and then combine them into a single server.

Sure, MySQL also has this feature. It’s just that MariaDB went a few steps further and developed a more intricate and scale-proof scheme of horizontal replication.

Among other MariaDB perks are connectivity and scaling improvements and performance optimizations at higher concurrency levels.

Percona is another solid choice for Magento store owners. It’s much less popular than MariaDB but offers a few nice things for performance- and security-minded folks out there. Percona development is focused primarily on scaling and performance issues so it’s not surprising that the engine performs so well on enterprise-level tasks (successfully running massive 32- and even 48-core CPUs with high threadpool scaling, good query logging, and I/O count features).

In addition to performance, Percona is famous for its security capabilities. The engine features advanced encryption, smart user isolation, and audit logging to cater to even the most paranoid of Magento store owners.

So which of the two is best for you? Every store is unique in its own right so it’s hard to give advice based on assumptions. Overall, we recommend that you use Percona for large-scale stores, highly-performant use cases, and huge dataset management.

MariaDB is more about fancy perks and quality-of-life features that power users will be happy to see in their stores. Whichever you choose, it’s still a significant upgrade over the default MySQL option.

Get Rid of MySQL. Speed Up Your Store

MySQL can be a useful tool for Magento store owners but once you grow up and become a large store, MySQL can become a bottleneck for your business. Upgrading to a more capable engine is a great way to speed up the store and offer customers a better user experience.