Magento 2 Extremely high load on MySQL due to select query

Question

I've seen similar questions asked on this site, but haven't seen a solution or fix to resolve the problem. Our site is spawning a large number of select queries to the point where mysql is overloaded and causing sever capacity problems. Here's the query:

SELECT e.*, at_inventory_in_stock.is_in_stock AS inventory_in_stock, stock_status_index.stock_status AS is_salable, links.link_id, links.product_id AS _linked_to_product_id FROM catalog_product_entity AS e INNER JOIN cataloginventory_stock_item AS at_inventory_in_stock ON (at_inventory_in_stock.product_id=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1)) INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id INNER JOIN catalog_product_link AS links ON links.linked_product_id = e.entity_id AND links.link_type_id = 6 INNER JOIN catalog_product_entity AS product_entity_table ON links.product_id = product_entity_table.entity_id WHERE (stock_status_index.stock_status = 1) AND (links.product_id in ('27120')) AND (e.entity_id != '27120')

Our site has about 11000 products listed. This appeared out of the blue about 1 week ago and we do not have a solution.

Any help would be great! Thank you!

Answer 1

For 11000 products listed, I think that select query is fine. Maybe your page is uncached due to some bad code, so whenever a customer visits your page (the category page and catalog search) the select query will run again, it caused Mysql to be overloaded and causes sever capacity problems.

Firstly, you or your developer should check if your page is cacheable or not. If so, you have to implement to make your page cacheable again (the category page and catalog search are catchable by default).

In addition, I recommend you upgrade your magento site from 2.3.x to 2.4.x because in magento 2.4, Elasticsearch will replace MySQL as catalog search engine and Elasticsearch provides superior search capabilities as well as catalog performance optimizations.

If you would like to upgrade to magento 2.4.x, I'll write more detail about which version you should upgrade to at this time because there is a big change in magento version < 2.4.4 (use PHP version 7.x) and > 2.4.4 (use PHP version 8.1).