The upgrade to Magento 2.3, aimed at improving user experience, resulted in an unexpected increase in database CPU load. The problem was linked to a query frequently run in relation to the new Popular Search Term Cache. Instead of aiding user experience, this cache created a database bottleneck that slowed system performance and caused CPU surge. Several solutions were proposed, including the removal of the redundant DISTINCT operator from the problematic query, indexing the search_query table, integrating Elasticsearch, and using asynchronous insertions to reduce database load. However, these solutions could potentially impact user experience visibility. With the issue continuing in Magento 2.4, finding an optimal solution is crucial. This affirms the importance of balancing user-centric enhancements and system performance.
Full article here: https://medium.com/@lawsuithelpdesk/unraveling-the-mystery-how-magento-2-3s-f40ab8b82729