I'm creating my own sharding solution with MySQL. I develop my application in ASP.NET 4.5.
My idea is to create a document wit the available shards for every table:
components_servers[
{shard_id: 1, is_locked: false, hostname: 127.0.0.1}
{shard_id: 2, is_locked: false, hostname: 127.0.0.1}
]
This document will be stored on DynamoDB. I'm starting with two shards for the 'components' table.
When I insert data, I read that file, filter out the is_locked = false ones. I get the total_number_of_shards (ex. 2) and use MOD total_number_of_shard on the part of my primery_key to distribute the data evenly across shards.
Of course in the above configuration, all the data will get to server 127.0.0.1. NOt let's say that the data capacity is already at 100GB and I want to add another server, all I have to do is to change the second shard like this to 127.0.0.2:
components_servers[
{shard_id: 1, is_locked: false, hostname: 127.0.0.1}
{shard_id: 2, is_locked: false, hostname: 127.0.0.2}
]
Problem 1:
I can decide whether to lock the first shard of leave it unlocked, but the problem is that I am not using rebalancing so now shard 1 will have more data than shard 2 and if I lock the first one all inserts will get to shard 2. I don't want to move data, and I wonder if there is a way to solve this.
The thing is that I want to start with one server (start cheap) and then add a new server when I need to grow in capacity and write performance. Because I use Amazon RDS, replicas can be created to increase read performance, but I do care about write performance hear.
Problem 2:
Choosing the right shard key. I have a 'components' table and 'items' table. Each components can hold 1 or more items. My first idea was to create the shard key as follows:
server_type_id + shard_id + total_components_for_user
server_type_id: can be 'components_servers' which is a number like 100, 200, 300, etc.
Shard_id: the shard that the data will be written to - three digit number (eg. 001)
total_componenets_for_user: each user can create as many components as he wants. I save the total number of components in each user row, so when I insert the data, I will always increase that number by +1, and use the total number in the sharding key.
That shard key isn't good for READS, because new components will be read much more frequently. I need a recommendation for a shard key that will help to better distribute the read across the shards.
Problem 3:
sorting and paging on sharded servers. Let's say that I want to get page 1 with 10 components, etc.
My Goal:
To create my own sharding strategy that will be easy to implement against Amazon RDS, and add servers will be easy buy just launching a new RDS copy the schema and change the configuration file.
My configuration file should be in a certerlized place, I've chosen DynamoDB for that, but I might store that in every database instead for fast access.
I think that sharding won't need to be that hard when using it with Amazon RDS and backup and restore are very easy, just backup each Amazon RDS server.
It's also important to note that Components table rows and Items table rows that are related (ie. Items.component_id = Components.id) will be store on the same table. So if one server failes it won't hurt the whole application.
Another consideration is that every shard will be mapped only to one server. By doing so, SELECT queries can be done on a SINGLE server, instead of several servers (without this change: when I move from one shard to two shards, so now shard 2 also have some of the data of shard 1, and if I extract the shard data from the URL, I will need to query those two servers, because I don't know on which server the data resides - I don't want to change the URL that holds the primary key ie. http://domain.com/111001233456).
Another option is to create several logical shards a head of time.
As you can see, some problems arise and I need your help to solve those so I can make my own sharding against Amazon RDS and not pay thousands of dollars each month to other 3rd party solutions. Thanks.
