Simple Test : MongoDB vs MySQL

It’s just come up in my mind that I want to prove the performance difference between MySQL and MongoDB, so I started this research. It took almost 2 days (I waste a lot of my time in importing a lot of data) and the conclusion is MongoDB is faster than MySQL and it’s about 50% faster. Okay, let’s say ‘wow’ together, “woooooow!”. It might be some of you already known about this fact, but this is my first time in proving the performance between to technologies and I felt so ecstatic!

Now let me tell you the environment that I use for this test. I use Ruby on Rails 3.2.8MySQL 5.1.44, mysql2 gem (0.3.11), mongoid gem (3.0.14), and MongoDB (2.2.0). My plan is to see which one has the better performance in solving the N + 1 Query Problem, so I prepared 2 tables of data which are containing a quite lot amount of data. Table hotels (the main table) has 4,450 rows of data and Table hotel_reviews (the child table) has 75,650 rows of data.

The tools are prepared and let’s start our test, first from MySQL then we continue to MongoDB.

1. MySQL

First, I created the relation between table hotels and table hotel_reviews as one-to-many in their model, then I wrote this code to find out its performance

def eagerload
  start_time = Time.now
  @hotels = Hotel.includes(:hotel_reviews).all
  end_time = Time.now
  @diff_time = end_time - start_time
  logger.info "Time elapsed : " + @diff_time.to_s + " s"
  logger.info "Hotel size : " + @hotels.size.to_s
  review_time = Time.now
  @hotels.each do |hotel|
    logger.info "Review Size : " + hotel.hotel_reviews.size.to_s
  end
  end_review_time = Time.now
  @review_diff_time = end_review_time - review_time
  logger.info "Review time : " + @review_diff_time.to_s
  final_time = Time.now
  @final_diff = final_time - start_time
  logger.info "Final Time Elapsed : " + @final_diff.to_s
  respond_to do |format|
    format.html
  end
end

And the result of this test was :

  • Query time (represented by variable @diff_time) : 12.57465 seconds
  • Get hotel reviews data (represented by variable @review_diff_time) : 0.078913 seconds
  • Overall process (represented by variable @final_diff) : 12.653674 seconds

2. MongoDB

I did the same thing with the model relation and also used the same process for MongoDB testing, the result of the test was :

  • Query time (represented by variable @diff_time) : 0.028555 seconds
  • Get hotel reviews data (represented by variable @review_diff_time) : 12.195999 seconds
  • Overall process (represented by variable @final_diff) : 12.226271 seconds

What on earth happened!? The performance didn’t have a significant differences, then what so special about NoSQL?

Those contradiction questions come up in my head, but in the other side, I still believe that it was impossible, there maybe something was wrong with the code, the data, or whatever it is. So I started my looking about MongoDB a bit deeper and I got this

The strength of MongoDB is in its unstructured collection of data and so MongoDB doesn’t provide joins to collection various of data but because of its dynamic structure, so the child data should be collected in every row of data in the parent table

Now I saw my mistakes by using a new technology without trying to understand the basic concept or architecture of it. So, I made some changes in my model, from has_many to embeds_many and belongs_to to embedded_in (those keyword were provided by Mongoid). I also made a change at the code so it was like this

def eagerload
  start_time = Time.now
  @hotels = Hotel.all
  end_time = Time.now
  @diff_time = end_time - start_time
  logger.info "Time elapsed : " + @diff_time.to_s + " s"
  logger.info "Hotel size : " + @hotels.size.to_s
  review_time = Time.now
  @hotels.each do |hotel|
    logger.info "Review Size : " + hotel.hotel_reviews.size.to_s
  end
  end_review_time = Time.now
  @review_diff_time = end_review_time - review_time
  logger.info "Review time : " + @review_diff_time.to_s
  final_time = Time.now
  @final_diff = final_time - start_time
  logger.info "Final Time Elapsed : " + @final_diff.to_s
  respond_to do |format|
    format.html
  end
end

There was no includes method since the data is already in there in the first place. So I restarted the test and the result was like this :

  • Query time (represented by variable @diff_time) : 0.045823 seconds
  • Get hotel reviews data (represented by variable @review_diff_time) : 6.989655 seconds
  • Overall process (represented by variable @final_diff) : 7.037146 seconds

That was the performance that I expected from MongoDB. You can see the speed that it performed to query the data and to get the child data cut almost 50% of MySQL process.

From this fact I can see it clearly why MongoDB has a better performance than MySQL. But, (there’s a but in this) I just speak about the performance or the speed in querying data. I haven’t made more research about the other aspect, let’s hope that I have more time or at least, the interest to do it. Hehehe..

That’s all folks! See you in other posts. Cheers!

Advertisements

8 thoughts on “Simple Test : MongoDB vs MySQL

  1. Sorry, but this is simply wrong. If you get query times of >10s in MySql with these few entries, you’re definitely doing something *really* wrong. The n+1 query can be solved quite efficient in MySQL, btw.

    Second, if you publish performance tests please provide the raw data, i.e. the exact table definitions and entries (e.g. mysql table creation scripts plus csv files). Also specify how many runs you did, how you regarded caching and what system you measured these runs on.

    Last but not least: Keep in mind that someone might use your blog article as a basis for an important decision – so publish responsible. Even if your measurements were correct, you tested for a very specific environment and scenario – what if MySQL is faster in all other cases?

    1. Hi Michel, thank you for stopping by.

      You can see what I was doing from the code written in the article. So, if I’m doing anything wrong, please advise me.

      This test performed in my laptop with 4GB and 2.8 GHz Intel Core i7 processor. I performed about 10 times tests and I always get the same result (with a little difference in more or less). Regarding the data, in MySQL I didn’t create any indices in any tables, and neither in MongoDB.

      I really appreciate your intention and thank you for reminding me, Michel. I’ll keep it in mind.

      Btw, can you show me the proof that MySQL is faster than MongoDB in dealing with N+1 query problem?

      1. Hi Hafiz

        For ideas to efficiently solve the n+1 problem in MySQL see for example the top answer and its comments at http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue#answer-97253
        And applying indices here for the foreign keys is for sure a good idea ;)

        Please don’t get me wrong – MongoDB may still be faster than MySQL, I really don’t know. The thing is, you are testing the performance with a very specific set. Do the results reflect the performances in ASP.net, PHP or node.js as well? What if RoR’s next version contains optimizations that turn around the result? What if MySQL or MongoDB get incredible slow or fast with large datasets, i.e. how do their algorithms scale?

        So the title and résumé should rather be “MongoDB seems to be 50% faster than MySQL at the n+1 query problem on small data sets without further optimizations in RoR 3.2.8” (if it still is after applying indices). This would still be an actual useful result – maybe you can also test other scenarios like simple and complex joins, searches, insertions, deletions, …
        Unfortunately correct performance testing is a science for itself.

        Nevertheless, have a nice week and happy coding! :)

      2. Hi Michel,

        I admit that I’m no an experienced technical writer, therefore, I hope this experience should make me a better one. I guess it’s all about the context that I should write and I suppose you don’t see a clear context in this article. Your intention is well taken and I’m thankful for that.

        It’s nice to have your comment. :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s