We are running Magento 2.3.1 which has been running properly for a while.
Recently the site began to experience extremely long wait times whenever hitting pages not cached by Varnish such as searches and checkout pages.
Looking at the CPU usage on our box it seemed that MySql was consuming nearly all of the it. Next, I took a look at what queries were being processed using the show full processlist command. This returned a bunch of the same queries stuck in the Sending Datastatus. The Time column showed values ranging from 10 to 10,000 with the amount of process growing with each added iteration of the query.
Killing all of these processes fixed the usage for a short time until these queries piled back up causing the site to slow to a crawl once again.
Here’s the query:
SELECT `product_table`.*,
`product_table`.`entity_id`
FROM `catalog_product_entity` AS `product_table`
LEFT JOIN `catalog_product_entity_int` AS `status_global_attr`
ON status_global_attr.attribute_id = 97
AND status_global_attr.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `status_attr`
ON status_attr.attribute_id = 97
AND status_attr.store_id = 1
WHERE ( product_table.entity_id IN( '132617' ) )
AND ( Ifnull(status_attr.value, status_global_attr.value) = 2 )
The queries are all the same as this one with the exception of the attribute_id being different for each.
I spent the past couple of days trying to track down what is issuing the query and why it is taking so long to complete but haven’t been able to figure it out.
If anyone knows why this is happening, or how to diagnose further it would be greatly appreciated.
