Skip to content
Lawsuit Help Desk

Lawsuit News Center

Unmasking Magento 2.3: How a Single Operator Escalated Database CPU Load and the Simple Fix to Reclaim Your Performance

Unmasking Magento 2.3: How a Single Operator Escalated Database CPU Load and the Simple Fix to Reclaim Your Performance

"Unmasking Magento 2.3: How a Single Operator Escalated Database CPU Load and the Simple Fix to Reclaim Your Performance"

Magento 2.3's upgrade has stirred the hornet's nest for database administrators worldwide, sparking a steep rise in CPU load tied to a peculiar cache issue concerning popular search terms. This nefarious performance glitch finds its roots traced to a 'SELECT DISTINCT' operator in a time-consuming query, creating a bottleneck that inflates the query's duration from a mere 2 milliseconds to a staggering 700 milliseconds. This article unravels the intricacies of this technical quandary, and explores the easy fix – removing the DISTINCT operator – that can restore your Magento store's performance, making it sleek and efficient once again.

Unraveling the Complex Web: The Roots and Ramifications of Magento 2.3's Performance Issue

Performance is a critical aspect of any e-commerce venture. Slow loading times not only frustrate users but also deter potential buyers. Thus, when Magento 2.3's upgrade sparked a significant rise in database CPU load, it rang alarm bells for database administrators across the globe. The primary culprit? A problematic cache issue linked to popular search terms.

The trouble began when administrators noticed a substantial increase in database CPU load following the upgrade to Magento 2.3. Further investigation led to the discovery of a frequently executed query that was taking an inordinate amount of time to complete. This wasn't just a minor hiccup; instead, it was a significant performance issue that threatened to derail the smooth operation of Magento stores. The crux of the problem lay in the query's select count of distinct search terms with non-zero results – a seemingly benign function that proved to be a substantial bottleneck.

The larger the search term table, the longer the query took to complete. In extreme cases, the num_results > 0 condition took the query's duration from 2 milliseconds to a whopping 700 milliseconds. This issue was not only reproducible on the 2.4-develop branch but had also been confirmed on multiple versions of Magento, including 2.3.4 and 2.4.4-p1.

The Trouble with SELECT DISTINCT: Understanding the Query Culprit in Magento's Database Dilemma

At the heart of this performance issue rested a single operator: SELECT DISTINCT. This operator, widely used in SQL to return unique values in the output, was causing a high CPU usage, leading to a bottleneck in the query's execution.

The DISTINCT operator was being used to calculate the count of unique search terms with non-zero results. But this operator's use was not necessary – in fact, it was doing more harm than good. The query_text and store_id columns in the search_query table already had a unique constraint, rendering the DISTINCT operator redundant. Over time, as the number of unique search terms grew, the DISTINCT operator's use led to slow MySQL inserts and consequently, high CPU usage.

Moreover, even disabling search suggestions and enabling Elasticsearch – popular solutions recommended to boost Magento's performance – failed to resolve the issue. The main search bar continued to hit the MySQL 'search_query' table, indicating that the problem was embedded deeper.

Navigating the Query Quagmire: The Performance Impact of Magento's Large Search Term Tables

The performance issues were further compounded by the size of the search_query table. This table, which tracks user search terms, can have millions of entries depending on a store's size and user activity. The larger the table, the more time-consuming the query execution became, leading to unwieldy query durations.

The implications were far-reaching. Longer query times were not just a technical problem – they had the potential to impact Magento stores' user experience, affecting everything from search speed to page load times. The issue was reproducible on the latest 2.4-develop branch and various Magento versions – a clear sign that a solution was urgently needed.

However, the answer lay not in a complex overhaul but in a simple adjustment: removing the DISTINCT operator from the query. This small step lowered the query duration from 700ms to 2ms, drastically improving query performance and reducing CPU load. As we delve deeper into potential solutions and their implications in the following sections, it is important to remember that sometimes, the simplest remedies can have the most powerful impact.

The Dawn of a Solution: Removing the DISTINCT Operator to Reclaim Query Efficiency

The root cause of Magento 2.3's performance conundrum lies in the use of the DISTINCT operator. In MySQL, the SELECT DISTINCT statement is used to return only distinct (different) values, which in theory sounds practical. However, the devil is in the details. This seemingly innocuous operator, when used in the query that selects the count of distinct search terms with non-zero results, has proven to be a major bottleneck.

As our research and case studies suggest, the DISTINCT operator is not only unnecessary but also detrimental to performance. The reason is simple: the operator is redundant due to the unique constraint on query_text and store_id columns. It's akin to double-checking a locked door, wasting precious time and resources without any justifiable cause.

The solution, therefore, is straightforward: remove the DISTINCT operator from the query. This simple operation can drastically reduce the query duration from a staggering 700 milliseconds to a quick 2 milliseconds. In fact, the performance uplift was observed across all Magento versions, from 2.3 to the latest 2.4-develop branch, indicating the ubiquity and effectiveness of this fix.

Final Takeaways: Implementing Practical Fixes for Performance Optimization in Magento Stores

In conclusion, the seemingly daunting performance issues of Magento 2.3 can actually be resolved with a simple yet potent correction: eliminating the DISTINCT operator from the query. Not only does this adjustment dramatically cut down query execution time from an alarming 700 milliseconds to a swift 2 milliseconds, but it also significantly curbs the CPU load, restoring your store's performance to its optimal efficiency. Alongside, it is equally crucial to implement strategies for managing the size of the search_query table to avoid its expansion, such as:

  • Asynchronous insertion of search terms in batches.
  • Tracking only a fraction of total search terms.
  • Completely disabling search term tracking.

These measures, coupled with the DISTINCT removal, are instrumental in enhancing Magento's performance, proving once again that in the realm of databases, every millisecond truly matters and every step towards optimization is a leap towards a smoother, more efficient e-commerce platform. Additionally, this case underscores the necessity of reviewing every operator and condition in your queries, ensuring they serve a purpose because, as we've seen with Magento 2.3, even a single redundant operator can lead to a considerable surge in database CPU load, threatening your store's performance.

So, how can we practically apply this solution? The DISTINCT operator should be removed from queries in the \Magento\Search\Model\ResourceModel\Query\Collection. Doing so will help restore the performance balance, allowing your Magento store to run more smoothly and efficiently.

However, the optimization does not stop there. Since the performance issue is related to the size of the search_query table, it's crucial to implement strategies to prevent its bloating. A table filled with millions of unique search terms will naturally slow down any query execution. Asynchronous insertion of search terms in batches, tracking only a fraction of search terms, or entirely disabling search term tracking can mitigate this problem.

Furthermore, despite popular belief, disabling search suggestions and enabling Elasticsearch does not resolve this issue. The main search bar still hits the MySQL 'search_query' table, causing performance decay. Hence, a comprehensive approach is needed for performance enhancement, starting with removing the DISTINCT operator and adeptly managing the size of the search_query table.

In conclusion, the journey through Magento's intricate web of performance issues has revealed a simple yet powerful solution: the removal of the DISTINCT operator. This fix, together with strategies for managing the search_query table's size, can significantly optimize Magento's performance. Remember, in the world of databases, every millisecond counts; therefore, every optimization step is a stride towards a more efficient and effective e-commerce platform.

A final note as we part ways: always ensure that every operator, every condition in your queries is there for a reason. Because as we've learned from Magento 2.3's case, even a single unnecessary operator can escalate database CPU load, bringing your store's performance to a grinding halt.