Using combination of MySQL and MongoDB

Question

Does it make sense to use a combination of MySQL and MongoDB. What im trying to do basically is use MySQl as a "raw data backup" type thing where all the data is being stored there but not being read from there.

The Data is also stored at the same time in MongoDB and the reads happen only from mongoDB because I dont have to do joins and stuff.

For example assume in building NetFlix

in mysql i have a table for Comments and Movies. Then when a comment is made In mySQL i just add it to the table, and in MongoDB i update the movies document to hold this new comment.

And then when i want to get movies and comments i just grab the document from mongoDb.

My main concern is because of how "new" mongodb is compared to MySQL. In the case where something unexpected happens in Mongo, we have a MySQL backup where we can quickly get the app fallback to mysql and memcached.

Answer 1

On paper it may sound like a good idea, but there are a lot of things you will have to take into account. This will make your application way more complex than you may think. I'll give you some examples.

Two different systems

You'll be dealing with two different systems, each with its own behavior. These different behaviors will make it quite hard to keep everything synchronized.

• What will happen when a write in MongoDB fails, but succeeds in MySQL?
• Or the other way around, when a column constraint in MySQL is violated, for example?
• What if a deadlock occurs in MySQL?
• What if your schema changes? One migration is painful, but you'll have to do two migrations.

You'd have to deal with some of these scenarios in your application code. Which brings me to the next point.

Two data access layers

Your application needs to interact with two external systems, so you'll need to write two data access layers.

• These layers both have to be tested.
• Both have to be maintained.
• The rest of your application needs to communicate with both layers.
• Abstracting away both layers will introduce another layer, which will further increase complexity.

Chance of cascading failure

Should MongoDB fail, the application will fall back to MySQL and memcached. But at this point memcached will be empty. So each request right after MongoDB fails will hit the database. If you have a high-traffic site, this can easily take down MySQL as well.

Word of advice

Identify all possible ways in which you think 'something unexpected' can happen with MongoDB. Then use the most simple solution for each individual case. For example, if it's data loss you're worried about, use replication. If it's data corruption, use delayed replication.