adapt.com uses sharding to scale out. In my talk on moving to MySQL, I mentioned a few design issues we had to become aware of before we rolled out a sharded database environment. I promised to give more details, so here they are…
We wanted some keys to be globally unique (eg, userid and accountid). How would we do this?
auto_increment_offset and auto_increment_increment
We had been using autoincremented values. So we considered using MySQL’s auto_increment_offset and auto_increment_increment to keep autoincrement values unique across shards. (This basically tells each shard to start at a certain number (auto_increment_offset), eg 1 for one shard, 2 for the next, etc, then to skip auto_increment_increment between values. So, eg, if we set that to 1000, our next keys would be 1001 for shard one, 1002 for shard 2, etc. Then 2001 for shard one, 2002 for shard 2, and so on. This would allow up to 1000 shards.) But we were worried that we would have to be careful to roll out shards with correct, unique values for auto_increment_offset, and thought there might be a better way.
We considered using a UUID. MySQL has a UUID() function. However, we also wanted to be able to use replication. With MySQL 5.0, apparently UUID’s can cause a problem for replication. If you use MySQL’s UUID() function, when you replicate, the statement you used is sent to the slave and executed there. But that means the slave will also call UUID(), creating a different value on the slave. (MySQL 5.1 promises row-based replication – ie, the generated values themselves are sent to the slave, not the SQL statement.)
So we considered using a UUID generated in the application code, and executing a statement with the UUID value in it. But then we wondered about the efficiency of using a UUID for an InnoDB primary key. (How would we store it? As a char(36)? Big for an InnoDB primary key. Also, UUIDs are a bit ugly for reading and working with. (See the MySQL Performance Blog entry “To UUID or not to UUID” for performance considerations.)
Then we thought of using a global view, using the federated storage engine (think database links in Oracle), that would select from each shard. We thought our code could check this view and generate a next value and use it. But then, if any shard fails, the view will bomb out, and you’re out of luck. So we dropped that idea.
What we ended up doing is have a small, “global” database that has tables for some of our keys that need to be unique. The code inserts into them, gets the values, and uses them to populate the appropriate shard.
Next issue – what about foreign keys?
Currently – as far as I know – you can’t enforce foreign keys across servers. So we set up code to check and monitor some of our foreign key relationships and alert us if it finds any anomolies.
And that is how we arrived at sharding. So far so good…