8 Levels of Magento 2 MySQL Optimization and Database Performance Tuning

8 Levels of Magento 2 MySQL Optimization and Database Performance Tuning

8 Levels of Magento 2 MySQL Optimization and Database Performance Tuning

In our Magento optimization guide, we’ve already created a comprehensive list of improvements that you can do for servers, media, and code. This time we would like to add more tips on how to diagnose and troubleshoot Magento database performance issues.

Before You Start

In this guide, we assume you already know your database is the real bottleneck of the system and you don’t need to investigate this issue further, you just need actionable steps on how to optimize your Magento MySQL database.

However, for those of you who are not convinced what part of their store is actually the bottleneck, we would like to offer a piece of advice. MySQL is rarely the cause of your speed issues. If you have properly set up Redis and Varnish cache management, implemented other tips from our Magento optimization guide, then chances are your bottleneck is not in the database.

There is such a thing as a slow database, sure. MySQL can become a real issue when database queries take too long, when you lack indexes for the most popular entries or have an improperly configured DB server. Let’s go through all the steps one by one.

1. Remove Useless Entries From Magento Database

We’ve already mentioned this tip in our Magento Add to Cart slow guide. This is a great solution for more than just the Shopping Cart optimization. Truncating works well for bloated log entries, various system events, comparisons inside the catalog, and some other files you might not really care about.

Create a backup and then use TRUNCATE command on 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,
  • index_event,
  • catalog_compare_item,
  • catalogindex_aggregation,
  • catalogindex_aggregation_tag,
  • catalogindex_aggregation_to_tag,
  • adminnotification_inbox,
  • aw_core_logger.

In case you like to automate ALL THE THINGS, take a look at our speed optimization guide where you can find step-by-step instructions on how to install and use a dedicated module that cleans Magento logs via cron once a week.

2. Consider Switching to a MySQL Alternative

Traditional MySQL implementations might be the safest choice for your Magento install, but if you want to squeeze every last bit of juice out of your database setup look into switching to either MariaDB or Percona.

MySQL

mysql optimization magento What’s the difference between these 3 systems? The long version is here, in this comprehensive comparison. In general, the biggest advantage of MySQL is its long-term endurance. MySQL entered the market in the 90s and has been in the leading positions ever since. 

Magneto MySQL 5.7 boasts great compatibility, robust architecture, and huge help documentation.

To list both pros and cons, MySQL in some cases requires a paid license for commercial use, offers limited capabilities in scaling and security, and generally rests on its laurels too much.

MySQL advantages for Magento:

With MySQL, one thing you need to understand well is expectations. As we’ve already mentioned, MySQL is a great baseline solution for database management if you don’t expect your store to handle extreme loads. 

Sure, it’s a bit basic, but it gets the job done. You will always find tons of help online and knowledgeable teams ready to manage your database in a conservative but solid way.

MariaDB

mariadb mysql alternative for database optimization MariaDB is more community-focused and works better for users who care more about features than pure performance. MariaDB supports a large array of database engines, disk encryption, complex horizontal interconnectivity, and scaling features which could be interesting for large Magento stores.

One thing to consider, though. MariaDB supports switching from MySQL to MariaDB, but not the other way around if you plan on using complex replication schema with global transactional IDs. 

The migration is a one-way ticket to those who would like to make the move so think twice. Backward compatibility could be important if you are just experimenting and not ready to commit to MariaDB in the long term.

Among prominent MariaDB adherents are Ansell, America Movil, BlaBlaCar, Nokia, Red Hat, and Samsung.

MariaDB advantages for Magento:

  • Advanced Magento sharding support (Spider storage engine or Galera cluster),
  • In-depth horizontal replication between multiple sources, intricate scaling and connectivity,
  • Performance boost compared to the default MySQL setup (see this review for an in-depth comparison of different workloads).

Percona

percona magento database optimization mysql alternative Percona is a fork of MySQL that centers around performance and peak load handling.

Compared to MariaDB, Percona is a less popular, more specialized database engine. Even though the two alternatives were released at roughly the same time (2008-2009), they have very different adoption rates and community sizes.

Among high-profile Percona fans are Facebook, Netflix, SoundCloud, and Adobe.

Percona advantages for Magento:

  • Better performance for large datasets and heavy loads especially using expensive enterprise-level hardware (threadpool scaling, 48-CPU scaling limit),
  • Noticeable advantages for high-load e-commerce applications,
  • Good security (encryption, advanced user isolation through sharding, audit logging, PAM authentication),
  • Decent database optimization and diagnostics tools (query logging, I/O count, access counters, etc).

