Skip to content
Lawsuit Help Desk

Lawsuit News Center

Unmasking Magento 2.3: The Hidden Culprit Behind Your Surging Database CPU Load

Unmasking Magento 2.3: The Hidden Culprit Behind Your Surging Database CPU Load

Unmasking Magento 2.3: The Hidden Culprit Behind Your Surging Database CPU Load

In the expansive world of e-commerce, Magento 2.3 stands out as an intricate yet compelling platform. However, beneath its smooth functionality, there lurks a performance issue causing vexing surges in database CPU load. This blog post will unravel the mystery surrounding this issue and shed light on the unsuspected culprit – a 'DISTINCT' operator in the search_query table.

Unveiling the Underlying Issue: Magento 2.3 Database CPU Load

Upon upgrading to Magento 2.3, a startling shift is noticed in the database CPU load which suddenly starts to surge. Predominantly, this increase is tracked back to a frequent query linked to the addition of the popular search term cache feature in this version. Expected to complete within a reasonable amount of time, this query, unfortunately, takes substantial time, creating a strain on the database, particularly on platforms processing millions of search terms. The more expansive the search_query table, the longer the query takes to conclude, thereby putting an undue burden on the system.

Diagnosing the Cause: The Role of 'DISTINCT' Operator Within search_query Table

The query under scrutiny is: SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0). The role of the 'DISTINCT' operator is to eliminate duplicate rows and return only unique rows, a task which, when performed on a table of extensive size like search_query, significantly slows down the query execution time. Thus, the 'DISTINCT' operator emerges as a speed bump on the fast-lane of Magento 2.3's performance, especially when the search_query table already has a unique constraint on query_text and store_id, rendering the use of 'DISTINCT' redundant and counterproductive.

Diving Deeper: The Impact of the 'num_results > 0' Condition

The condition 'num_results > 0' adds another layer of complexity to the issue by consuming additional time. Being part of the query, the condition checks for instances where the number of search results is greater than zero, thus amplifying the time taken for the query to execute. While the removal of this condition may appear as a viable solution for improving performance, it warrants a closer look as it might have an impact on the overall performance of the platform. The key here is to strike a balance between efficient query execution and maintaining the integrity of the search functionality, a challenge that needs to be addressed.

By understanding the problem at its roots, we are now better equipped to evaluate the existing solutions and explore new ones. The seamless functioning of e-commerce platforms like Magento 2.3 depends heavily on efficient and timely database operations, and it is crucial to address these issues promptly to prevent them from spiraling into larger performance concerns. As we delve deeper into the existing solutions and their efficacy in the following sections, we will continue to unmask the hidden facets of Magento 2.3's performance issue.

Tackling the Problem: The Need for Asynchronous Insertion of Search Terms in Batches

As we delve into the myriad ways of resolving this taxing issue, one innovative approach stands out— the asynchronous insertion of search terms in batches. This technique aims to prevent performance issues by breaking down the process into manageable chunks, thereby reducing the strain on the database.

When we dig into the actual operation, it becomes clear how this method can potentially mitigate the issue. A live site with 2.7 million search terms exerts immense pressure on the database. If these terms are inserted asynchronously in batches, the burden on the database is considerably lessened. The argument for switching to this method becomes stronger when you consider that this approach not only prevents performance issues but also optimizes search efficiency—a win-win for all involved.

However, we must bear in mind that these solutions might require further tweaking based on the unique characteristics of specific Magento instances. For instance, in cases of high search term cardinality, it might be necessary to insert only a fraction of the search terms or even stop tracking search terms altogether. This highlights the need for a flexible yet robust approach to mitigating this issue.

Looking Forward: Implementing Lasting Solutions to the Performance Issue in Future Magento Updates

While short-term fixes and workarounds may offer temporary relief, the need for a durable solution to the Magento 2.3 performance issue is undeniable. Future Magento updates must address this issue head-on, not just to mitigate the current issue, but also to ensure the sustainability of the platform.

One promising solution is the removal of the 'DISTINCT' operator from queries within the \Magento\Search\Model\ResourceModel\Query\Collection. As our analysis shows, removing the 'DISTINCT' operator can significantly reduce query time. The logic behind this is straightforward. The 'DISTINCT' operator is superfluous, as the search_query table already has a unique constraint on query_text and store_id. Its removal, therefore, does not impact the functionality but enhances the performance.

Moreover, our research indicates that the 'DISTINCT' operator's elimination does not lead to the feared performance dip due to high search term cardinality. On the contrary, it aids in improving overall performance.

This solution's viability is currently under review. Yet, the initial results appear promising, and it certainly deserves attention in upcoming Magento updates.

In conclusion, the ongoing journey to resolve the performance issue in Magento 2.3 has been a riveting blend of challenges and enlightening discoveries.

  • Our meticulous research and detailed analysis have allowed us to pinpoint potential sources of the problem and consider promising solutions, primarily the removal of the superfluous 'DISTINCT' operator, and the innovative approach of inserting search terms in batches asynchronously.
  • These solutions, while currently under scrutiny, show promising initial results, and their integration within future Magento updates can tremendously enhance the platform's efficiency and robustness.
  • As our beloved e-commerce platform, Magento, continues to evolve and embrace innovation, we eagerly anticipate the incorporation of these solutions in forthcoming updates, bolstering Magento's performance and making it even more robust and efficient.

Let's continue to unmask and tackle these performance-related hidden culprits to ensure our favorite e-commerce platform stays in the fast lane of technology, efficiency, and user satisfaction.