We're running CE 22.214.171.124 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?
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!