Shard key choice
Three shards do not help when one key gets most of the writes. A user key puts 12,000 writes/s of 18,000 on one shard, and a date key puts all 18,000 on one. An order-ID hash sends 6,000 writes/s to each shard.
See it on the board
The same traffic runs on two boards. The simulator measured both when this page was built.
| Measure | Goal | Shard by user | Shard by a hash of the order ID |
|---|---|---|---|
| p99 latency (*) | under 100 ms | 880 ms Fail | 4.9 ms Pass |
| Availability (*) | at least 99.5% | 84.6% Fail | 100% Pass |
| CPU use | under 85% | 120% Fail | 60% Pass |
| Monthly cost | 12 USD/mo | 12 USD/mo | |
| p99 latency | 880 ms | 3.6 ms | |
| Error rate | 15.4% | 0% |
Key numbers
| Number | Value |
|---|---|
| Writes per second, one database vCPU | 10,000 writes/s |
| Reads per second, one database vCPU | 30,000 reads/s |
| Default partitions | 1 partitions |
Order ID hash · busiest shard 6,000 writes/s, one shard takes 10,000
How it works
Sharding splits data over several servers, and the shard key decides which server gets each row. The key must spread the load. If most rows share one key value, one shard gets most of the work, and the other shards sit idle.
In the mini board one user sends two thirds of all new orders, and all orders carry today's date. The total is 18,000 writes/s over three shards. If the shard key is the user, the large user keeps its 12,000 writes/s together on one shard, which then overloads. If the key is the date, all 18,000 writes/s land on the shard for today.
A hash of the order ID gives each order an almost random value. The orders spread evenly, so each shard gets about 6,000 writes/s. All three shards work in parallel, and the p99 and the CPU stay inside the limits.
The choice has a price. A key that spreads writes well may scatter the reads. A query for all orders of one user must now ask every shard. Pick the key from the access pattern, and check the skew: find the largest key value, and see how much of the load it carries.
When one value is large, such as a very busy user, add a suffix to split it, or give it its own shard.
In the levels you pick the key and check the load of each shard.