We're running CE 1.9.1.0 with all the latest patches and use New Relic, among others, to track site performance. I've noticed a number of slow queries when customers are checking out, and in every case it is due to CatalogInventory re-index events that are triggered automatically when placing an order. It seems like the logic and processing item used for re-indexing was put in the wrong place and forces the customer to wait for maintenance tasks in Magento to complete, something that should happen in the background, after the order is done and the customer is happy. I've looking around for index-improving extensions, but they all seem to focus on improving the indexing processes when saving things in the admin to improve product edit performance. We don't care about that, we care about the customer experience, which seems to be hurt greatly by bad Magento performance.
Below are some screenshots of a recent trace. This customer bought 10 individual items. Each item triggers a re-index for that item, and each time it's taking 2-3 seconds to do the re-indexing. Multiplied out by 10 products, that's 30 seconds of the customer sitting their wondering if things are going to complete while Magento does what should be background tasks.
My first question is, is this normal Magento performance or does this smell of a bigger problem? If this is par for the course, any suggestions? I could disable automatic re-indexing all together and set up a cron job to do it overnight. That's a doable solution, I just worry about the implications if we sell out of a product and it doesn't re-index right away. Would someone else be able to buy it?
This picture shows the trace, collapsed down to show only the product save events for each item purchased
This picture shows a single index event, expanded to show that it actually processes 4 different index events in this one event, each one taking 1/2 second or more
This picture shows a single event, expanded out fully, and includes the query
At first glance you might say that 115ms for one DB transaction isn't much. But, taken with the other slow tasks, and compounded by the number of items the customer buys, you end up with exceptionally long transactions for the most loyal, highest paying customers, and that's exactly what you DON'T want. Any help anyone can provide would be greatly appreciated!
Best Answer
The only solution to deal with this problem is to put all indexers into the background. Re-indexation might not only make your store slow but also introduces a chance of deadlock when two concurrent orders bring your order out of stock.
The best way to achieve it:
Mage_CatalogInventoryobserver forsales_model_service_quote_submit_successevent.