Skip to content
Lawsuit Help Desk

Lawsuit News Center

Unveiling the Hidden Culprit: How Magento 2.3 Upgrade Unleashes a Barrage on Your Database CPU Load

Unveiling the Hidden Culprit: How Magento 2.3 Upgrade Unleashes a Barrage on Your Database CPU Load

Unveiling the Hidden Culprit: How Magento 2.3 Upgrade Unleashes a Barrage on Your Database CPU Load

Magento 2.3's upgrade, once heralded as an advancement, has unexpectedly turned into a nightmare for many, with a marked onslaught on database CPU load. Unbeknownst to most, the hidden cause of this digital chaos is an innocuous-looking feature – the popular search term cache, which inadvertently is causing a substantial slowdown in queries. This article sheds light on the issue, its replication in subsequent Magento versions, and possible workarounds, primarily focusing on the troublesome distinct operator that has unexpectedly shoved your database into overdrive.

Unmasking the Hidden Perpetrator: The Impact of Magento 2.3 Upgrade on Database CPU Load

The Magento 2.3 upgrade, at first glance, appeared to be a boon for digital platforms, bringing with it a potent assortment of enticing new features. Of these, the popular search term cache feature emerged as a headline feature. However, this seemingly benign addition stealthily introduced an unexpected surge in database CPU load, transforming the upgrade into a digital Pandora's box.

This surge, as our research found, was a direct result of an increase in the size of the search_query table. A larger table invariably results in longer query times, leading to an excessive strain on the database. On live sites with upwards of 2.7 million search terms, this strain manifested as noticeably slower query times, leading to the dreaded user experience nightmare – sluggish website speed.

Demystifying the Problematic Query: The Role of SELECT DISTINCT COUNT(*)

Nestled within this surge of CPU load, the SELECT DISTINCT COUNT(*) query emerged as a particularly troublesome entity. This query is executed on the search_query table and its role is to count the number of unique search terms. The objective was simple enough, yet its execution had an unintended consequence – a significant load on the database CPU.

The effects of this query were especially pronounced on larger websites, where it led to query times reaching extremes of up to 23 seconds – a veritable lifetime in the digital world. The DISTINCT operator in the query, although seemingly useful, was found to be the main culprit behind this inefficiency. It turned out to be a redundant operation due to the unique constraint on query_text and store_id columns and only served to increase processing time.

Understanding num_results > 0: The Bottleneck in Magento's Query Execution

In the grand scheme of query execution, the num_results > 0 part emerged as a significant bottleneck. This particular component of the query was found to slow down the system disproportionately, leading to longer query times. Removing this part of the query, as our research suggests, could potentially alleviate the situation, leading to an improved performance of the search term cache feature. However, this requires further testing to confirm.

Evaluations and Replications: Tracing the Issue Across Different Magento Versions

The problem, as it turns out, is not isolated to Magento 2.3. Further research revealed that the issue was consistently reproduced on the latest 2.4-develop branch of Magento. This underscores the need to address this issue urgently, as it continues to impact not just one, but multiple versions of Magento, potentially affecting millions of online businesses globally.

While the Magento 2.3 upgrade brought forth the issue of high database CPU load, it is important to note that this issue has not been completely addressed in subsequent versions either. It is not a one-off instance. The problematic query, SELECT DISTINCT COUNT(*) FROM search_query WHERE (store_id = 1) AND (num_results > 0), continues to hamper performance in versions as recent as 2.4.4-p2 and 2.4.5-p4 of Magento.

The ‘num_results > 0’ part of the query has been identified as the primary bottleneck, slowing down query execution to such an extent that it can take up to 23 seconds to complete. It is worth noting that this issue is not unique to custom Magento instances, but is also reproducible on vanilla Magento instances, thereby emphasizing the widespread impact of this problem.

Further, this query places unnecessary strain on the database, particularly when the search_query table is large. On a live site with 2.7 million search terms, this query can unduly lengthen the response time, thereby affecting the user experience.

The Way Forward: Recommendations for Optimizing Performance and Reducing CPU Load

Given the persistence of this problem across multiple versions of Magento, it becomes crucial to identify potential solutions that can alleviate the high CPU usage and optimize overall performance.

One of the key solutions that has emerged from extensive research and analysis is the removal of the distinct operator from queries within Magento\Search\Model\ResourceModel\Query\Collection. The distinct operator, it appears, does not add any value but instead hampers performance. Notably, the removal of the distinct operator from this query has been shown to drastically reduce query time, from an average of 700ms to a mere 2ms.

Another potential solution lies in the asynchronous insertion of search terms in batches, which could reduce the strain on the database. Alternatively, only inserting a fraction of the search terms or discontinuing the tracking of search terms altogether could prove beneficial.

Additionally, adding an index on the search_query table may help optimize performance by resolving the issue at hand. While these proposed solutions need to be tested and validated, it is clear that a combination of these efforts could potentially address the underlying issue of high database CPU load, providing much-needed relief to beleaguered Magento users.

As we continue to delve deeper into the complex world of Magento's search term cache feature, it becomes clear that a single problematic query has far-reaching implications for overall database CPU load. The path to resolution, however, is not as clear-cut. Stay tuned as we explore potential solutions in the next part of our exploration into the hidden intricacies of Magento's query execution.

As we move forward amidst this digital chaos, it is imperative that Magento users, developers, and the wider community, work collaboratively to address this critical issue. It is only through such collective efforts that we can successfully navigate the complexities of Magento's upgrade and optimize its performance for the benefit of all.

Therefore, it is clear that while the Magento 2.3 upgrade offers a host of valuable features, it does so at a cost. The toll it takes on the database CPU load, largely due to the SELECT DISTINCT COUNT(*) query, results in noticeably slower site speeds, affecting user experience. This problem is not confined to version 2.3, but persists across subsequent versions, making it a pressing issue for Magento users globally.

• Our research suggests that removing the DISTINCT operator from the query could drastically reduce query time, thus alleviating the strain on the database.
• Additional solutions could include asynchronous insertion of search terms in batches or discontinuing the tracking of search terms altogether.
• Adding an index on the search_query table may also help optimize performance.

In conclusion, as we navigate the digital intricacies of Magento's upgrade, it underscores the need for collective efforts from users, developers, and the wider community to tackle this issue head-on. These recommendations, while needing further testing and validation, offer a promising start to resolving this problem, optimizing performance, reducing CPU load, and enhancing user experience.