magento-scaling-hypothesis.md

Magento Scaling Hypotheses

Magento’s checkout throughput can increase to at least 8 times its current capacity and up to 26 times more in ideal conditions.

1. The current checkouts per hour limit for large, real-world Magento stores is 4,500
2. This limit cannot effectively be increased with more and/or better hardware
3. The improper type handling in Magento’s SQL code is the cause of the current limit
4. If one SQL query is fixed, large Magento stores can scale to a new, real-world limit of 120,000 checkouts per hour
5. For commodity hardware, this new limit might be 36,000 checkouts per hour

Background Assumptions

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.)

Some Numbers

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

Four Slow Checkout Queries

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 there is this PoC from @punkstar that I've been meaning to test for a while (https://github.com/punkstar/magento-delayed-events).

@davidalger and @parhamr Seems crazy that type handling could be so expensive, but certainly feasible!

I join all people above. Nothing more to add.

Well why it's always that the theory is not meeting practice.

@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.