Changes

* Each master synchronously replicates to a one or more '''hot standby''' dbs in the same DC, to guard against individual machine failure.

* Each master The entire DC is asynchronously replicates replicated to a '''warm standby''' setup in a separate DCanother region, to guard against whole-DC failure.

* All sharding logic and management lives in a stand-alone proxy "db router" process, so that it's transparent to the application.

== Transparent Sharding Proxy DB Router ==

The application code is actually talking to a proxy "db router" server that speaks the MySQL wire protocol. In turn, the proxy router is talking to the individual MySQL servers that are hosting each shard:

+---------------------------+ +----->| MySQL Server for Shard #1 | +---------+ +----------------+ | +---------------------------+ | Web App |------->| Sharding Proxy DB Router |---+ +---------+ +----------------+ | +---------------------------+ +----->| MySQL Server for Shard #2 | +---------------------------+

The proxy process db router will:

The particulars of shard selection/lookup are not defined in this proposal, and are orthogonal to the rest of the setup. :rfkelly likes the consistent-hashing-plus-vbucket approach taken by couchbase, but it could be as simple as a lookup table. We assume that the proxy router implements this appropriately and efficiently.

Handling all the sharding logic in a separate proxy process gives us a number of advantages:

* The proxy router can do centralized health monitoring of the individual servers, handle failover, etc.

To guard against the loss of any individual database server, each shard will also have a hot standby database, living in the same DC and configured for synchronous (semi-synchronous?) replication. For AWS it would be in a separate Availability Zone. The proxy router monitors the health of the standby database, but does not forward it any queries. Its only job is to serve as a backup for the active master:

+---------------------+ +----->| Master for Shard #1 | | +----------+----------+ | | (replication) +---------+ +----------------+ | +----------V---------------+ | Web App |------->| Sharding Proxy DB Router |---+----->| Hot Standby for Shard #1 | +---------+ +----------------+ | +--------------------------+ | | +---------------------+ +----->| Master for Shard #2 | | +----------+----------+ | | (replication) | +----------V---------------+ +----->| Hot Standby for Shard #2 | +--------------------------+

The proxy router process is responsible for monitoring the health of these machines and sounding the alarm if something goes wrong. If the active master appears to be down, the proxy router will transparently promote the hot standby and start sending queries to it. When the downed master comes back up, it is demoted to being the new standby.

We don't want any single-point-of-failures, so we'll have to have multiple instances of the webapp talking to multiple instances of the proxyrouter. These are connected via loadbalancing, virtual IPs, and whatever Ops wizardry is required to make single-machine failures in each tier be a non-event:

+--------------+ +------------------+ | Web App Tier | | Shard Proxy DB Router Tier | +---------------------+ | | | | +-->| Master for Shard #N | | +---------+ | | +-------------+ | | +----------+----------+ | | Web App | |--->| | Shard Proxy DB Router | |-----+ | (replication) | +---------+ | | +-------------+ | | +----------V---------------+ | +---------+ | | +-------------+ | +-->| Hot Standby for Shard #N | | | Web App | | | | Shard Proxy DB Router | | +--------------------------+ | +---------+ | | +-------------+ | +--------------+ +------------------+

Rendered concretely into AWS, we would have an Elastic Load Balancer and corresponding Auto-Scaling Group for each of these Tiers. The ELB provides a single endpoint for each service to contact the next, while being a magical auto-cloud-managed non-single-point-of-failure itself:   +--------------+ +----------------+ | Auto-Scale | | Auto-Scale | +---------------------+ | | | | +-->| Master for Shard #N | +-----+ | +---------+ | +-----+ | +-----------+ | | +----------+----------+ | ELB |--+--|->| Web App |-|--+-->| ELB |--+--|->| DB Router | |-----+ | (replication) +-----+ | | +---------+ | | +-----+ | | +-----------+ | | +----------V---------------+ | | +---------+ | | | | +-----------+ | +-->| Hot Standby for Shard #N | +--|->| Web App |-|--+ +--|->| DB Router | | +--------------------------+ | +---------+ | | +-----------+ | +--------------+ +----------------+  With multiple Shard Proxy DB Router processes, we run into the problem of shared state. They must all agree on the current mapping of userids to shards, of shards to database machines, and which database machines are master versus standby. They'll operate as a ZooKeeper (or similar) cluster to store this state in a consistent and highly-available fashion:

| Shard Proxy DB Router Tier |

| | Shard ProxyDB Router: | | Shard ProxyDB Router: | |

| | Proxy Router Process | | Proxy Router Process | |

Note that this shard-state metadata will be very small and be updated very infrequently, which should make it very friendly to a local zookeeper installation. We might even be able to provide a nice web-based status view and management console.

We'll replicate the entire stack into several a second data-centerscenter, each of which will maintain a full backup copy of all shards. In concrete AWS terms, this means a second AWS Region.

One DC will be the active master for each shardall shards, and the other is purely a backup. All reads We could have it accepting web traffic and writes for that shard will be forwarded into that DC and routed forwarding all DB queries across to the master, or we could remove it from DNS entirely unless we need it. Other DCs are Every shard has a designated as warm-standby hosts for that shardhost in this DC, configured for asynchronous WAN replicationfrom the hot standby (so that the master doesn't have additional load from this replication). They can be failed-over to if there Likewise, the internal DB Router state is a serious outage in replicated into the master second DC, but this will almost certainly result in the loss of some recent transactions:

For Since this scheme is replicating cross-DC, any attempt to work, every Shard Proxy process in every DC needs fail over to agree on where the master is for every shardwarm standby will almost certainly lose recently-written transactions. That's We should probably not trivialtry to automate whole-DC failover, so that Ops can ensure consistent state before anything tries to send writes to a new location.  '''TODO:''' How many DCs? But it The principle should be pretty doable with some replication between the respective ZooKeeper masters same regardless of how many we have. Nested Star Topology FTW. '''TODO:''' We could potentially fail over to the second DC for individual shards, if we happen to lose all DBs for that shard in each the master DC. Remember: this data is small and changes infrequentlyAt the cost of sending DB queries to a separate region. Worth it?

'''TODO:''' Will For a final level of redundancy, we wind periodically snapshot each database into long-term storage, e.g. S3. Likely take the snapshot on the least up in a situation where different shard have their master db in different DCs? Does -to-date replica to minimize the chances that it matter?would impact production capacity.

'''TODO:''' Do we want As well as providing redundancy, these snapshots allow us to try quickly bring up another DB for any sort of geo-locality with data storage, ea particular shard. E.g. putting if we lose the master for European users in Europe? This could greatly complicate the assignment of users hot standby, we can start a fresh one, restore it from a snapshot, then set it to shardswork catching up from that point via standard replication.

Using a single master for each shard means we don't have to worry about conflicts or consistency. The sharding means this should not be a bottle-neck, and the use of an intermediate proxy router process means we can fail over fast if the master goes down.

== Implementing the Proxy Router ==

The Sharding Proxy DB Router process is obviously key here, and looks like a reasonably complex beast. Can we use an off-the-shelf solution for this? There are some that have most of the required features, e.g. ScaleBase.

On the other hand, the feature set seems small enough that we could realistically implement the proxy router in-house, with the benefit of tighter focus and greater control over the details of monitoring, failover, replication etc.

* There's quite a few moving parts here. ZooKeeper is a beast. The proxy router process has a few different, interacting responsibilities that would have to be carefully modeled and managed.