Sharding

Sharding is nearly every web 2.0 sites answer to database scalability.

The advice is to design to allow sharding.

If/when predict.ly becomes immensly popular, data should be sharded.

However, I'd really like to play sharding now. Therefore I've devised (after quite some hours) a scheme to shard the data. Based on what I've read it is fundamentally the web 2.0 scheme of sharding by user ID.

Everything on the site is created/owned by a user (even system users). All keys will be a combination of user/item id. Mysql autonumbers are not really viable anymore so a basic sequence implimentation is needed.

I've gone over the scheme 20 times and I'm fairly confident. The problem will be getting zend framework/doctrine to deal with the sharding at the application level. This looks very useful on dealing with it in doctrine/zend: http://blog.routydesign.com/?p=62

The other issue is - I don't have the hardware to run sharded databases. I only have 2 database servers. However the shards will be placed in differing database names on the same servers. No cross database queries are allowed (not sure if mysql even supports them to be honest).

This will to all intense purposes create an application level that is using a sharded database. When the database actually needs to scale, I will migrate the shards out initially to 2 additional databases which will be a very natural migration as the whole databases can be moved in a single action.

To reduce the impact of this admitidly too early optimisation there will only be 2 shards. The stages that require to query multiple shards will be doubling up on work, but thats the price of sharding.

The biggest potential penalties are tags and comments.

Tags
Initially the tag table will be in a database of it's own with no joins. Each prediction is limited to 5 tags - so a simple select with 5 primary keys to get the tags for a prediction. Getting tags associated with a user will be similar user > predictions > select tags by primary key. If the tags database has to be sharded, then multiple queries will be required.

The problem becomes finding out what tags are the most common. Each user shard will need to queried and then an aggregate produced. Luckily this is not an operation that is required frequently.

Comments
Loading the comments is simple enough because the comments for a prediction are held in the same shard, simple join. Each comment has an author, and we need to know some detail about the author: name/rank/avatar/etc.

This is a killer. It's fine when there are 2 shards. You have at worst 2 queries per prediction view for the comments. However as user is the primary shard mechanism, lets say it went to 26 shards - that's 26 queries just for the comments.

All advice talks about denormalising the data, but if that users rank/avatar change then you'd need to make the update potentially on all 26 shards. Now that I think about it, maybe that isn't too painful? How often would the image url/rank change? Seldom writes compared to heavy reads. Denormalisation might just be the key.

I had toyed with the idea of recording comments/vibes asynchronously however I'm now of the opinion it would damage usability too much. Instead the user posting the vibe/comment will see the change immediately - other users will experience a delay as the cache refreshes however they won't actually know it's delayed. I think the lesson is to show change to the person making the change to confirm it has occurred happily. Everyone else can wait a little while.