I am using mongodb with rails app. From past few days, I am facing weird issues while querying the data directly from mongodb. Let’s say there is object M1 and this object has few parameters for which I need to query data from mongo. Now suppose I query data for current day, there are almost 5000 records, and I am using .to_a in rails controller, but this operation is taking almost 50~60 seconds. Now for same object M1 and for same as previous parameters, when I query data of any past day and use .to_a, it takes <10 seconds. Now again if I query for same object M1 and same parameters and use .to_a, it now takes <5 seconds. Also now if I change few of object parameters, it again take >50 seconds. And any query for historical data is taking <10 seconds but for current day is taking >50 seconds, unless I run that same query once for any past day. I have checked the indexes, and are being used correctly in each query. What can be possible reason for this?
Without any other measures I suspect that your data working set does not fit RAM and the server keeps going to disk to retrieve the required data. That is only a gut feeling as there is insufficient metrics to support any conclusion.
When you query cold data (i.e. data which is not in memory) it must be paged from disk and this takes time. Once the data is in memory subsequent queries do not incur the same disk overhead and will be many orders of magnitude faster. The database will retain this data in memory until a different query requires it to flush that data from memory to page in data to satisfy a new query. Hence new queries that touch the same working set will be fast. Queries that hit a new data set will be slow. The big performance hit comes when you don’t allocate enough memory to keep all indexes in memory. Then every query using an index that is not in memory requires paging.
Remember disks are about 100,000 times slower than memory when doing random access queries i.e. database queries.
When posting questions about query speed we always recommend you post the explain plan. This can be obtained by running .explain("executionStats") on the find in the shell.