Skip to content
Lawsuit Help Desk

Lawsuit News Center

Unraveling the Mystery: How Magento 2.3's Update Unleashed a CPU Load Crisis & the Pioneering Fixes to Restore Your Site's Performance

Unraveling the Mystery: How Magento 2.3’s Update Unleashed a CPU Load Crisis & the Pioneering Fixes to Restore Your Site’s Performance

Unraveling the Mystery: How Magento 2.3's Update Unleashed a CPU Load Crisis & the Pioneering Fixes to Restore Your Site's Performance

In the digital age, performance is paramount. But when Magento's 2.3 update rolled out, it unwittingly triggered a database CPU load crisis due to a problematic query linked to the new Popular Search Term Cache feature. This article embarks on a journey to unravel this mystery and explore pioneering fixes to restore your website's performance.

The Unforeseen Impact: Magento 2.3 Update and Surging CPU Loads

In the world of e-commerce, Magento's reputation precedes it. As a globally recognized platform, its software updates are eagerly anticipated. However, the Magento 2.3 update brought with it an unforeseen consequence. Although intended to enhance functionality, it resulted in an alarming surge in database CPU load, causing significant strain on websites and leading to a performance downturn.

The unexpected CPU load surge was reported following the migration to Magento 2.3. Website administrators found that the strain on the database increased dramatically, leading to queries that were painfully slow, particularly on live sites housing millions of search terms. The problematic query, SELECT DISTINCT COUNT(*) FROM search_query WHERE (store_id = 1) AND (num_results > 0), was identified as the main culprit, adversely affecting performance on the admin panel.

Digging Deeper: The Problematic Query and Its Role in the Performance Downturn

To understand the root cause, it's crucial to dissect the problem. At the core is the problematic query tied to the update. For websites with a large search_query table, the query’s execution time skyrocketed when the num_results > 0 condition was included. This, in turn, caused the CPU load to surge. The more extensive the search_query table, the longer the query took to complete, and the greater the strain on CPU resources.

Interestingly, the DISTINCT operator in the query was discovered to be not only unnecessary but also detrimental to performance. Removing this operator led to a significant improvement in query execution time, resulting in a lessened CPU load. Yet, the question remains: why was the DISTINCT operator included in the first place, and what role does it play within the larger Magento framework?

Beyond The Update: Delving into the 'Popular Search Term Cache' Feature

The problematic query is tied to a new feature introduced in the Magento 2.3.0 update — the Popular Search Term Cache. This feature was intended to streamline user experience by caching popular search terms. However, it ended up causing more harm than good, leading to the performance issues we've examined.

The query is tasked with selecting distinct counts from the search_query table given certain conditions. In essence, it's designed to support the Popular Search Term Cache feature. However, the larger the search_query table, the longer it takes to complete this function, thereby causing a surge in CPU load.

What's truly worrying is the fact that the issue persists even when using ElasticSearch as the search engine. This suggests a deeper flaw within the Magento framework, one that transcends the choice of search engine. At this point, it becomes clear that addressing the issue is not as simple as removing the DISTINCT operator or overriding the execute function – it requires a more holistic assessment of Magento’s structure and the way it caches search terms.

But fear not, e-commerce enthusiasts and site admins. The digital landscape is a vast playground for innovation, and concern breeds creativity. Just as the unexpected CPU load surge sparked worry, it's also ignited the search for pioneering fixes and performance enhancing solutions. Stay tuned as we delve into these in the coming sections.

When Fixes Aren't Enough: Persistent Challenges and Further Evaluations

The Magento 2.3 update, while introducing the 'Popular Search Term Cache' feature, also brought about a significant performance issue. The frequent query, SELECT DISTINCT COUNT(*) FROM search_query WHERE (store_id = 1) AND (num_results > 0), was identified as the primary culprit. This query, as part of the new feature update, was meant to improve user search experience but instead it caused a drastic surge in database CPU load.

Several attempts were made to address this problem. Removal of the num_results > 0 condition from the query was suggested as a way to improve performance. This provided some relief, but was not a comprehensive solution. The issue was not limited to a single version and was reproducible on the latest 2.4-develop branch of Magento.

Some suggested overriding the execute function in Magento_CatalogSearch/Controller/Result/Index to restore previous performance. However, this solution was not effective in all cases. Moreover, the addition of an index on the search_query table was proposed, but even this could not completely resolve the issue. The problem persisted even when using ElasticSearch as the search engine, and significantly impacted the admin panel performance.

The proposed fix in c90edaa was regarded as a potential solution, but it did not address the core performance issue. It became clear that the issue was not fixable with the current structure and caching of search terms.

Looking Ahead: Innovative Approaches to Restoring and Enhancing Performance

The persistence of the issue even after multiple attempts at resolution indicated that it was time to re-evaluate the problem and look for innovative solutions. The DISTINCT operator in the problematic query was identified as a major contributor to the performance downturn. By removing this operator from the queries within \Magento\Search\Model\ResourceModel\Query\Collection, a significant performance improvement was observed.

But, even this was not enough. The insertion of search terms into the table or tracking of search terms had to be reconsidered. Asynchronous insertion of search terms into the table was suggested as a possible workaround. Another radical approach was to stop tracking search terms entirely. These ideas were certainly drastic, but given the severity of the issue, they were worth considering.

A third proposition involved batching the insertion of search terms or reducing the number of tracked search terms. This approach aimed to manage the size of the search_query table more efficiently, thereby reducing the load on the database.

Despite these innovations, the performance issue remains relevant and continues to be worked on. The issue has been confirmed on multiple branches of Magento, including the latest 2.4-develop branch. The strain on the database, especially on live sites with millions of search terms, is a problem that requires a robust and well-crafted solution.

As we continue to unravel this mystery, it is clear that the Magento 2.3 update and the associated CPU load crisis has brought forth challenges that require complex problem-solving and innovative thinking. It also underscores the fundamental tenet of digital evolution – the need for continual learning, adaptation, and improvement. Even as we face these challenges, the goal remains clear – to restore and enhance your website's performance.

In conclusion, the Magento 2.3 update, while promising enhanced usability with its 'Popular Search Term Cache' feature, inadvertently led to a crisis in CPU load, causing website performance to suffer. It's evident that the issue surpasses mere code-level fixes and requires a deeper evaluation of Magento's structure and its mechanisms for caching search terms. Some of the solutions proposed include:

  • Removing the DISTINCT operator from the problematic query,
  • Asynchronous insertion of search terms,
  • Restricting the tracking of search terms,
  • Batching the insertion of search terms,
  • Managing the number of tracked search terms to control the size of the search_query table.

However, the issue persists, underscoring the need for innovative thinking and continual learning in the face of digital evolution. As we delve into the realm of e-commerce, it remains a necessity to adapt quickly, find creative solutions to unforeseen problems, and above all, keep the end goal in sight – ensuring a smooth, efficient experience for the end user. With open minds, the digital community continues to unravel this mystery and work towards restoring and enhancing your website's performance.