MariaDB or Percona?

Choose MariaDB if you need more quality-of-life and DevOps features. Go for Percona if your goal is to gain high-load performance in large-scale datasets.

NOTE: Comparing MySQL, Percona, and MariaDB, you would inevitably come to comparisons of XtraDB and InnoDB. Here’s our take on the situation. XtraDB is only slightly better than InnoDB which means you shouldn’t worry too much about which engine you actually use. The gain in performance is not worth the upgrade. You are better off investing in other optimization efforts.

3. Switch to Flat Catalogs to Reduce DB Queries

Flat catalogs help reduce database load. Flattening cuts down on DB queries since the system can fetch the same amount of data with fewer calls. 

In large-scale stores with vast product count, these gains from flat catalogs and flat product categories are especially noticeable. The more products you have, the bigger the difference.

switch to flat catalogs to optimize magento database

In Magento 2 Admin Panel, go to Stores > Configuration > Catalog and make sure to switch to YES on both Use Flat Catalog Category and Use Flat Catalog Product.

4. Turn on Logging to Find Poorly Performing Queries 

MySQL offers built-in tools to facilitate Magento MySQL database performance optimization, one of which is slow query log. 

Turn it on, define which kind of queries you consider too slow, and watch the log grow. All queries longer than a certain amount will end up here. For example, let’s start with populating the log with queries longer than 1 second.

Once you deal with the slower queries, you can move on to queries longer than 500 ms, etc. To define which queries the system should consider slow, use long_query_time value.

Use MySQL EXPLAIN statement to find out the structure and contents of each slow query and properly analyze it.

5. Update Your Database Version

Sounds obvious but newer is always better. Update your database to the latest version to improve performance, security, and fix bugs. New versions often boast better read and write I/O speeds, concurrency performance, improved algorithms, and other upgrades. Keeping your database up to date costs literally nothing.

6. Add Indexes to Large Tables

Processing queries from larger MySQL tables can be extremely slow, up to 30-60 seconds in the worst cases. When you add an index to a table, you reduce the amount of time needed to process data. The larger the table, the more benefits you’ll see from implementing indexes.

Why extension developers don’t add indexes to their tables? Most of the time they either don’t bother to add or forget to do that before release. So it’s up to you to fix this issue.

All tables will benefit from having an index. The bigger the table, the more visible this performance gain will be. The only downside of total indexing is data storage.

7. Setup Layered Navigation Through Elasticsearch

Elastic-powered layered navigation works faster than the default Magento MySQL setup. Elasticsearch will lift some workload from your database and at the same time offer better performance, especially if you move it to a separate machine.

elasticsearch_search_engine magento database optimization

Another Magento MySQL database optimization tip is to turn off product count from layered navigation. This setting slows down page loading and doesn’t bring a lot of value in return. Switching it off is standard practice in most optimization scenarios.

product_count_OFF magento database tuning

Go to Stores > Configuration > Catalog > Catalog, locate Layered Navigation then untick Use System Value and set Display Product Count to No.

8. Discover Hidden Issues Using Advanced Tools

MySQLTuner is a well-known diagnostics script that helps developers find issues in their Magento MySQL setups and fix them following the script’s recommendations. It can uncover hidden issues that are otherwise hard to see.

If you have already implemented all our tips and looking for more action, run MySQLTuner and see what it can find. Alternatively, use ProfileSQL tool to analyze each database request and optimize them one by one.

Another tool that could help you with Magento MySQL optimization is the Tuning Primer script which focuses on the following tables:

  • Slow Query Log,
  • Max Connections,
  • Worker Threads,
  • Memory Usage,
  • Key Buffer,
  • Query Cache,
  • Sort Buffer,
  • Joins,
  • Temp Tables,
  • Table (Open & Definition) Cache,
  • Table Scans (read_buffer),
  • Table Locking,
  • InnoDB Status.

Magento MySQL as Fast as Possible

Always keep in mind your return on investment. Sometimes it doesn’t make a lot of business sense to invest hundreds of man-hours into honing and tweaking your Magento MySQL config. If you can gain the same performance improvement from expanding your hardware setup, do it.

Take into account both the time and cost of implementation when you make this business decision. Throwing more money at the problem might not be the most elegant solution but if it’s the most cost-effective, it’s a great alternative.

Want to know how we deal with slow Magento MySQL performance? Let’s look at your store together and discuss how our team can speed it up.

Magento tips from real projects
Magento tips from real projects
Close
CALL US 24/7:
Australia
& asia
+61 (02) 8005-7494