title: Switching from MySQL to Cassandra - Pros/Cons?
body: <p>For a bit of background - this question deals with a project running on a single small EC2 instance, and is about to migrate to a medium one. The main components are Django, MySQL and a large number of custom analysis tools written in python and java, which do the heavy
  lifting. The same machine is running Apache as well.</p>
  
  <p>The data model looks like the following - a large amount of real time data comes in streamed from various networked sensors, and ideally, I'd like to establish a long-poll approach rather than the current poll every 15 minutes approach (a limitation of computing stats and writing into the database itself). Once the data comes in, I store the raw version in
  MySQL, let the analysis tools loose on this data, and store statistics in another few tables. All of this is rendered using Django.</p>
  
  <p>Relational features I would need -</p>
  
  <ul>
  <li>Order by <em>[SliceRange in Cassandra's API seems to satisy this]</em></li>
  <li>Group by</li>
  <li>Manytomany relations between multiple tables <em>[Cassandra SuperColumns seem to do well for one to many]</em></li>
  <li>Sphinx on this gives me a nice full text engine, so thats a necessity too. <em>[On Cassandra, the Lucandra project seems to satisfy this need]</em></li>
  </ul>
  
  <p>My major problem is that data reads are extremely slow (and writes aren't that hot either). I don't want to throw a lot of money and hardware on it right now, and I'd prefer something that can scale easily with time. Vertically scaling MySQL is not trivial in that sense (or cheap).</p>
  
  <p>So essentially, after having read a lot about NOSQL and experimented with things like MongoDB, Cassandra and Voldemort, my questions are,</p>
  
  <ul>
  <li><p>On a medium EC2 instance, <strong><em>would I gain any benefits in reads/writes by shifting to something like Cassandra</em></strong>? <a href="http://static.last.fm/johan/nosql-20090611/cassandra_nosql.pdf" rel="noreferrer">This article</a> (pdf) definitely seems to suggest that. Currently, I'd say a few hundred writes per minute would be the norm. For reads - since the data changes every 5 minutes or so, cache invalidation has to happen pretty quickly. At some point, it should be able to handle a large number of concurrent users as well. The app performance currently gets killed on MySQL doing some joins on large tables even if indexes are created - something to the order of 32k rows takes more than a minute to render. (This may be an artifact of EC2 virtualized I/O as well). Size of tables is around 4-5 million rows, and there are about 5 such tables.</p></li>
  <li><p>Everyone talks about using Cassandra on multiple nodes, given the CAP theorem and eventual consistency. But, for a project that is just beginning to grow, <strong><em>does it make sense
  to deploy a one node cassandra server</em></strong>? Are there any caveats? For instance, can it replace MySQL as a backend for Django? [Is this recommended?]</p></li>
  <li><p>If I do shift, I'm guessing I'll have to rewrite parts of the app to do a lot more "administrivia" since I'd have to do multiple lookups to fetch rows.</p></li>
  <li><p><strong><em>Would it make any sense to just use MySQL as a key value store</em></strong> rather than a relational engine, and go with that? That way I could utilize a large number of stable APIs available, as well as a stable engine (and go relational as needed). (Brett Taylor's post from Friendfeed on this - <a href="http://bret.appspot.com/entry/how-friendfeed-uses-mysql" rel="noreferrer">http://bret.appspot.com/entry/how-friendfeed-uses-mysql</a>)</p></li>
  </ul>
  
  <p>Any insights from people who've done a shift would be greatly appreciated!</p>
  
  <p>Thanks.</p>