Possibly related to: Slow Save Order Due To Re-Index
- CE 18.104.22.168 (soon to be 1.9.3.x)
- 150+ orders per hour on CyberMonday and later in the holidays
- 40k SKUs
- 3 websites, 3 stores
- 2 customer groups
- DB has been upgraded from 1.3 -> 1.5 -> 1.6 -> 1.9.x
- OMS pulling down orders to warehouse every 15 mins
This is a legacy client of mine hosted at Nexcess. They continually have post-order pricing indexing queries hang and lock up tables and subsequently the whole site. I've done substantial comparison to vanilla CE database and I'm 95% sure we're not missing an index.
We have all automatic indexing disabled and have attempted to use the hackathon AsyncIndex extension. https://github.com/magento-hackathon/AsyncIndex It does appear to help but doesn't stop the queries hanging.
Has anyone attempted to off-load those indexes similar to how Ivan C describes in the question above? Other than throw additional hardware at it, I'm thinking that could be the best solution.
My gut feeling is that it happens when two orders are saved simultaneously.
Usually the queries that hang are:
INSERT INTO `catalog_product_index_price` (`entity_id`, `customer_group_id`, `website_id`, `tax_class_id`, `price`, `final_price`, `min_price`, `max_price`, `tier_price`, `group_price`) SELECT `catalog_product_index_price_tmp`.`entity_id`, `catalog_product_index_price_tmp`.`customer_group_id`, `catalog_product_index_price_tmp`.`website_id`, `catalog_product_index_price_tmp`.`tax_class_id`, `catalog_product_index_price_tmp`.`price`, `catalog_product_index_price_tmp`.`final_price`, `catalog_product_index_price_tmp`.`min_price`, `catalog_product_index_price_tmp`.`max_price`, `catalog_product_index_price_tmp`.`tier_price`, `catalog_product_index_price_tmp`.`group_price` FROM `catalog_product_index_price_tmp` ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `customer_group_id` = VALUES(`customer_group_id`), `website_id` = VALUES(`website_id`), `tax_class_id` = VALUES(`tax_class_id`), `price` = VALUES(`price`), `final_price` = VALUES(`final_price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`), `group_price` = VALUES(`group_price`)
INSERT INTO `catalog_product_index_price_tmp` SELECT `catalog_product_index_price`.* FROM `catalog_product_index_price` WHERE (entity_id IN('###', '###'))
Though we occasionally get an integrity constraint. Presumably because an earlier query was killed manually.
SQLSTATE: Integrity constraint violation: 1062 Duplicate entry '24683-0-1' for key 'PRIMARY', query was: INSERT INTO `catalog_product_index_price_tmp` SELECT `catalog_product_index_price_final_tmp`...
DB server is a few years old but has 24GB RAM. We could move over to AWS and use RDS potentially. Could simply be hardware limitation.
Thanks for your thoughts on offloading the post-order price indexer or any suggestions for Magento optimizations or possibly for additional MySQL indexes that could help.