How can I check mongodb query performance without cache

Question

I have a query that takes too long to finish. I like to do some performance tests but after I check for one time (that takes currently ~30 seconds) the query start to run a lot faster (< 1 second). I assume it has to be with the mongodb caching. Is there a way to disable caching for mongodb or another way I can check the performance?

I'm using mongodb hosted in mongohq. Program with Ruby on Rails 3. Here is the explain:

{"cursor"=>"BtreeCursor start_-1", "nscanned"=>5276, "nscannedObjects"=>5276, "n"=>25, "millis"=>3264, "nYields"=>0, "nChunkSkips"=>0, "isMultiKey"=>false, "indexOnly"=>false, "indexBounds"=>{"start"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]]}, "allPlans"=>[{"cursor"=>"BtreeCursor attendees_count_-1", "indexBounds"=>{"attendees_count"=>[[1.7976931348623157e+308, 1]]}}, {"cursor"=>"BtreeCursor images_count_-1", "indexBounds"=>{"images_count"=>[[1.7976931348623157e+308, 2]]}}, {"cursor"=>"BtreeCursor start_-1", "indexBounds"=>{"start"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]]}}, {"cursor"=>"BtreeCursor start_-1_end_-1", "indexBounds"=>{"start"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]], "end"=>[[{"$maxElement"=>1}, {"$minElement"=>1}]]}}, {"cursor"=>"BtreeCursor attendees._id_1 multi", "indexBounds"=>{"attendees._id"=>[[BSON::ObjectId('4f0b621e94bb688563000007'),BSON::ObjectId('4f0b621e94bb688563000007')], [BSON::ObjectId('4f0b647d5a8c00acde05236f'), BSON::ObjectId('4f0b647d5a8c00acde05236f')], [BSON::ObjectId('4f0b647d5a8c00acde052370'), BSON::ObjectId('4f0b647d5a8c00acde052370')], [BSON::ObjectId('4f0b647d5a8c00acde052371'), BSON::ObjectId('4f0b647d5a8c00acde052371')], [BSON::ObjectId('4f0b647d5a8c00acde052372'), BSON::ObjectId('4f0b647d5a8c00acde052372')], [BSON::ObjectId('4f0b647d5a8c00acde052373') ... (lots of Object IDs)]]}}}

Notice that I had to truncate some of the explain because it was too long. Where there is "lots of Object IDs" there were lots of Object IDs (~400).

Thanks all

Answer 1

I guess you can not stop Mongo from preparing the query plan (that's how mongo works). So, Before query stats.. 1. Clear the plan cache of all collections by

>db.colllction_name.getPlanCache().clear()

2. Record the slow queries with Profiler (see mongo docs),
3. work on query to optimize it,
4. clear the cache again, and check the query performance again.

Answer 2

When you are first running the query, the data set is memory mapped, but has not been paged into actual memory, see Caching on the MongoDB site. So, the OS has to page that data set into memory and then you get your query run and result.

Because you are paging in from the disk (slow) into RAM (fast) the initial run is slow, and then, unless you have memory pressure, that data will stay in RAM and all of your subsequent queries on that data set will be fast.

This is how MongoDB is designed to function, the process of loading your data set into memory is often called "warming up" the database and it is only after that warming up (in your case the first query) that you get the true performance.

It is worth noting that your initial query still seems to take a very long time to return. You should make sure it is using indexes effectively. The best place to start in that investigation is the explain() page.

Answer 3

You can use db.collection.getPlanCache().clear() to remove all cached query plans for a collection.

https://docs.mongodb.com/v3.2/reference/method/PlanCache.clear/