Re: sharding steps
Posted by:
Rick James
Date: September 10, 2011 10:54AM
First thing to ask: Do you have only one huge table (plus smaller ones) and it is only indexed by the PRIMARY KEY? If so, then sharding is "easy". You devise a way to turn the PK into a host (more below) and spread that huge table across multiple machines. Your code deals with connecting to the right machine before issuing the INSERT/SELECT/...
Any ancillary tables are either kept on all machines (then you have issues of syncing them) or kept on one machine.
How to split among machines?
* Pure hash -- hash the key, map that to a host
* Pure dictionary -- have a lookup table that maps key to host
* Hybrid -- hash to, say, 4096 values; map (many-to-one) those values to machines.
A maintenance issue: If too many items land on one machine, or those items are too 'busy', then you need to migrate items off that machine to other machine(s) or a new machine. 'Pure hash' does not allow such; 'pure dictionary' lets you migrate individual items as needed; 'hybrid' requires that you migrate batches in unison (usually this is 'good enough' and 'fast enough').
If you have two huge tables, you could shard each one independently.
JOINs -- now things get messy. You would need to simulate the JOIN in your code.
Fetching more than one row from the sharded table -- Another messy situation. Some options:
* Multiple 1-row fetches.
* Code on each shard to get what it can for the query, plus centralized code to stitch the results together.
See Spider; NDB Cluster.
If you would like to describe your application further, I can get in to specifics. Need to know something about the sizes of the important tables, selects, how the data is loaded, etc.
Think out of the box. Do you really need all the data accessible via SELECT? That is, can you restructure your application and/or data such that it will fit on a 'normal' server?
Even if you are reinventing Flikr/Facebook/YouTube/etc., those are not really sharded databases. They simply have the items scattered across servers. Plus, there is a central, unsharded, database containing an 'index' of the items. (Yeah, yeah, it is more complicated than that.)
Numeric data can usually be summarized into a database small enough to sit on a normal server.