Skip to content
Lawsuit Help Desk

Lawsuit News Center

Decoding the Magento 2.3 Upgrade: Unraveling the Mystery of Rising CPU Load and Search Performance Issues

Decoding the Magento 2.3 Upgrade: Unraveling the Mystery of Rising CPU Load and Search Performance Issues

Decoding the Magento 2.3 Upgrade: Unraveling the Mystery of Rising CPU Load and Search Performance Issues

The recent upgrade to Magento 2.3 ignited a perplexing surge in database CPU load, primarily attributed to issues with search-term cache performance and scalability. Users reported notable delays in the execution of search queries, particularly as the size of the 'search_query' table escalated. In unraveling this mystery, we'll delve into the specifics of these problems, exploring the role of the DISTINCT operator, the impact of the search_query table size, and potential solutions to these pressing performance issues.

Unveiling the Culprit: The Popular Search Term Cache

The cornerstone of this conundrum lies in the addition of the Popular Search Term Cache with the Magento 2.3.0 upgrade. Diving deeper into the issue, it's evident that an increase in database CPU load is the unfortunate by-product. The cache, while engineered to enhance search efficiency, ironically acted as a catalyst accelerating performance issues, posing a scalability problem that was unforeseen.

What's important to understand here is that every query made in the main search bar triggered this cache, causing a significant delay in completing the query. With each query, the size of the 'search_query' table swelled, leaving the database under strain and queries achingly slow. For sites with millions of search terms, this was a significant problem, with queries being slowed to a crawl.

Dissecting the Problem: The 'num_results' Condition

Inspection of the query used in Magento's search function revealed a significant bottleneck – the 'num_results > 0' part. This seemingly innocuous condition was slowing things down considerably.

The query in question, 'SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0)', took a considerable amount of time to execute, particularly with a large 'search_query' table. The 'num_results' condition was the main culprit, causing a significant increase in query duration.

However, the removal of this 'num_results' condition led to an improvement in query performance, which was illustrated by a drastic reduction in query duration – from 23 seconds to a mere 0.7 seconds. This shows the dramatic impact this single condition had on the overall performance of the search function.

Under the Lens: The Impact of DISTINCT Operator and Search Query Table Size on Performance

Upon further scrutiny, the DISTINCT operator used in the query emerged as another performance squelcher. While intended to eliminate duplicate entries in the result, it did not provide any value, instead only added undue overhead to the query execution. The 'search_query' table already housed a unique constraint on 'query_text' and 'store_id', rendering the DISTINCT operator redundant and unnecessary.

What's more, the 'search_query' table's size was found to be directly proportional to the performance degradation. As the table size increased, keyword search performance took a nosedive. This is because the DISTINCT operator had to sift through a larger set of data, increasing the time taken to execute the query.

The insights gained from this deep-dive investigation into the problems facing the Magento 2.3 upgrade are critical in addressing the pressing performance issues and finding potential solutions. The next sections will delve into these possible solutions, offering a ray of hope for the beleaguered Magento users.

Potential Remedies: Indexing and Elasticsearch

The soaring CPU load and slowdown in search performance triggered by the Magento 2.3 upgrade can be mitigated with specific strategies. Two potential solutions have emerged to address these issues: indexing the 'search_query' table and utilizing Elasticsearch for all search-related functions.

Indexing the 'search_query' table can provide a simple, yet effective solution to this performance bottleneck. An index can expedite the search process by creating a data structure that allows for faster access to the data in the table. This could result in significantly improved query performance, relieving the strain placed on the database.

Concurrently, Elasticsearch, a powerful and versatile search and analytics engine, offers another avenue for improving performance. By handling all search-related operations, Elasticsearch can efficiently sift through large amounts of data, returning relevant results swiftly and accurately. This approach would lessen the load on the 'search_query' table, potentially offering relief from the performance issues.

Nevertheless, it's important to note that disabling search suggestions from the admin panel and enabling Elasticsearch does not entirely prevent the main search bar from accessing the 'search_query' table. Asynchronous insertion of search terms into the table in batches, inserting only a fraction of the search terms, or ceasing to track search terms may further alleviate the performance issue.

The Unresolved Issue: Performance Lag in Various Magento Versions

Despite these potential solutions, the performance problem is not yet fully resolved. The issue persists across different Magento versions, including the latest 2.4-develop branch, Magento 2.4.1-p1, and even as recent as Magento 2.4.5-p4. This widespread occurrence indicates that the current implementation has not effectively addressed the issue, leading to a surge in traffic causing slowdowns in the main search bar and admin panel.

The DISTINCT operator and the 'num_results' condition are consistently identified as the primary culprits behind the performance lag. The DISTINCT operator only adds overhead to the query execution, while the 'num_results' condition significantly increases query duration. Removing these elements from the queries within the \Magento\Search\Model\ResourceModel\Query\Collection significantly improved search performance.

The Final Verdict: Optimizing Keyword Search Queries for Improved Performance

In the final analysis, to overcome the performance issues arising from the upgrade to Magento 2.3, it is imperative to optimize keyword search queries. The DISTINCT operator, while seemingly innocuous, is a substantial performance bottleneck, significantly slowing down the keyword search function. Given that the 'search_query' table already has a unique constraint on 'query_text' and 'store_id', the DISTINCT operator becomes unnecessary.

By removing the DISTINCT operator from keyword search queries, significant improvements in performance can be achieved. Moreover, optimizing the keyword search queries can not only improve performance but also reduce the high CPU usage experienced post the Magento upgrade.

To conclude, the mystery behind the performance issues of the Magento 2.3 upgrade can be cracked by focusing on query optimization, especially by removing the DISTINCT operator, and exploring potential remedies such as indexing and Elasticsearch. Performance improvement is a continuous process, and with the right strategies in place, these challenges can be effectively overcome.

Thus, delving into the core of the issues arising from the Magento 2.3 upgrade, we find ourselves faced with a paradox of performance improvement strategies that have ironically induced performance degradation. The addition of the Popular Search Term Cache, the DISTINCT operator, and the 'num_results' condition in the query stand as unintentional culprits, each playing a role in slowing down keyword search functionality and escalating CPU load. Accordingly, potential solutions in the form of indexing the 'search_query' table and leveraging Elasticsearch offer promising alternatives to alleviate these performance issues. However, the ultimate key to unlocking improved performance lies in the meticulous optimization of keyword search queries, particularly through the removal of the DISTINCT operator and constant refinement of other query components.

In summary, the following points emerge as critical takeaways:

  • The Popular Search Term Cache, the DISTINCT operator, and the 'num_results' condition have inadvertently contributed to the performance issues in Magento 2.3.

  • Indexing and Elasticsearch offer viable solutions to mitigate the performance degradation, but they are not complete solutions in themselves.

  • The removal of the DISTINCT operator from keyword search queries can yield significant improvements in search performance and reduce high CPU usage.

  • Lastly, continuous performance improvement is a journey, not a one-time fix. It requires relentless focus on query optimization and exploring new solutions as the Magento platform evolves.

In conclusion, the real mystery behind the Magento 2.3 upgrade performance issues isn't shrouded in complex code or technical glitches – rather, it lies in a series of well-intentioned but counterproductive performance improvement strategies. By focusing on query optimization and exploring other potential remedies, we can chart a path towards not only resolving these issues, but also achieving superior performance in the future.