I'm trying to build my own sharding strategy as follows. Let's assume that I have a BOXES and ITEMS table, each box can have several items. I put the items that are related to the same BOX in a single machine.
The box_id primary key contains: server_type (ex. 100) + shard_id + total_amount_of_boxes_per_user
The total_amount_of_boxes_per_user is stored in the users' database for each user and I increment it by one each time the user inserts a new box.
The server type 100 will be lineup with the list of servers that stores the box+items data. This list of server_type->shard relationship should be in a central location, I thought about storing it on DynamoDB as a document.
The configuration document on DynamoDB:
boxitems_servers[
{shard_id: 1, is_locked: false, hostname: 127.0.0.1}
{shard_id: 2, is_locked: false, hostname: 127.0.0.2}
{shard_id: 3, is_locked: false, hostname: 127.0.0.3}
{shard_id: 4, is_locked: false, hostname: 127.0.0.4}
]
I modeled my database and my application layer so I won't need to make joins. At most, I will be making several queries to the DB, but those will be cached at the server and client side. I am using MySQL and developing my application in ASP.NET 4.5.
When a user hit the page:
http://domain.com/1000014294967295
I can read that data, split it and get the following:
I get the boxitems_servers document from DynamoDB and only the document of the server_type. So server type 100 = boxitems_servers.
I make a connection to the shard based on the hostname (credentials are in the web.config) and query the data based on the primary key 1000014294967295.
I can decide to lock a specific shard by putting is_locked: true in the configuration document. So when writing data (not updating) it will write only to the unlocked shards.
I will write data by using MODULU on the shard_id % number_of_active_shard to evenly distribute the data across several shards.
Now if I want to add another Amazon RDS database to scale horizontally, I just create the database with the same schema via an Amazon AMI that I've created previously and add the server to the shards list.
boxitems_servers[
{shard_id: 1, is_locked: false, hostname: 127.0.0.1}
{shard_id: 2, is_locked: false, hostname: 127.0.0.2}
{shard_id: 3, is_locked: false, hostname: 127.0.0.3}
{shard_id: 4, is_locked: false, hostname: 127.0.0.4}
{shard_id: 4, is_locked: false, hostname: 127.0.0.5} <- NEW ONE
]
Amazon RDS already has replication so I don't need to worry about that. Back/restore are easy too.
My only concerns are:
What I need: I want your opinion about that strategy. I want to make some kind of plug-n-play architecture that I can use Amazon RDS and scale easily by adding more machines and updating the config file. This should work on the fly without any downtime.
I don't want to pay thousands of dollars to all those expensive solution out there. I believe that I can built a good sharding solution that will fit my application needs, which has a few tables and those already de-normalized to prevent joins. Amazon RDS already provides the replication that I need.
I can also created logican shards and each shard_id can be changed to point to another DB machine (IP Addres), but then when I query the 'leaf', if I can't find the data there, I need to move up and query the other shards until I find the data.
I think that this can lead to a good sharding strategy, which has its limitations, but can work pretty well for high traffic websites (I think).
I don't think the MOD strategy is the best one, because if you add a node, you have to move every single record to a different database (which i understand is a bad option).
A better option (like the Cassandra one) is to hash the key and split the whole keyspace in chunks.
As an example if hash gives an answer between 0 to FFFF in hex (this should be full md5 or sha1)
This is so that is you look for a single register, you request only at that node, if you need more registers, you may end up requesting all nodes. Depends on what you choose as key to locate your data (it doesn't need to match the pk)
If you need to add more nodes, you just split what you have in for example node 3, and in the example above, you get from 5000 to 6FFF is left in node 3 and from 7000 to 8FFF goes to a new node 6.