Alex Rivera | Logout

MongoDB vs. Cassandra vs. MySQL for real-time advertising platform

Asked 2011-05-28T16:06:46.273
60

I'm working on a real-time advertising platform with a heavy emphasis on performance. I've always developed with MySQL, but I'm open to trying something new like MongoDB or Cassandra if significant speed gains can be achieved. I've been reading about both all day, but since both are being rapidly developed, a lot of the information appears somewhat dated.

The main data stored would be entries for each click, incremented rows for views, and information for each campaign (just some basic settings, etc). The speed gains need to be found in inserting clicks, updating view totals, and generating real-time statistic reports. The platform is developed with PHP.

Or maybe none of these?

mongodb database-design cassandra database nosql
Edit
Report

1 Answer

27

Characteristics of MySQL:

  • Database locking (MUCH easier for financial transactions)
  • Consistency/security (as above, you can guarantee that, for instance, no changes happen between the time you read a bank account balance and you update it).
  • Data organization/refactoring (you can have disorganized data anywhere, but MySQL is better with tables that represent "types" or "components" and then combining them into queries -- this is called normalization).
  • MySQL (and relational databases) are more well suited for arbitrary datasets and requirements common in AGILE software projects.

Characteristics of Cassandra:

  • Speed: For simple retrieval of large documents. However, it will require multiple queries for highly relational data – and "by default" these queries may not be consistent (and the dataset can change between these queries).
  • Availability: The opposite of "consistency". Data is always available, regardless of being 100% "correct".[1]
  • Optional fields (wide columns): This CAN be done in MySQL with meta tables etc., but it's for-free and by-default in Cassandra.

Cassandra is key-value or document-based storage. Think about what that means. TYPICALLY I give Cassandra ONE KEY and I get back ONE DATASET. It can branch out from there, but that's basically what's going on. It's more like accessing a static file. Sure, you can have multiple indexes, counter fields etc. but I'm making a generalization. That's where Cassandra is coming from.

MySQL and SQL is based on group/set theory -- it has a way to combine ANY relationship between data sets. It's pretty easy to take a MySQL query, make the query a "key" and the response a "value" and store it into Cassandra (e.g. make Cassandra a cache). That might help explain the trade-off too, MySQL allows you to always rearrange your data tables and the relationships between

answered 2013-01-23T18:02:40.283

Your Answer