1

Our Magento 2.3.5 has started to continually use 100% CPU and I found that the below query gets executed over and over for an unknown reason.

Any information to what is generating the query would be of great assistance as it seems the result is not cached.

The only difference in the queries are the offset number.

The category id of 3 is also disabled in the backend.

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(3, 4) AND cat_index.category_id=3 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product')) INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id INNER JOIN `search_tmp_6152e24b9ed758_22676751` AS `search_result` ON e.entity_id = search_result.entity_id ORDER BY `e`.`entity_id` DESC LIMIT 16 OFFSET 48

4
  • Could you post A) SHOW FULL PROCESSLIST; and B) EXPLAIN SELECT ....... results? Still having this trouble? View profile for free downloadable Utility Scripts to assist with performance tuning. Commented Oct 17, 2021 at 21:05
  • Review your Magento managed tasks related to reindexing frequency, please. You may find this URL significantly helpful for your situation. - devdocs.magento.com/guides/v2.4/performance-best-practices/… - Commented Nov 5, 2021 at 22:00
  • Can you disable cron queue consumer and check. This has a bug and can result in resources getting consumed. Commented Nov 18, 2021 at 18:19
  • The query above is a search on the website, found this out the hard way of running MySQL logging... no other to find out.
    – Doc9
    Commented Nov 18, 2021 at 19:49

2 Answers 2

0

To troubleshoot CPU issues and load time, we can use several tools, such as top or pidstat. To learn more about its installation and usage see this guide

https://yegorshytikov.medium.com/magento-2-high-mysql-cpu-usage-troubleshooting-a0789f1adc31

3
  • 1
    Not asking to track CPU usage, simply asking what in Magento generates the above query.
    – Doc9
    Commented Oct 19, 2021 at 7:05
  • @Doc9 Pay MAGENTO and they can tell you where it comes from, unless it is a hacker that is injecting the query at will. Commented Dec 17, 2021 at 17:01
  • 1
    No need for silly jokes.... as mentioned in the comments above, I found the related query to be from the Magento catalog search module.
    – Doc9
    Commented Dec 18, 2021 at 0:18
0

Magento core developers are all simple coders who know nothing about database performance. Magento seems to have gathered the most clueless developers who neither care about why it's so slow nor have any interest beyond coding what they like

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.