Automatically sharding MySQL?

Question

Right now, I'm dealing with a TON (trust me) of data that needs to be available in real-time for fast reads and writes to customers. The backend storage system that we're using is Oracle, but we'd like to replace our big, beefy machines with a leaner system.

For various reasons, we can't use Cassandra, and we're testing (but I'm scared of) MongoDB (it's really young and is missing some critical features), so I was thinking about sharding a bunch of MySQL instances.

Is there a good system to manage this, or do I have to roll my own? I've found a few projects but it's unclear to me if they support adding/removing shards on the fly.

Answer 1

You can definitely implement database sharding with MySQL very effectively. If your sharding scheme is simple it can be done in your application layer, but if its more complex you may want to use a tool. A lot of the options are described on our site here, as well as the advanced options we support.

You can learn more about the options here:

http://www.dbshards.com/articles/database-sharding-whitepapers/

Also its important to consider the entire lifecycle for a sharded environment, including failover, active-active replication, backups, restores, and re-sharding as you mentioned above.

FYI we have customers reading and writing very high volumes with 1000s of concurrent users in cloud environments (with slow I/O), and even faster in data center environments. Sharding definitely can be incredibly effective, with writes scaling linearly and reads often better than linear (because more data is cached in the database).