We have a Magento 2 store which seems to have a query automatically respawning multiple time, even if you kill it.
The Inno Buffer Pool is at 99% even at 50G on a 64G dedicated physical server.
Handler_read_rnd_next is huge even with sql optimisations in place.
SELECT e.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS status, at_visibility. valueAS visibilityFROM catalog_product_entityAS eINNER JOIN catalog_product_entity_intAS at_status_default ON ( at_status_default. entity_id= e. entity_id) AND ( at_status_default. attribute_id= '97') AND at_status_default. store_id= 0 LEFT JOIN catalog_product_entity_intAS at_status ON ( at_status. entity_id= e. entity_id) AND ( at_status. attribute_id = '97') AND ( at_status. store_id= 1) INNER JOIN catalog_product_entity_intAS at_visibility ON ( at_visibility. entity_id= e. entity_id) AND ( at_visibility. attribute_id = '99') AND ( at_visibility. store_id = 0) WHERE (( e. updated_at >= '2024-01-22T10:24:06')) AND (( e. updated_at< '2024-01-27T16:25:47')) ORDER BY e. updated_at ASC LIMIT 50 OFFSET 100
Really need help to find out why this query keeps running and how we get the performance of the site under control.