Unraveling the Mystery: How Magento's Latest Update Amplifies CPU Load and the Strategies to Counteract It

Unraveling the Mystery: How Magento’s Latest Update Amplifies CPU Load and the Strategies to Counteract It

Unraveling the Mystery: How Magento's Latest Update Amplifies CPU Load and the Strategies to Counteract It

In the ever-evolving landscape of digital commerce, the latest update to Magento 2.4.6 has unwittingly culminated in an increased database CPU load, posing a significant challenge to website performance. This unexpected surge traces back to the 'search_query' table's exponential growth and the inefficient implementation of Distinct Count queries within the platform. This article aims to delve into the details of this issue, exploring the implications of the 'Popular Search Term Cache', the role of high search term cardinality, and the viable strategies to mitigate these adverse effects.

Exploring the Root Cause: The Aftermath of Upgrading to Magento 2.4.6

When it comes to digital commerce platforms, Magento has always been a highly respected choice. However, with the recent upgrade to version 2.4.6, a peculiar issue has transpired, causing a sharp increase in database CPU load. This unexpected surge has been traced back to a specific aspect of how Magento handles its search functionality – namely, an exponential growth in the 'search_query' table.

The 'search_query' table is a component of Magento’s database schema where it logs all search terms entered by users on a site. It is integral in the operation of search-related functionalities but, as with anything, its unchecked growth has a downside. The issue was reproduced on the latest 2.4-develop branch of Magento and persists in versions 2.4.1-p1, 2.4.4-p2, and 2.4.6-p1.

Unmasking the Miscreant: The Role of the 'search_query' Table in Slowing Down Your Site

The 'search_query' table contributes to the overall database footprint and, when bloated, can result in slow queries which, in turn, cause performance lags. The table's size has a significant impact on query duration, thereby slowing down the site's performance. For example, a large 'search_query' table can make the keyword search take a long time to complete. The problem intensifies with a high search term cardinality, i.e., when the table contains a vast number of distinct search terms.

Another factor aggravating this situation is the use of DISTINCT COUNT(*) queries on the 'search_query' table. These queries, especially when combined with the 'num_results > 0' condition, have been identified as the main bottleneck, causing considerable performance degradation. In essence, the larger the 'search_query' table, the slower these queries become.

The Hidden Troublemaker: How 'Popular Search Term Cache' Contributes to Performance Lag

The introduction of 'Popular Search Term Cache' in Magento 2.3.0, although intended to enhance the platform's performance, ironically has contributed to the slowing down of the site. This feature works by caching popular search terms to expedite search results. However, this also means that the 'search_query' table is frequently accessed and manipulated, leading to an increased CPU load.

Moreover, the inclusion of the DISTINCT operator in queries within the \Magento\Search\Model\ResourceModel\Query\Collection slows down performance. The DISTINCT operator is used to return unique results, but in this context, it is unnecessary and hurts query performance.

To sum up, the 'search_query' table's size and the use of DISTINCT COUNT(*) queries within it, along with the implementation of the 'Popular Search Term Cache', are the root causes of the increased database CPU load post-upgrade to Magento 2.4.6. In the sections to follow, we will delve into more complex aspects of this issue and propose solutions to counteract these adverse effects.

Probing Further: Diving into the Effects of High Search Term Cardinality on Query Duration

High search term cardinality, the unique search terms that a user inputs, can have a considerable impact on the query duration. The 'search_query' table in Magento, which holds the records of these search terms, grows significantly with an increase in search term cardinality. This growth leads to slower performance, as witnessed in the Magento 2.3.4 community version and persisting in the 2.4.1-p1, 2.4.4-p2 and 2.4.6 Commerce version.

In essence, the 'search_query' table strains under the weight of millions of records, leading to extended keyword search times. A distinct culprit is the SELECT DISTINCT COUNT(*) query, which unnecessarily complicates processes, despite its intention to prevent duplicate entries. Removing the DISTINCT operator from the query significantly improves keyword search performance without affecting the search results, establishing that the operator is not just unnecessary, but detrimental to site performance.

The Road to Redemption: Implementing Successful Strategies to Boost Magento's Performance

So, how can Magento rectify these performance issues? It's simple—start with altering the 'search_query' table. One promising strategy is to implement asynchronous insertion of search terms in batches, which can decrease the load on the table and improve performance. Alternatively, inserting only a fraction of the search terms can have a similar positive effect.

Completely stopping the tracking of search terms could be another viable strategy. However, this might have repercussions on the site’s analytics and SEO. Hence, it should be approached with caution.

Another compelling solution lies in modifying the coding within the \Magento\Search\Model\ResourceModel\Query\Collection. Here, changing the function $select->distinct(false) can remarkably improve performance. This function change essentially removes the DISTINCT operator from queries, thereby minimizing execution time and boosting site performance.

In conclusion, the adverse performance impacts following the Magento 2.4.6 update are indeed challenging but far from insurmountable. Recognizing the issues arising from the 'search_query' table growth and high search term cardinality are the first steps towards a resolution. Implementing the right strategic changes could not only rectify these issues but also significantly enhance Magento's overall performance. The road to redemption might be complex, but it is certainly achievable.

Therefore, understanding the impact of Magento's latest update on CPU load hinges on recognizing the role of the 'search_query' table and the 'Popular Search Term Cache'. To summarize our findings:

  1. The exponential growth of the 'search_query' table, which logs user's search terms, results in severe performance issues and higher CPU loads.
  2. High search term cardinality, resulting in a larger 'search_query' table, intensifies this problem.
  3. The use of DISTINCT COUNT(*) queries, particularly in combination with 'num_results > 0' conditions, exacerbates the performance degradation.
  4. The 'Popular Search Term Cache', introduced in Magento 2.3.0, ironically slows down the site instead of enhancing performance due to frequent access and manipulation of the 'search_query' table.

However, these challenges aren't insurmountable. Asynchronous insertion of search terms, minimizing entries, and altering functions within the \Magento\Search\Model\ResourceModel\Query\Collection can rectify these performance issues. The road to rectifying these issues and enhancing Magento's performance may be complex, but with the correct strategies, it's certainly achievable.