Here's what I do in these situations (I'm an Oracle DBA, but this should apply to most loaded shards):
1) Use connection pooling at the application layer to prevent overloading the DB of any specific shard. This means that if a shard has 16 CPUs, having 16 connections sounds reasonable. Additional connections will not give you more performance.
This means you need to queue and throttle requests at the application layer and with some thought you can probably figure out what to do with the waiting users - show partial results? show a nice whale? A "loading please wait" sign?
2) If you didn't do #1 and the DB is getting overloaded, my normal response is to start shooting down connections. Oracle has separate unix process per connection. MySQL has its own way of shooting connections down. Put up a small script that will kill the correct percentage of sessions to prevent overload on shared resources. This will generates lots of errors and will cause a percentage of the users to hate you, but you won't be down.
1) Application connection pooling won't scale. In a scenario like FourSquare, there is likely a 4:1 ratio of app server to DB shard server. Further, connections don't necessarily equal load.
2) This sounds like a great way to create data inconsistencies, unless you've got very tight constraints on your database, which is impossible in a sharded scenario.
I agree though, that ultimately they should have had some way to "fail whale" instead of getting overloaded.
1) Use connection pooling at the application layer to prevent overloading the DB of any specific shard. This means that if a shard has 16 CPUs, having 16 connections sounds reasonable. Additional connections will not give you more performance. This means you need to queue and throttle requests at the application layer and with some thought you can probably figure out what to do with the waiting users - show partial results? show a nice whale? A "loading please wait" sign?
2) If you didn't do #1 and the DB is getting overloaded, my normal response is to start shooting down connections. Oracle has separate unix process per connection. MySQL has its own way of shooting connections down. Put up a small script that will kill the correct percentage of sessions to prevent overload on shared resources. This will generates lots of errors and will cause a percentage of the users to hate you, but you won't be down.