- Based on no of rows in shard
- Based on no of customers in shard
The reason to not always pick the first least used shard is that 6 months down the line if we add 2 more shards to cluster then every new registration would pick those two shards only. The way our customers use the system is that they register and play with the system for a while during the 15 day trial period and if they like the solution (which they do) then they upload their real dataset. During the trial period the customer would upload may be 100-1000 files/folders but when they upload the real dataset then they would upload 1-2 million files. So always picking the least used shard would cause hotspots. That's why we pick the least 8 used shards and randomly pick one of those shards.
This gives us uniform distribution here is a screenshot of 5 random shards from our Graphite dashboard.
As you can see the rows are evenly distributed, you could get many small customers on 1 shard and 1 big customer can take up 1 shard. As we use memcache many small customers on 1 shard will not cause issues for reads. Its usually the bigger customers with 3-5 M files that cause issues.
One key thing to note is that information schema can be slow when you run select query on it. If the statistics on table are obsolete, then select query on information schema can cause mysql to scan the table and calculate statistics, which can be bad for registration. So I wrote a quartz job that every hour find the 8 least used shard per cluster and populates in memcache and registration process just uses that. If for some reason the data is not in cache cache then registration process queries and populates it.
Part1 of series
Part2 of series
Part3 of series
Part4 of series