Digg: 4000% Performance Increase by Sorting in PHP Rather than MySQL

O'Reilly Radar's James Turner conducted a very informative interview with Joe Stump, current CTO of SimpleGeo and former lead architect at Digg, in which Joe makes some of his usually insightful comments on his experience using Cassandra vs MySQL. As Digg started out with a MySQL oriented architecture and has recently been moving full speed to Cassandra, his observations on some of their lessons learned and the motivation for the move are especially valuable. Here are some of the key takeaways you find useful:

  1. Precompute on writes, make reads fast. This is an oldie as a scaling strategy, but it's valuable to see how SimpleGeo is applying it to their problem of finding entities within a certain geographical region. Using Cassandra they've built two clusters: one for indexes and one for records. The records cluster, as you might imagine, is a simple data lookup. The index cluster has a carefully constructed key for every lookup scenario. The indexes are computed on the write, so reads are very fast. As reads dominate, this makes a lot of sense. Queries based on time are also precomputed. Joe mentions some special algorithms for spreading out data, which tends to cluster around geographical regions, but does not mention what these are.
  2. Restrict what the user can do. The system is kept simpler by not allowing open ended queries. Users are allowed to perform a well defined set of operations that end up using highly optimized searches. They have no intention of being a generic database, they only intend to be able to serve geodata, well.
  3. The relation tool chain has failed for real-time. The relational database tool chain is not evolving. It has failed for large scale, real-time environments. Building scalable systems on a relational database requires building sharding, load balancing, resharding, cluster management, worrying about consistency, implementing distributed queries, and other layers yourself, so why bother? Cassandra does all that for you out of the box. Shutdown a server and Cassandra will handle all the remapping and rerouting automatically.
  4. Scaling practices turn a relational database into a non-relational database. To scale at Digg they followed a set of practices very similar to those used at eBay. No joins, no foreign key constraints (to scale writes), primary key look-ups only, limited range queries, and  joins were done in memory. When implementing the comment feature a 4,000 percent increase in performance was created by sorting in PHP instead of MySQL. All this effort required to make a relational database scale basically meant you were using a non-relational database anyway. So why not just use a non-relational database from the start?
  5. Embrace and extend existing products rather than build your own. Cassandra allowed SimpleGeo to create custom data partitioning policies to better spread the data around. This meant a custom database didn't have to be created, an existing database could be extended to go that extra mile while still benefiting from a well supported highly functional database. This is also a lesson learned at Justin.tv and will likely become an even more important strategy as complexity increases.
  6. Scaling equals specialization. To scale often requires building highly custom, problem specific solutions.
  7. MySQL works fine for a certain problem set. Typically for relatively static data sets, relatively low query volumes, and relatively high latency requirements.