Magento’s checkout throughput can increase to at least 8 times its current capacity and up to 26 times more in ideal conditions.
- The current checkouts per hour limit for large, real-world Magento stores is 4,500
- This limit cannot effectively be increased with more and/or better hardware
- The improper type handling in Magento’s SQL code is the cause of the current limit
- If one SQL query is fixed, large Magento stores can scale to a new, real-world limit of 120,000 checkouts per hour
- For commodity hardware, this new limit might be 36,000 checkouts per hour
Many load and performance tests for Magento have shown impressive numbers for checkouts per hour, but the tests have been performed with invalid conditions. The 2012 publication Optimizing Magento for Peak Performance from Magento lists 487,000 orders per day as the framework’s capacity. This means Magento’s least performant SQL query under test conditions is only 177 ms. Multiple independent parties have seen this query require approximately 800 ms in production for large Magento stores.
Magento’s read operations (SQL SELECT) can scale to nearly as many threads as you’re able to afford to run. The framework’s most expensive write operations—finalizing and saving an order in checkout—are fully and absolutely limited to a single thread.
The fundamental bottleneck is SQL INSERT, UPDATE, and DELETE commands against tables like sales_flat_order_grid and catalog_product_index_price. Only one thread should write to these tables at any given moment, so it is practically impossible for Magento’s checkout throughput to exceed the rate of the slowest average write operation on either of these tables. (Technically it can be done but it violates ACID compliance and threatens referential integrity.)
1 hour = 3600 seconds
4500 checkouts per hour is the maximum known real world vanilla example in a large store (Source: Magento Expert Consulting Group)
700–900 ms is commonly the slowest transaction (catalog_product_index_price - DELETE) in Magento’s checkout for a large store (Source: https://gist.github.com/parhamr/6177160)
3600 / 4500 = 0.8
This is a really good fit between the 4500 checkout limit and the 800 ms query, but this correlation has not been verified as causally linked.
Assuming the above query is improved…
33 ms is the average execution time for sales_flat_order_grid - INSERT — the next-slowest, single-threaded bottleneck during checkout (source: 3 months of New Relic data for a large cluster)
3600 / 0.03 = 120000 checkouts per hour
To be pessimistic…
100 ms as the new, single-threaded bottleneck should be easily attainable in less than ideal conditions, assuming the above query is fixed
3600 / 0.1 = 36000 checkouts per hour
| table | operation | average response time |
|---|---|---|
catalog_product_index_price |
DELETE |
806 ms |
sales_shipping_aggregated_order |
DELETE |
95.6 ms |
catalog_product_index_price |
INSERT |
35.2 ms |
sales_flat_order_grid |
INSERT |
33.4 ms |
@aschroder Here is a PoC for async orders https://github.com/magento-hackathon/MongoDB-OrderTransactions
The PoC from @punkstar does not relate to orders. Only for some custom observers.
The risk with the stock amount can kill your business. It must be for 100% sure that the customers get their ordered quantity.