Using Magento 2.3.1 EE, 50k products and 14 store views. The catalog_product_price reindex gets stuck with this query listed in SHOW FULL PROCESSLIST

 DELETE `price_index`  FROM `catalog_product_index_price_temp` AS `price_index`
 INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.entity_id = price_index.entity_id AND (product_entity.created_in <= '1553317143' AND product_entity.updated_in > '1553317143')
 LEFT JOIN `inventory_stock_1` AS `inventory_stock` ON inventory_stock.sku = product_entity.sku WHERE (inventory_stock.is_salable = 0 OR inventory_stock.is_salable IS NULL) AND (price_index.website_id = 2) AND (price_index.entity_id IN ('129058', '129059', '129060', '129061', '129062', '129063', .....))

The catalog_product_index_price_temp table seems to be temporary. max_heap_table_size and tmp_table_size are both 16777216.

That price_index.entity_id IN has thousands of IDs and the query state stays at "Sending data" for hours. Does somebody have any idea what could be wrong?

  • 1
    After some digging I believe that it is related to this github.com/magento/magento2/issues/22516 we have adjusted the batch size from 5000 to 1000 and seems to be processing faster
    – acampos
    Jun 10, 2019 at 11:36
  • please post an asnwer
    – Amit Bera
    Jun 10, 2019 at 13:34
  • Hi @AmitBera I don't have an answer yet, adjusting the batch size improved the processing speed but it is still very slow for production. Still looking into this
    – acampos
    Jun 11, 2019 at 8:05
  • You can post whatever you got the solution. At least, not is stuck at now
    – Amit Bera
    Jun 11, 2019 at 8:06


Your Answer

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