Skip to content
Lawsuit Help Desk

Lawsuit News Center

Cracking the Code: Navigating Magento 2.3's Unexpected CPU Load Spike and the Power of Efficient Query Optimization

Cracking the Code: Navigating Magento 2.3’s Unexpected CPU Load Spike and the Power of Efficient Query Optimization

"Cracking the Code: Navigating Magento 2.3's Unexpected CPU Load Spike and the Power of Efficient Query Optimization"

In the labyrinthine world of Magento 2.3, an unanticipated CPU load surge has baffled many since its update, with a frequently running query playing the unsuspected villain. This article delves into the intricacies of this issue, originating from the addition of Popular Search Term Cache, and the profound impact of seemingly innocuous parts like the "num_results > 0" condition and the DISTINCT operator. As we traverse this technical maze, we'll explore efficient query optimization strategies, offering solutions to alleviate this unexpected strain and enhance overall performance.

Navigating the Maze: Unpacking the Magento 2.3 Update CPU Load Issue

In the bustling digital marketplace, e-commerce platforms like Magento are continuously refined and updated to meet the evolving needs of businesses. However, the Magento 2.3 update brought along an unexpected hitch – a sudden spike in database CPU load. This unforeseen surge was not just a minor hiccup. It was a substantial and concerning strain, especially for larger websites with extensive search-query tables.

The heart of the issue lies in a specific query that was introduced as part of the Magento 2.3 update. This query, SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0), put an unforeseen strain on the database. Particularly, websites with larger search-query tables experienced longer query durations, dramatically impacting the performance of the platform, leading to slower site speeds.

The Invisible Culprit: Understanding the Impact of the Frequent Query

The strain on the database CPU load can be largely attributed to the frequency of this new query. A query, in and of itself, is not an issue. Still, when frequently run, especially on a live site with a vast number of search terms, it can cause serious performance problems.

For instance, a website with 2.7 million search terms saw the query place considerable strain on the database, resulting in sluggish performance. The query was run multiple times, causing significant slowdowns, and even rendering the admin panel practically unusable. The extent of the strain this query placed on the system was such that the site's performance was drastically hindered, making navigation a frustrating process for both users and administrators.

Digging Deeper: The Role of Popular Search Term Cache and ‘num_results > 0’ Condition

Digging deeper into the issue, we find two main culprits – the Popular Search Term Cache and the 'num_results > 0' condition. The addition of the Popular Search Term Cache in the Magento 2.3 update, while aimed at enhancing the platform's usability, in fact, exacerbated the CPU load issue.

The 'num_results > 0' condition, though seemingly innocuous, turned out to be a significant factor impacting query duration. It was discovered that the primary segment of the query that took time was this condition. When removed, the query duration improved dramatically, from 23 seconds to a mere 0.7 seconds.

This discovery underscored the profound impact of small, often overlooked components on the overall performance of a system. It highlighted the need for comprehensive performance testing and optimization, ensuring that every bit of code, every condition, contributes positively to system performance, and does not become a stumbling block.

The exploration of the Magento 2.3 CPU load issue serves as a reminder of the fundamental principle of software engineering – the devil, as always, is in the details. In the following sections, we’ll delve into how removing the DISTINCT operator revolutionizes query duration and explore practical solutions to prevent slow search request times, thereby equipping you to cope with similar challenges in your Magento journey.

Breaking the Code: How the Removal of the DISTINCT Operator Revolutionizes Query Duration

As our exploration penetrates deeper, we encounter the code's linchpin—the DISTINCT operator. Though seemingly benign, this operator was found to be the heart of the performance issue. Within the query, SELECT DISTINCT COUNT(*) FROM search_query AS main_table WHERE (main_table.store_id = 1) AND (num_results > 0), the DISTINCT operator was the true catalyst for elongated query durations.

It was noted that removing the DISTINCT operator led to a drastic change in the query duration, plummeting from 700ms to an impressive 2ms. This revelation is critical as the DISTINCT operator within \Magento\Search\Model\ResourceModel\Query\Collection was found to be unnecessary and, in fact, detrimental to query performance.

Alternate Routes: Exploring Solutions to Prevent Slow Search Request Times

Now that we’ve identified the problem, we turn our focus to solutions. The primary recommendation is to remove the DISTINCT operator from queries within \Magento\Search\Model\ResourceModel\Query\Collection. This alone could revolutionize query performance, mitigating the slow search request times that have been plaguing Magento 2.3 users.

Additionally, asynchronously inserting search terms into the table in batches could further prevent slow search request times. By only inserting a fraction of the search terms or refraining from tracking search terms entirely, we can bypass the unnecessary strain on the database. These strategies offer alternate routes to relieve the system of the burden of hefty and time-consuming queries.

Reopening the Case: The Ongoing Evaluation of the Performance Issue in Magento 2.3 and 2.4

Despite these discoveries, the case is far from closed. The c90edaa commit, initially perceived as the performance issue solution, has proved to be ineffective. The issue has been reproduced on the latest 2.4-develop branch, confirming that the performance problem is still relevant and requires further evaluation—even after the proposed solutions are implemented.

The query SELECT COUNT(*) FROM (SELECT DISTINCT main_table.query_text FROM search_query AS main_table WHERE (main_table.store_id IN (1)) AND (num_results > 0) ORDER BY popularity desc LIMIT 100) AS result WHERE (result.query_text = 'test') continues to cause slow performance, leading to freezing of the admin panel. The issue, still present in 2.4.5-p4, reiterates the necessity for further research and resolution strategies.

As evident, efficient query optimization is key to solving Magento 2.3's unexpected CPU load spike. By continuing to investigate and adapt, we hope to enhance overall performance, creating a smoother and more reliable experience for all Magento users. In conclusion, the unexpected CPU load spike in Magento 2.3, caused by the introduction of a new query, underscores the importance of efficient query optimization in software development. This exploration has uncovered the critical role of seemingly minor components like the DISTINCT operator, whose removal drastically improved query duration, and the 'num_results > 0' condition, which significantly impacted system performance. It has also highlighted the value of performance testing and optimization, drawing attention to the importance of ensuring every bit of code contributes positively to the system's overall performance. Accordingly, the following recommendations can be made to mitigate the CPU load issue and improve performance:

  • Remove the DISTINCT operator from queries within \Magento\Search\Model\ResourceModel\Query\Collection.
  • Insert search terms into the table asynchronously and in batches.
  • Refrain from tracking search terms entirely, if possible.

However, as the issue is still present in Magento 2.4.5-p4, ongoing evaluation is necessary to devise effective resolution strategies. This case serves as a reminder that in software engineering, the devil is indeed in the details and underlines the need for continuous investigation and adaptation in the face of evolving challenges.